Visual Basic Q&A

Symptoms
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.
Resolution
The following code demonstrates using the SELECT @@Identity to retrieve the value of the newly inserted auto-increment field. The code snippet also includes code to create the table for the query.

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “INSERT INTO AutoIncrementTest ” & _”(Description) VALUES (‘AutoIncrement Test’)”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT @@Identity”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _adLockReadOnly, adCmdTextMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing Thanks to this newly added functionality, you can see the newly added auto-increment values in your client-side ActiveX Data Objects (ADO) recordsets in ADO 2.1 and later. When you submit the new row to the Jet provider by calling Update or UpdateBatch (depending on your choice of LockType), the ADO cursor engine generates an INSERT INTO query to create the new row in the table. If the recordset contains an auto-increment field, ADO will also generate a SELECT @@Identity query to retrieve the value generated for that auto-increment field. The following code demonstrates this feature:

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT ID, Description FROM AutoIncrementTest”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.CursorLocation = adUseClientrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenStatic, _adLockOptimistic, adCmdTextrsNewAutoIncrement.AddNewrsNewAutoIncrement(“Description”).Value = “AutoIncrement Test”rsNewAutoIncrement.UpdateMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing You can create a new Jet 4.0 database using Microsoft Access 2000 or using the ADOX library that is included with MDAC 2.1. To use this library in your Visual Basic project, create a reference to Microsoft ADO Ext. 2.1 for DDL and Security. You can then use code like the following to create a new Jet 4.0 database:

Dim strPathToMDB As StringDim catNewDatabase As ADOX.CatalogstrPathToMDB = “C:\NewJet4.MDB”If Dir(strPathToMDB) <> “” ThenKill strPathToMDBEnd IfstrConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set catNewDatabase = New ADOX.CatalogcatNewDatabase.Create strConnSet catNewDatabase = Nothing To determine the format of your Microsoft Access database, check the dynamic “Jet OLEDB:Engine Type” property in the Connection object’s Properties collection. The property will return a value of 5 for Jet 4.x databases. The following code snippet demonstrates using the property:

Dim cnDatabase As ADODB.ConnectionDim strConn As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnIf cnDatabase.Properties(“Jet OLEDB:Engine Type”).Value = 5 ThenMsgBox “Jet 4.0 database”ElseMsgBox “Not a Jet 4.0 database”End IfcnDatabase.CloseSet cnDatabase = Nothing

Symptoms
DAO is a COM wrapper around the Jet Engine, which is used by MicrosoftAccess, Excel, Project, Visual Basic, and the Microsoft Foundation Classes(among others). It can be confusing which version of Jet you are usingbecause so many applications may distribute Jet onto your machine. Thetables and explanations in this article are intended to help you identifythe version and components of Jet that you are using for the givenapplication.
Resolution
This table displays which version of Jet is shipped with each of thefollowing products.

ApplicationMicrosoft JET version———————————————————————–Microsoft Access 1.01.0Microsoft Access 1.11.1Microsoft Access 2.02.0Microsoft Access 2.02.5 with Microsoft Access Service PackMicrosoft Access 7.03.0Microsoft Access 973.5 with Office 97Microsoft Access 97 SR13.5 with Office 97SR1Visual Basic 3.01.1Visual Basic 3.02.0 with Visual Basic CompatibilityLayerVisual Basic 3.02.5 with Microsoft Access Service PackVisual Basic 4.0 16-bit2.5Visual Basic 4.0 32-bit3.0Visual Basic 5.0 32-bit3.0Visual Basic 5.0 32-bit3.5Visual C++ 4.X3.0Visual C++ 5.03.5Jet 3.51 (web download)3.51 (Latest, 3.5 Binary compatible)
Compatibility Among VersionsWith each new version of Microsoft Jet, enhancements in functionality andchanges to the structure of the database file cause problems with backwardcompatibility. Wherever possible, efforts have been made to ensure an easymigration path among versions. However, incompatibilities do exist. Thefollowing table illustrates how you can use database files and objectsamong different versions of Microsoft Jet.

