Cannot update the ADO recordset by using Access OLE DB Provider

View products that this article applies to.

Symptoms

When you try to edit a field in an ADO recordset by using Microsoft Office Access 2003 and Microsoft Jet OLE DB Provider, you may receive the following 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.

↑ Back to the top

Cause

The CursorLocation property of the recordset is set to adUseClient to open a client-side cursor.

↑ Back to the top

Workaround

You can work around the problem by using one of the following methods:

Use a Server-Side Cursor

You can use a server-side cursor instead of a client-side cursor. To do this, open the client-side cursor by setting the CursorLocation property of the recordset to adUseServer.

For example, you may use code that is similar to the following code.

Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strPath As String
   
'Update the following path to point to the sample
'Northwind.mdb database on your computer.

strPath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'by using Access and the Jet OLE DB
'provider.

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 Provider or the Jet Provider

Do not use the Access 10.0 provider. If you have to access the data without shaping services, you can use only the Jet provider to open the ADO connection.

For example, you may use code that is similar to the following code to open an ADO connection with the Jet OLE DB provider:

Dim cn As ADODB.Connection
Dim strPath As String
    
'Update the following path to point to the sample
'Northwind.mdb database on your computer.

strPath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'by using Access and the Jet OLE DB
'provider.

Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strPath
    .Open
End With

If you must have the shaping services support for hierarchical recordsets, you can use the Shape (MSDataShape) provider together with the Jet provider to open your ADO connection.

For example, you may use code that is similar to the following code:

Dim cn As ADODB.Connection
Dim strPath As String
    
'Update the following path to point to the sample
'Northwind.mdb database on your computer.

strPath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

'Create a new ADO Connection to Northwind
'by using Access and the Jet OLE DB
'provider.

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

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.

↑ Back to the top

More information

When you specify a client-side cursor with Access 10.0 and with the Jet 4.0 OLE DB provider, the ADO cursor engine overrides the LockType property of the recordset and then sets the LockType property to adLockReadOnly. Therefore, you cannot update the recordset by using code.

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

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

Steps to Reproduce the Behavior

Start Access 2003.
Create a new database.
In the Database window, click Modules under the Objects section.
Click New.

In the Visual Basic Editor, type the following code or paste the following code:

Sub UpdateCategories()
   
	Dim cn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim strPath As String
    
	'Update the following path to point to the sample
	'Northwind.mdb database on your computer.

	strPath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

	'Create a new ADO Connection to Northwind
	'by using Access and the Jet OLE DB
	'provider.

	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 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 <Database Name>.
On the File menu, click Save <Database Name>.
On the View menu, click Immediate Window.
In the Immediate window, type the following code, and then press ENTER:
UpdateCategories
Notice that you receive the error message that is mentioned in the "Symptoms" section.