Microsoft KB Archive/287455

= You cannot update ADO recordset using Access OLE DB provider in Access 2002 =

Article ID: 287455

Article Last Modified on 8/12/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287455



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you try to edit a field in an ADO recordset by using the Microsoft Access and the Microsoft Jet OLE DB providers, you receive the following error:

Run-time error '3251':

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



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



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 = &quot;C:\Program Files\Microsoft &quot; & _ &quot;Office\Office10\Samples\Northwind.mdb&quot;

'Create a new ADO Connection to Northwind 'using the Microsoft Access and Jet OLE DB 'providers. Set cn = New ADODB.Connection With cn   .Provider = &quot;Microsoft.Access.OLEDB.10.0&quot; .Properties(&quot;Data Provider&quot;).Value = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).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 = &quot;SELECT * FROM Categories&quot; .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 = &quot;C:\Program Files\Microsoft &quot; & _ &quot;Office\Office10\Samples\Northwind.mdb&quot;

'Create a new ADO Connection to Northwind 'using the Microsoft Access and Jet OLE DB 'providers. Set cn = New ADODB.Connection With cn   .Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).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 = &quot;C:\Program Files\Microsoft &quot; & _ &quot;Office\Office10\Samples\Northwind.mdb&quot;

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



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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 = &quot;C:\Program Files\Microsoft &quot; & _ &quot;Office\Office10\Samples\Northwind.mdb&quot;

'Create a new ADO Connection to Northwind 'using the Microsoft Access and Jet OLE DB   'providers. Set cn = New ADODB.Connection With cn       .Provider = &quot;Microsoft.Access.OLEDB.10.0&quot; .Properties(&quot;Data Provider&quot;).Value = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).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 = &quot;SELECT * FROM Categories&quot; .CursorType = adOpenKeyset .LockType = adLockOptimistic .CursorLocation = adUseClient .Open End With

'Try to update the CategoryName field from 'the table. rs.Fields(&quot;CategoryName&quot;).Value = &quot;Drinks&quot; '<-- Errors here rs.Update rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub  On the Debug menu, click Compile <Project Name>.</li> On the File menu, click Save <Project Name>.</li> On the View menu, click Immediate Window to view the Immediate window.</li>  Type the following in the Immediate window, and then press ENTER: UpdateCategories </li></ol>

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.

Additional query words: pra

Keywords: kbnofix kbprb KB287455

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.