Jet Version——————————–1.01.12.02.53.03.5———————————————-MDB Version———–1.0YYYYYY1.1NYYYYY2.0NNYYYY3.0NNNNYY
A “Y” indicates that you can use the database without conversion; “N”indicates that you cannot use or convert the database. With DAO code, youcan open any version of any database up to the same version of MicrosoftJet. Microsoft Access is an exception to this rule because it can open onlythose databases with the same version as itself. However, it can link totables with the same or earlier version. In other words, if you upgrade toMicrosoft Jet 3.0, you will still be able to read version 2.x databases.
There is no version 2.5 or 3.5 of an .mdb file (just 2.0 and 3.0), whichare common to the x.0 and x.5 versions of Jet.
Jet 2.0 and Jet 2.5 use identical database formats in every aspect. Jet 2.0can read databases created with Jet 2.5.
Microsoft Jet 3.5 databases have the same structure as Jet 3.0. MicrosoftJet 3.0 can read data from Microsoft Jet 3.5 tables. The main limitationhere is that Microsoft Jet 3.5 may support additional properties on thedatabase object that aren’t recognized or taken advantage of by MicrosoftJet 3.0, but there aren’t any data incompatibilities.
Notice that Visual Basic 3.0 is capable of using three different versionsof Jet each requiring a separate set of dynamic link libraries (DLLs). Thiscan cause problems when your Visual Basic application expects to use Jet2.0 for example, and then you install another Visual Basic applicationusing an earlier version of Jet on the same system that replaces some ofthe version 2.0 DLLs with version 1.1. Typically the problem DLL in thissituation is Vbdb300.dll because it determines which version of the Jetengine will be used.
The following table should help you solve any version conflicts, and helpyou identify the version of Jet you are using. You may use the Wps.exeutility shipped with Visual Basic to find out which version of Jet youcurrently have loaded in memory. Wps.exe is located in the \VB\CDKdirectory of Visual Basic 3.0 Professional, and in \TOOLS\PSS directory ofthe Visual Basic 4.0 CD. Pay special attention to the version informationobtained from File Manager (File, Properties) in Visual Basic 3.0, andMicrosoft System Info. in Visual Basic 4.0 32-bit.
The following files are required by Visual Basic to use the Jet DatabaseEngine:

Jet VersionFileVersionDescription———————————————————————1.1VBDB300.DLL3.00.0528VB/JET supportMSAES110.DLL1.10.0000Expression servicesMSAJT110.DLL1.10.0001Jet 1.1 engineXBS110.DLL1.10.0002External xBASE ISAMBTRV110.DLL1.10.0000External Btrieve ISAMPDX110.DLL1.10.0000External Paradox ISAM2.0 (comlyr)VBDB300.DLL3.00.0529VB/JET supportMSAJT112.DLL1.99.1605Jet 2.x comp. loaderMSAJT200.DLL2.00.0000Jet 2.0 engineXBS200.DLL2.00.0000External xBASE ISAMBTRV200.DLL2.00.0000External Btrieve ISAMPDX200.DLL2.00.0000External Paradox ISAM2.5 (accsvc)VBDB300.DLL3.00.0529VB/JET supportMSAJT112.DLL1.99.1605Jet 2.x comp loaderMSAJT200.DLL2.50.1606Jet 2.5 engineMSJETERR.DLL2.50.1108Error servicesMSJETINT.DLL2.50.1108InternationalXBS200.DLL2.50.1108External xBASE ISAMBTRV200.DLL2.50.1108External Btrieve ISAMPDX200.DLL2.50.1108External Paradox ISAM2.5 (VB4 16)VBDB16.DLL4.00.2422VB/JET supportMSAJT200.DLL2.50.1606Jet 2.5 engineMSJETERR.DLL2.50.1111Error servicesMSJETINT.DLL2.50.1111InternationalXBS200.DLL2.50.1117External xBASE ISAMBTRV200.DLL2.50.1117External Btrieve ISAMPDX200.DLL2.50.1117External Paradox ISAMMSXL2016.DLL2.50.1117External Excel ISAMMSTX2016.DLL2.50.1117External Text ISAM3.0MSJT3032.DLL3.0.0.2118Jet 3.0 engineMSJINT32.DLL3.0.0.2118InternationalMSJTER32.DLL3.0.0.2118Error servicesMSXL3032.DLL3.0.0.2001External Excel ISAMMSRD2X32.DLL3.0.0.2118External Jet 2.0 ISAMMSLT3032.DLL3.0.0.2008External Lotus ISAMMSPX3032.DLL3.0.0.2001External Paradox ISAMMSXB3032.DLL3.0.0.2008External xBASE ISAMMSTX3032.DLL3.0.0.2008External Text ISAM
With Jet 3.5x, there have been multiple releases. The following tableindicates which version of Jet applies to a given product:

