You cannot update ADO recordset using Access OLE DB provider in Access 2002 (287455)

RESOLUTION

There are two possible methods you can use to work around this problem:

You can use a server-side cursor.
You can use either or both the Shape or the Jet OLE DB providers to open the connection.

Use a Server-Side Cursor

You can use a server-side cursor instead of a client-side cursor. You can open a client-side cursor by setting the CursorLocation property of the recordset to adUseServer. For an example of this, see the following code segment:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strPath As String
   
'Update the path below to point to the sample
'Northwind.mdb database on your computer.
strPath = "C:\Program Files\Microsoft " & _
 "Office\Office10\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'using the Microsoft Access and Jet OLE DB
'providers.
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strPath
    .Open
End With

'Create a new ADO Recordset by using a server-side
'keyset cursor and optimistic locking.
Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .Source = "SELECT * FROM Categories"
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .Open
End With

Use Either or Both the Shape or the Jet Providers

To work around this problem, do not use the Microsoft Access 10.0 provider. If you have to access the data without shaping services, then you can use only the Jet provider to open the ADO connection. For example, you may use code similar to the following to open an ADO connection with the Jet OLE DB provider.

Dim cn As ADODB.Connection
Dim strPath As String
    
'Update the path below to point to the sample
'Northwind.mdb database on your computer.
strPath = "C:\Program Files\Microsoft " & _
 "Office\Office10\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'using the Microsoft Access and Jet OLE DB
'providers.
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strPath
    .Open
End With

If you need shaping support for hierarchical recordsets, use the Shape (MSDataShape) provider together with the Jet provider to open your ADO connection. For an example of this, see the following code segment:

Dim cn As ADODB.Connection
Dim strPath As String
    
'Update the path below to point to the sample
'Northwind.mdb database on your computer.
strPath = "C:\Program Files\Microsoft " & _
 "Office\Office10\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'using the Microsoft Access and Jet OLE DB
'providers.
Set cn = New ADODB.Connection
With cn
    .Provider = "MSDataShape"
    .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strPath
    .Open
End With

MORE INFORMATION

When you specify a client-side cursor with the Microsoft Access 10.0 and the Jet 4.0 OLE DB providers, the ADO cursor engine overrides the LockType property of the recordset and sets it to adLockReadOnly. This prevents your code from updating the recordset.

The Microsoft Access 10.0 provider is an OLE DB service provider. The Microsoft Access 10.0 provider was created to enhance updateability and functionality with Microsoft SQL Server in Microsoft Access project (.adp) files and in data access pages.

Because Microsoft Access 10.0 provider is a service provider, it does not expose data from a particular data source. It performs additional services for data exposed by an OLE DB data provider. Therefore, you must also specify an OLE DB data provider, such as the Microsoft SQL Server OLE DB provider (SQLOLEDB), or the Microsoft Jet OLE DB provider (Microsoft.Jet.OLEDB.4.0) when you open the ADO connection. The Microsoft Access 10.0 OLE DB provider is only supported for use in Microsoft Access.

Steps to Reproduce the Behavior

Start Microsoft Access.
Create a new, blank database.
On the Insert menu, click Module. This opens the Microsoft Visual Basic Editor and creates a new, blank module.

Add the following ADO code to the module:

Sub UpdateCategories()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strPath As String
    
    'Update the path below to point to the sample
    'Northwind.mdb database on your computer.
    strPath = "C:\Program Files\Microsoft " & _
     "Office\Office10\Samples\Northwind.mdb"

    'Create a new ADO Connection to Northwind
    'using the Microsoft Access and Jet OLE DB
    'providers.
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Access.OLEDB.10.0"
        .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = strPath
        .Open
    End With

    'Create a new ADO Recordset using a client
    'keyset cursor and optimistic locking.
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "SELECT * FROM Categories"
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Open
    End With

    'Try to update the CategoryName field from
    'the table.
    rs.Fields("CategoryName").Value = "Drinks" '<-- Errors here
    rs.Update
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

On the Debug menu, click Compile <Project Name>.
On the File menu, click Save <Project Name>.
On the View menu, click Immediate Window to view the Immediate window.
Type the following in the Immediate window, and then press ENTER:
```
UpdateCategories
					
```

Note that you receive the error message:

Run-time error '3251':

Current Recordset does not support updating. This may be a limitation of the provider or of the selected locktype.