PRB: Cannot Add Rows to a Replica Database With ADO

• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2
• Microsoft OLE DB Provider for Jet, version 4.0
• Microsoft ODBC Driver for Access, version 4.0

SYMPTOMS

When attempting to insert a row into an Access 97 Design Master or Replica database with either the Microsoft Access ODBC Driver or the Microsoft OLE DB Provider for Jet that ships with MDAC 2.1 GA, the following error occurs:

Operation not supported on replicable databases that have not been converted to the current version.

RESOLUTION

The current workaround is to use the Microsoft Jet OLE DB 3.51 Provider.

STATUS

This behavior is by design. MDAC 2.1 and later service packs for MDAC 2.1 use the Microsoft Jet Engine 4.0. The Microsoft Jet Engine 4.0 allows read/write access only to design master or replica databases in the Microsoft Access 2000 format. If you open a design master or replica database using version 4.0 and the database format is Microsoft Access 97 format (or an earlier format) the database will be read-only.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Access 97.



2. Open an Access 97 MDB.



3. Choose Tools, Replication, and then Create Replica... from the menus.



4. Follow the steps to create a Design Master and Replica database.



5. Start Visual Basic and choose a Project type of Standard EXE. Form1 is created by default.



6. Add a Project Reference to Microsoft ActiveX Data Objects 2.1 Library.



7. Paste the following code into the default form's Load method:




Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'The following two Connection Strings will fail.
'Make sure the path to the MDB points to the Design Master or Replica
cn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}" & _
                       ";DBQ=<path to Design Master or Replica>"
'cn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0" & _
                       ";DATA SOURCE=<path to Design Master or Replica>"
'Uncomment this Connection String for a work around
'cn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.3.51" & _
                      ";DATA SOURCE=<path to Design Master or Replica>"
cn.Open

rs.ActiveConnection = cn
rs.Open "select * from authors", , adOpenDynamic, adLockOptimistic, adCmdText
rs.AddNew
rs.Update

cn.Close
Set cn = Nothing 

8. Save the Project and then execute by pressing the F5 key.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Matthew Hofacker, Microsoft Corporation

Additional query words:

Keywords : kbADO210 kbDatabase kbDriver kbJET kbMDAC kbGrpVBDB kbGrpMDAC
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,4.0
Platform : WINDOWS
Issue type : kbprb