Access 97Access 97aFileNameOffice 97VB5Office 97aJet 3.51——————————————————–MSEXCL35.DLL3.50.3428.03.50.3602.53.50.3907.0n/aMSJET35.DLL3.50.3428.03.50.3602.43.50.3907.53.51.0623.4MSJINT35.DLL3.50.3428.03.50.3602.53.50.3907.0n/aMSJTER35.DLL3.50.3428.03.50.3602.03.50.3907.0n/aMSLTUS35.DLLn/a3.50.3602.5n/an/aMSPDOX35.DLLn/a3.50.3602.0n/an/aMSRD2X35.DLL3.50.3428.03.50.3602.03.50.3907.0n/aMSREPL35.DLL3.50.3428.03.50.3602.03.50.3907.13.51.0623.0MSTEXT35.DLL3.50.3428.03.50.3602.03.50.3907.0n/aMSXBSE35.DLL3.50.3428.03.50.3602.03.50.3907.0n/aVBAJET32.DLL3.0.0.69085.0.0.71223.0.0.6908n/aVBAR332.DLL3.0.0.69083.0.0.69083.0.0.6908n/a

Symptoms
You can edit the Jet Registry keys to alter the data access behavior of theMicrosoft Jet database engine. However, doing so affects all Jet-basedapplications, and may have unintended negative consequences for some. Byusing custom profiles, you can tune Jet on a per-application basis.
Resolution
In 16-bit versions of Visual Basic and Microsoft Access, Jet settings arestored in an .ini file (for example, Msaccess.ini or Msacc20.ini). You canprovide a custom .ini file with your application that contains settingsspecific to your application.
The 32-bit versions of Jet read their settings from the registry.
In Visual Basic 4.0, 5.0, 6.0, and Jet versions 3.0 and 3.5 use the defaultregistry location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5
If using Microsoft Access, settings are read from the following branchfirst, and then the default location (above) is used for any additionalvalues:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5
To prevent multiple applications from making custom changes to thesesettings and negatively impacting other Jet-based programs, the applicationcan tell Jet to read its settings from a different branch of the registry.These alternate branches are known as Profiles.
You don’t have to reproduce the entire registry branch in your profile.Values you specify in your custom profile override values specified in thedefault registry location, which in turn override internal Jet defaultvalues.
Example ProfileWARNING: Using Registry Editor incorrectly can cause serious problems thatmay require you to reinstall your operating system. Microsoft cannotguarantee that problems resulting from the incorrect use of Registry Editorcan be solved. Use Registry Editor at your own risk.
For information about how to edit the registry, view the “Changing Keys AndValues” Help topic in Registry Editor (Regedit.exe) or the “Add and DeleteInformation in the Registry” and “Edit Registry Data” Help topics inRegedt32.exe. Note that you should back up the registry before you edit it.If you are running Windows NT, you should also update your Emergency RepairDisk (ERD).
This example illustrates registry changes for a custom profile that lowersthe LockRetry setting from the default value of 20 to 5.
In the Registry Editor, expand HKEY_LOCAL_MACHINE\SOFTWARE and add keyvalues for your company name (MyCorp), application name (MyApp), andversion (1.0):
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0The above is sufficient for Visual Basic, but to maintain compatibilitywith Microsoft Access applications, add keys for “Jet” and “3.0″ or”3.5″:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5This is an empty Profile. From here, add keys and values that affectJet’s behavior. To make the LockRetry changes, add the keys “Engines”and “Jet”:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5\Engines\Jet
Then, add a REG_DWORD Value, called LockRetry, and a value of 5.
The profile is complete.
Using the Profile in Visual BasicTo use the profile in Visual Basic, set DBEngine.IniPath to the profilename prior to using any database functionality:
In Visual Basic 4.0:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
In Visual Basic 5.0:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
Using the Profile in Microsoft AccessUnlike visual Basic, where you can set the profile name programmatically,Jet is already initialized by the time your Microsoft Access code isrunning. To specify a profile name, you need to add another registry key tospecify a “friendly” profile name and use a command-line switch whenstarting Microsoft Access:
Expand the following registry branch:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\AccessAdd a key “Profiles”.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Profiles
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\ProfilesAdd a REG_SZ value with the name being your “friendly” profile name(for example, MyProfile) and the value being the registry branch wherethe profile settings are located. For example:

