Symptoms
Attempting to use a Jet workspace to open an ODBC datasource based on a Jetdatabase can cause Visual Basic to hang. This is essentially causing Jet totalk to itself. This is not a supported way of accessing an Access/Jetdatabase.
Resolution
You can use an ODBC workspace to open an ODBC datasource based on a Jetdatabase. You can also open a Jet database directly, without an using anODBC datasource.
Symptoms
According to Microsoft Knowledge Base article 275561?(http://support.microsoft.com/kb/275561/EN-US/) “ACC2000: New Features in Microsoft Jet 4.0″:
To minimize the impact of the increased page size and respond to a long-standing request from developers building applications based on the Microsoft Jet database engine, row-level locking was added to Jet 4.0.However, row-level locking of an Access database is not available with Data Access Objects (DAO) 3.60.
Resolution
To resolve this problem, use ActiveX Data Objects (ADO) to enable row-level locking on an Access database, and then open DAO connections to the database. All subsequent attempts to open DAO connections to the database will respect the locking mode that you set.
Symptoms
When you use FileMon or another utility to monitor file activity, if an application uses DAO 3.6x with Jet 4.0, you receive the following error message:
FILE NOT FOUNDfor the following dynamic-link libraries (DLLs):
MSJET49.DLL
MSJET48.DLL
MSJET47.DLL
MSJET46.DLL
MSJET45.DLL
MSJET44.DLL
MSJET43.DLL
MSJET42.DLL
MSJET41.DLLHowever, you also notice that MSJET40.DLL is loaded successfully.
Resolution
This behavior is by design to allow future Jet 4 functionality to be included into DLLs that are named incrementally. For example, a new version of Jet 4 can be implemented in a DLL named MSJET41.DLL. DAO 3.60 then automatically uses the new version of Jet 4, MSJET41.DLL.
Microsoft Development has found that it does not significantly impact DAO 3.60 or Jet 4.0 performance to load these DLLs.
Symptoms
Error 3167 “Record is Deleted” is a common error when using the Data AccessObjects (DAO) or a data control to access ODBC tables. This is due to theway that the Microsoft Jet Database Engine manipulates its cursor for therecordset. It is not limited to DAO. Similar errors can be raised by anyengine that maintains a cursor. The ODBC cursor library and serversthemselves can and will raise similar errors. Understanding why and howthese errors are caused requires a knowledge of resultset and cursorbehavior.
Resolution
The “Record is Deleted” error (error 3167) is a byproduct of the Jetengine’s keyset cursor for the dynaset type recordset. A keyset cursor isfixed in membership, but there is nothing stopping another user fromdeleting a row in the underlying table that you have selected in yourkeyset. When you attempt to get the data or update the data in a deletedrow, the “Record is Deleted” error message is generated. Again, thisbehavior is not limited to the Jet engine, but can occur in any keysetcursor.
This is not the only cause of the error. There are several other causesthat are far more subtle and depend on the keyset implementation. Becausethe Jet engine uses a keyset based on a unique index in the underlyingtables, it is possible to get this error if something changes the indexinformation. When the fields that the keyset is built from are changed inthe underlying table for a given record, the Jet engine is not able to findthe record to read or update the data and raises the “Record is Deleted”error. In most cases the Jet engine knows that the indexed fields changedin the underlying table if it made the changes, but the following couldchange the indexed fields without the Jet engine being aware of it:
Other users. Other users may change the values in the indexed fields.When the Jet engine is unable to find that record based on the valuethat it is storing, it raises an error.Triggers. Triggers can change the values in the indexed fields. Sincea trigger changes the values from what the Jet engine thinks it putin there, the cached keyset value and the actual value in the tablediffer. When the Jet engine tries to fetch the record, it will raisean error.Null and Empty String behavior. Many databases automatically changedata if necessary without alerting the Jet engine. For example, if theuser were to add a record where the indexed column was a varchar() andthe user submitted a “” value for that field, SQL Server would changethe “” into a space (” “) and not alert the Jet engine. The Jet enginewould then try to find that record with “”, fail to do so and raise the”Record is Deleted” error.Functions. Many indexed fields are updated with server functions such asGetDate(). These can change the indexed data without the Jet engineknowing it.Indexes on non-standard datatypes or floating point datatypes. Someserver datatypes have no ODBC or Jet engine equivalent datatype. In mostcases, the Recordset is created as read-only, but sometimes it is not.Rounding or conversion errors on the datatypes can cause the error aswell.
Symptoms
If you try to gain access to an Access 2000 or Access 2002 database, you may receive one of the following errors within your Visual Basic program.
If you are using Microsoft ActiveX Data Objects (ADO) (or the ADO Data Control), you receive the following error message:
Run-time error -2147467259 Unrecognized Database Format XXXIf you are using Data Access Objects (DAO) (or the DAO generic Data Control), you receive the following error message:
Run-time error 3343 Unrecognized Database Format XXX
Resolution
Access 2000 and Access 2002 use the Jet 4.0 engine, which creates Jet 4.0 format database files. Jet 3.5 components do not recognize such a format.If you are using ADO, you get error -2147467259 when you try to connect to your Access 2000 or 2002 database through the Microsoft.Jet.OLEDB.3.51 provider.If you are using DAO, you get error 3343 when you use the Microsoft DAO 3.51 Object Library.The DAO generic Data Control does not work against Access 2000 or 2002 databases and always generates error 3343 unless it is used as instructed in the “Resolution” section of this article. This occurs because this control is based on Jet 3.51 and only recognizes Jet 3.51 (or before) database formats.
Symptoms
When attempting to use the Data Access Object (DAO) 3.5 CompactDatabasemethod to convert a Jet 2.0 .mdb file to a Jet 3.0 file format, theresulting .mdb file is still in a Jet 2.0 file format.
Resolution
DAO 3.5 requires you to provide explicitly the locale argument of theCompactDatabase when converting .mdb file formats.