FAQ: Databases in Visual Basic 3.0

ID: Q126730

The information in this article applies to:

Microsoft Visual Basic Standard and Professional Editions 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."

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

Q119591 : How to Obtain Microsoft Support Files from Online Services

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

Q113683 : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

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.

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:

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

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.

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:

Q103115 : PRB: Invalid picture Error When Try to Bind Picture Control

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:

Q100367 : BUG: Referencing Data Object Gives Error: Object not an Array

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:

Q107672 : Possible Reasons for Couldn't Find Installable ISAM Error

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:

Q100369 : LONG: Corrections for Errors in VB Version 3.0 Manuals

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:

Q103808 : Limitations of the Data Control in Visual Basic Version 3.0

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:

Q103808 : Limitations of the Data Control in Visual Basic Version 3.0 WORKAROUND

Q107748 : How to Create a Parameter Query in Visual Basic for Windows

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:

Q113683 : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

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:

Q104155 : Examples Show How to Query BIBLIO.MDB Database

Q105539 : How to Use VB Control Property or Variable in SQL Statement

Q113901 : How to Query for Literal Special Characters in a Where Clause

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:

Q106492 : How to Call SQL Stored Procedures from Visual Basic

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:

Q114771 : BUG: Disk or Network Error with Data Access Objects

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:

Q113953 : How to Use the Three Levels of Database Locking in VB 3.0

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:

Q117900 : Reserved Error Numbers Returned by the Jet 2.0 Engine

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

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

Q105990 : 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.

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.
1. It is not a good idea to turn the PC off in the middle of a Windows application for several reasons:
2. 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.
3. 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.

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:
1. From within Access, export the table to an ASCII file.
2. Delete the table from the database.
3. Compact the database.
4. Re-create the table and any relationships it had.
5. Examine the ASCII file for bad or strange data and remove those records.
6. Re-import the ASCII file into the newly re-created table.
7. Recreate any records you were forced to delete.

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:

Q113683 : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

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:

Q103442 : Differences Between Object Variables in VB Version 3.0

Q109218 : Using Table Objects Versus Dynaset/Snapshot Objects in VB

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

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:

Q103808 : Limitations of the Data Control in Visual Basic 3.0

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:

Q108149 : Comparison of Seek versus Find Methods, for VB Data Access

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:

Q107672 : Possible Reasons for Couldn't Find Installable ISAM Error

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:

Q109400 : INF: README.TXT File and Novell Locking Issues

Q102522 : PRB: "Record Lock Threshold Exceeded" with Large Action Query

Q113564 : INF: Microsoft Access 2.0 Readme Help File Contents

Additional query words: FAQ

Keywords : kbVBp300
Version : 3.00
Platform : WINDOWS
Issue type : kbinfo