Name:MyProfileType:REG_SZValue: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0Start Microsoft Access with the command-line option:
MSACCESS.EXE /PROFILE “MyProfile” MYDATABASE.MDBMicrosoft Access 95 will automatically choose the …\Jet\3.0 branch,Microsoft Access 97 will automatically choose the …\Jet\3.5 branch, inVisual Basic you must specify the complete profile path.
Because you can’t guarantee that Microsoft Access was started using thecorrect profile, you can check your startup code to see if the userstarted the application properly or just double-clicked the icon for the.mdb file. For example:

If SysCmd(acSysCmdProfile) <> “MyProfile” ThenMsgBox “To use this database, run MyApp from the Start Menu”DoCmd.Quit acExitEnd If
Compatibility with Earlier Jet DatabasesIf the database you’re opening is used by earlier versions of Jet (1.0,1.1, 2.0, or 2.5), Microsoft Jet 3.x doesn’t read these files directly. Ituses an Installable ISAM engine in the same way it would when reading adBase or Paradox file.
For example, to set the LockRetry value for accessing a Jet 2.x database,add the Value to the following location:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet 2.x\ISAM
You still only reference the profile location as indicated in the MicrosoftAccess and Visual Basic usage sections above. For example:
In Visual Basic:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
-or-

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
In Microsoft Access:
Value: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0
Table of Engine KeysThe following table lists registry branches for the Jet 3.x engine and thevarious other engines:

EngineRegistry Branch—————————————————-Jet 3.x…\Engines\JetJet 2.x…\Engines\Jet 2.x\ISAMODBC…\Engines\ODBCXbase…\Engines\XbaseParadox…\Engines\ParadoxExcel…\Engines\ExcelLotus…\Engines\LotusText…\Engines\Text
Jet 3.5 NotesWith Jet 3.5, you can tune some values dynamically using the new SetOptionmethod of the DBEngine object. This means you can use some values for onepart of your program, that is, to favor a bulk update, while using othervalues in another part of your program, that is, to favor interactive dataentry. Using DBEngine.SetOption affects only the current instance of yourprogram. It can be used in Microsoft Access 97 and Visual Basic 5.0.
The following table lists the settings you can change:

SettingConstant—————————————————PageTimeoutdbPageTimeoutSharedAsyncDelaydbSharedAsyncDelayExclusiveAsyncDelaydbExclusiveAsyncDelayLockRetrydbLockRetryUserCommitSyncdbUserCommitSyncImplicitCommitSyncdbImplicitCommitSyncMaxBufferSizedbMaxBufferSizeMaxLocksPerFiledbMaxLocksPerFileLockDelaydbLockDelayRecycleLVsdbRecycleLVsFlushTransactionTimeout dbFlushTransactionTimeout
Usage:

DBEngine.SetOption dbMaxBufferSize, 128

Symptoms
The following error occurs when using RDO or ADO to update datain an Access (Jet) database:

