Microsoft KB Archive/824256

= Cannot update the ADO recordset by using Access OLE DB Provider =

Article ID: 824256

Article Last Modified on 6/9/2004

-

APPLIES TO


 * Microsoft Office Access 2003

-



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

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

For a Microsoft Access 2002 version of this article, see 287455.



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.



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



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

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

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

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

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

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

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 in the &quot;Applies to&quot; section of this article.



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

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

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 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 </li> On the Debug menu, click Compile .</li> On the File menu, click Save .</li> On the View menu, click Immediate Window.</li>  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 &quot;Symptoms&quot; section. </li></ol>

Additional query words: ACC2003 Access 2003

Keywords: kberrmsg kbdatabase kbprogramming kbprb kbcursor kbado KB824256

-

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

© Microsoft Corporation. All rights reserved.