FAQ: Databases in Visual Basic 3.0

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

This article covers some of the most Frequently Asked Questions (FAQ) about non-technical issues for Microsoft Visual Basic for Windows. You can find this and other FAQ articles by querying on the keyword "FAQ." You can find additional general references in the Microsoft Knowledge Base by searching on "article list."

1. Q. I'm getting "Incompatible Database Version" on my Access 2.0 database
      from inside of Visual Basic.

   A. You need to install the Compatibility Layer for Visual Basic 3.0 to
      be able to read Access 2.0 databases.

 ~ comlyr.exe (size: 741798 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from
               Online Services

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q113683
      TITLE     : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility
                  Layer

2. Q. I've installed the COMLYR, but I still get "Incompatible Database
      Version." What is wrong?

   A. The frequent cause of Visual Basic failing to recognize a Access 2.0
      database even though the compatibility layer is installed is
      mismatched or duplicate versions of VBDB300.DLL.

      VBDB300.DLL was not renamed, only replaced, and establishes the
      connection to either the version 1.1 or 2.0 Jet engine. As such, it
      is not easy to spot when a newer version might be overwritten by an
      older version.

3. Q. What is FreeLocks and when should I use it?

   A. Freelocks should be the most frequently used statement you use when
      writing database code. Because Jet (the data access engine VB uses)
      is not multithreaded, it relies on its clients (your VB app) to give
      it the opportunity to do cleanup work. FreeLocks is that mechanism.

      Consider just adding records. Jet will allocate memory in order to
      keep track of locks; even after those locks are released, that
      memory is still allocated. Jet will allocate about 1.5 K for every 8-
      30 records added. FreeLocks releases locks made during the add
      process, as well as the memory that was allocated to store them.

      Those locks in that allocated memory may also still be in effect as
      well. Access will catch up to them sooner than it will free the
      actual memory, but why take chances?

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q122958
      TITLE     : When to Use Freelocks in a Multi-user Environment

4. Q. I've noticed my system memory decreasing using Access 1.X/2.0

   A. There are two issues concerning memory loss when adding records using
      the access engine within Visual Basic.

      First, there is a confirmed bug in both Access 1.x and Access 2.0
      wherein a leak of approximately 544 bytes under Access 1.x and 640
      bytes under Access 2.0 occurs when adding records. This leak occurs
      about every 50-60 records, occurs in Windows system memory, and is
      recoverable when the application terminates normally. The solution
      is to have your application terminate and restart itself about once a
      day to recover this lost memory.

      Second, because Jet (the data access engine VB uses) is not
      multithreaded, it relies on its clients to give it the opportunity to
      do cleanup work. FreeLocks is that mechanism. See the previous
      question on Freelocks.

5. Q. Why do I get an "Invalid Picture" error when binding a Picture Box to
      an Access database?

   A. This error occurs because the picture control can only bind to a
      bitmap, metafile, or icon stored in the database field -- not to an
      OLE object.

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q103115
      TITLE     : PRB: Invalid picture Error When Try to Bind Picture
                  Control

6. Q. Why do I get the error "Object Not an Array" on a data access
      statement when I have a global variable defined?

   A. There is a bug associated with parsing global data access object
      variables.

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q100367
      TITLE     : BUG: Referencing Data Object Gives Error: Object not an
                  Array

7. Q. Why do I get the error "Can't Find Installable ISAM" when I run my
      application in an .EXE format?

   A. You need to create a <appname.INI> with entries similar to the
      Installable ISAM section of your VB.INI file. Look on page 148 in the
      "Professional Features Book 2" for more information.

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q107672
      TITLE     : Possible Reasons for Couldn't Find Installable ISAM Error

8. Q. Why do I get the error "Can't Find Installable ISAM" when I try to
      connect to a Paradox database in the Visual Basic environment when
      using a data control?

   A. There is a documentation error in the manual on the Connect property
      for Paradox. Depending on what Paradox version you have, either set
      the Connect to: Paradox 3.x; or Paradox 4.x; [note: Paradox 4.x files
      require the PDX200.DLL driver that comes with the Compatibility Layer
      (COMLYR.EXE)].

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q100369
      TITLE     : LONG: Corrections for Errors in VB Version 3.0 Manuals

9. Q. When I try to use the .Index property of a data control, I get the
      error "Index property not found."

   A. There is a documentation error in the manual regarding a data control
      having an .Index property. This is not correct, a data control
      doesn't have an .Index property.

      For additional information, please see the following article in
      the Microsoft Knowledge Base:

      ARTICLE-ID: Q103808
      TITLE     : Limitations of the Data Control in Visual Basic Version
                  3.0

10. Q. I cannot get the data control to accept a parameter query.

    A. This is a limitation of the data control. The data control cannot
       handle parameter queries.

       For additional information, please see the following articles in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q103808
       TITLE     : Limitations of the Data Control in Visual Basic Version
                   3.0

       Workaround:

       ARTICLE-ID: Q107748
       TITLE     : How to Create a Parameter Query in Visual Basic for
                   Windows

11. Q. I can't seem to use Visual Basic 3.0 to get to my Access 2.0
       database data?

    A. You will need the Compatibility Layer (COMLYR.EXE) files to use
       Access 2.0 data in your Visual Basic 3.0 program.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q113683
       TITLE     : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility
                   Layer

12. Q. When I run my query I get either a syntax error or 1 parameters
       expected 0 parameters error with my SQL statement.

    A. There could be a number of things wrong with your SQL statement to
       get either of these errors; we recommend that you use the QBE (Query
       By Example) tool in Access to test and build your SQL statements.
       Then copy and paste the SQL syntax built in the QBE tool into your
       Visual Basic program.

       For additional information, please see the following articles in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q104155
       TITLE     : Examples Show How to Query BIBLIO.MDB Database

       ARTICLE-ID: Q105173
       TITLE     : How to Query for Dates Using a SQL Statement in VB 3.0

       ARTICLE-ID: Q105539
       TITLE     : How to Use VB Control Property or Variable in SQL
                   Statement

       ARTICLE-ID: Q113901
       TITLE     : How to Query for Literal Special Characters in a Where
                   Clause

13. Q. Can I call a stored procedure on my ODBC database?

    A. Yes, you can.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q106492
       TITLE     : How to Call SQL Stored Procedures from Visual Basic

15. Q. What would cause the a "Disk or Network error" in my Visual Basic
       program.

    A. If you are using the Data Access Object variables in your Visual
       Basic program, you may need to close these variables before ending
       your program.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q114771
       TITLE     : BUG: Disk or Network Error with Data Access Objects

16. Q. How can I program my Visual Basic program to work on a network with
       multiple users running my database program.

    A. You will need to set up some error trapping routines so that you can
       continue when multi-user conflict (errors) occur.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q113953
       TITLE     : How to Use the Three Levels of Database Locking in VB
                   3.0

17. Q. I'm using the Compatibility Layer and now I'm getting an error with
       a negative number. I looked in the "Trappable Database Errors" help
       topic and couldn't find any negative numbers listed there. What is
       the problem?

    A. With the release of Access 2.0 and the Jet 2.0/Visual Basic 3.0
       Compatibility Layer, a new set of database error numbers were
       introduced. These numbers are all negative, and are detailed in
       Knowledge Base article Q117900, which provides a list of these
       errors along with a brief description of their potential cause.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q117900
       TITLE     : Reserved Error Numbers Returned by the Jet 2.0 Engine

18. Q. How does Visual Basic handle security on an Access database?

    A. Please see the following article in the Microsoft Knowledge Base:

       ARTICLE-ID: Q105990
       TITLE     : How Visual Basic Handles Security Set by Microsoft
                   Access

       There are also several whitepapers/utilities available in the
       MSACCESS forum :

       SECWIZ.ZIP - Security Wizard for MS Access 1.1
       SECURE.ZIP - Whitepaper on Access Security
       SECWZ2.ZIP - Security Wizard for MS Access 2.0 and Whitepaper on
                    2.0 security.

19. Q. I'm using Access 2.0 and getting frequent "database is corrupt"
       messages. What do I do?

    A. This problem has not been exactly resolved or fully identified, in a
       large part due to an inability to create a reliable test case that
       demonstrates the problem. There are some "rules of thumb," however,
       to help prevent corruption.

       a. It is not a good idea to turn the PC off in the middle of a
          Windows application for several reasons:

          If windows is using a swap file, or smartdrive, or any kind of
          caching, anything that hasn't been written to disk will be lost.
          The advantage of having a smarter operating system is balanced
          against the disadvantage of having to go to a little bit of work
          to shut it down properly.

       b. Access 1.x/2.0 requires that you close your database objects
          explicitly, for example:

             Dim Db As Database, Ds as Dynaset
             Set Db = OpenDatabase(...)
             Set Ds = Db.CreateDynaset(...)
             ...
             Ds.Close
             Set Ds = Nothing
             Db.Close
             Set Db = Nothing

          However, it also requires that Visual Basic shut down cleanly.
          Again, caching is a problem, and the fact that both Visual Basic
          and Access keep track of things internally in memory, and which
          needs to be cleared out so the MDB file can be properly updated.

20. Q. My database has become corrupted and I can't get it back to normal.
       What do I do?

    A. The corruption can occur in a specific object in the database, such
       as a table. If that is the case, then try the following steps to
       create a "clean" database:

       a. From within Access, export the table to an ASCII file.
       b. Delete the table from the database.
       c. Compact the database.
       d. Re-create the table and any relationships it had.
       e. Examine the ASCII file for bad or strange data and remove those
          records.
       f. Re-import the ASCII file into the newly re-created table.
       g. Recreate any records you were forced to delete.

21. Q. How can I use Access 2.0 files inside of Visual Basic 3.0? How can I
       use Paradox 4.x files inside of Visual Basic 3.0?

    A. To make use of either Access 2.0 files or Paradox 4.x files, you
       must purchase a copy of Access 2.0, and acquire a copy of the Access
       2.0/Visual Basic 3.0 Compatibility Layer (VBCL). VBCL is available
       free for download in LIB 1 of MSBASIC and in the regular MSL forum.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q113683
       TITLE     : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility
                   Layer

22. Q. What are the differences between Table and Dynaset/Snapshot objects?

    A. There is an article in the Microsoft Knowledge Base that is a long
       summary of the properties and methods along with a discussion of how
       each behaves.

       For additional information, please see the following articles in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q103442
       TITLE     : Differences Between Object Variables in VB Version 3.0

       ARTICLE-ID: Q109218
       TITLE     : Using Table Objects Versus Dynaset/Snapshot Objects in
                   VB

23. Q. I'm getting error 3043 "Disk or Network Error." What's wrong?

    A. Each and every database object that is opened must be closed, and it
       also helps to set them to nothing as well; that is:

       Dim MyDB As Database, MySet As Dynaset, SqlStmt$
       Set MyDB = OpenDatabase("C:\VB\BIBLIO.MDB")   ' Open database.
       ' Create the new Dynaset.
       Set MySet = MyDB.CreateDynaset("SELECT * FROM Authors")
       ... do your thing here ...
       MySet.Close
       MyDb.Close
       Set MySet = Nothing
       Set MyDb = Nothing

     In addition, you could create a global routine for closing common
     database objects with the following code:

       If Not MySet Is Nothing Then
         MySet.Close
         Set MySet = Nothing
       End If
       If Not MyDb Is Nothing Then
         MyDb.Close
         Set MyDb = Nothing
       End If

24. Q. Why can't I use querydefs with the data control?

    A. This is a limitation of the data control.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q103808
       TITLE     : Limitations of the Data Control in Visual Basic 3.0

25. Q. What is the difference between the Seek and Find methods?

    A. The find methods (FindFirst, FindLast, FindNext, and FindPrevious)
       apply to Dynasets and Snapshots but not to Table objects.
       Conversely, the Seek method is available only on the Table object.

       The Seek method is significantly faster than the find methods. It is
       also more flexible because you can change the Index property of the
       Table object to change the order of the Seek. For intensive
       searches, you may want to create a Table object so that you can use
       the Seek method along with the find methods on the open Dynasets.

       For additional information, please see the following article in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q108149
       TITLE     : Comparison of Seek versus Find Methods, for VB Data
                   Access

26. Q. I'm getting "Can't Find Installable ISAM." What's wrong?

    A. There are a number of possible reasons, most having to do with
       configuration. Check your connect string, make certain it matches
       your INI entry value for the database. If this error only occurs
       when your app is compiled, you need to create an <appname>.INI.

       For additional information, please see the following article(s) in
       the Microsoft Knowledge Base:

       ARTICLE-ID: Q107672
       TITLE     : Possible Reasons for Couldn't Find Installable ISAM
                   Error

27. Q. I'm having problems locking a database on a Novell server.

     A. There are three Knowledge Base articles in the Microsoft Access
        Knowledge Base that deal with Novell issues:

        For additional information, please see the following articles in
        the Microsoft Knowledge Base:

        ARTICLE-ID: Q109400
        TITLE     : INF: README.TXT File and Novell Locking Issues

        ARTICLE-ID: Q102522
        TITLE     : PRB: "Record Lock Threshold Exceeded" with Large Action
                    Query

        ARTICLE-ID: Q113564
        TITLE     : INF: Microsoft Access 2.0 Readme Help File Contents