Query is too complexDue to a limitation of the Jet database engine, this error can occur if the recordset to be updated contains morethan 40 fields.
Using the Jet 3.51 engine, the error occurs if the recordset to be updated contains more than 50 fields. With the Jet 4.0 engine and later, the error occurs if the recordset to be updated contains more than 99 fields.
Using the Jet 4.0 engine and later, you may also see the errors:

Expression too complex -or-

Too many defined fieldsThis article describes the problem scenario, the Jet limitation, and several workarounds.
Resolution
When executing the Update() or BatchUpdate() methods of RDO’s rdoResultsetobject, or the Update() or UpdateBatch() methods of ADO’s Recordset object,the ODBC error S1000 “Query is too complex” (Visual Basic run-time error40002) occurs. An error with the same description occurs using the Microsoft Jet OLE DB providers versions 3.51 and later.
This behavior occurs because the default behavior of ADO and RDO is to useeach field in the recordset to determine the record to be updated on theserver. That is, when the update is attempted, a SQL Update query is sentto the server. Part of this query is a WHERE clause that is used toidentify the record to be updated. An AND clause appears within the WHEREclause for each field to be used in that identification. Updating arecordset with more than 40 fields involves a WHERE clause with more than40 ANDs.
The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVINGclause. Jet 3.51 increased this limit to 50 AND clauses, and Jet 4.0 increased the limit to 99 AND clauses. The Access engine returns a “Query is too complex” error whenit encounters such a SQL statement. See the REFERENCES section of thisarticle for more information.
There are several ways to avoid this behavior:For new applications, use DAO when working with Access databases. DAOwas designed for this purpose, and will not issue a SQL query to performthe update. Note that RDO and especially ADO were designed to operateefficiently with many other types of databases and are recommended forapplications that will interact with various servers or that will beupsized in the future to use Microsoft SQL Server, for example.When opening the recordset to be updated (such as the RDO OpenResultsetor ADO OpenRecordset methods), select specific, and 40 or fewer fields.Use a server-side cursor:
To do this in ADO, set the CursorLocation location property of theRecordset object to adUseServer before opening the Recordset:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseServerrs.Open …… In RDO, set the CursorDriver property of the rdoEnvironment objectbefore calling the OpenConnection() method:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseServerSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)… Ensure that a unique key is used to identify the record to be updated,rather than the default behavior of using every field. A unique key(though not necessarily a primary key) must be defined in the underlyingrecordset for this technique to work. To do this in ADO (this will not work in versions prior to ADO2.0), set the Recordset object’s “Update Criteria” property toadCriteriaKey. This will work both for the Update() and UpdateBatch()methods:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseClientrs.Properties(“Update Criteria”).Value = adCriteriaKeyrs.Open …… For RDO, use the ClientBatch cursor library and ensure that theResultset object’s UpdateCriteria property is not set tordCriteriaAllCols. By default, this property is set to rdCriteriaKey,which will cause the use of the primary key to determine the row(s) tobe updated on the server:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseClientBatchSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)rs.Edit…rs.UpdateCriteria = rdCriteriaKeyrs.Update…

Symptoms
When you attempt to connect to a Microsoft Access 2000 database by using either the standard Data control or the Data Form Wizard, you see the following error message where ‘…\databasename.mdb’ is the absolute filename of the Access 2000 database:

Unrecognized database format ‘…\databasename.mdb’”
Resolution
The standard Data control uses Data Access Object (DAO) 3.51 to connect to Access databases by using the Jet 3.51 engine.
The Data Form Wizard uses the Jet 3.51 OLE DB Provider to connect to Access databases by using the Jet 3.51 engine.
The Jet 3.51 engine can connect to Access database versions prior to Access 2000, but cannot connect to Access 2000 databases.

Symptoms
When importing a text file into a Microsoft Jet database through a Microsoft SQL statement or the Microsoft Access TransferText macro command when there is no Schema.ini file present, the Jet engine can sometimes interpret a column with Text data as Currency.
Resolution
The Microsoft Jet database engine examines the column for currency formatting symbols.