Help and Support
 

powered byLive Search

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

View products that this article applies to.
Article ID:287455
Last Review:August 12, 2004
Revision:3.1
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).

On This Page

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.

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

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.

Back to the top

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

Back to the top

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

Back to the top

STATUS

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

Back to the top

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.

Back to the top

Steps to Reproduce the Behavior

1.Start Microsoft Access.
2.Create a new, blank database.
3.On the Insert menu, click Module. This opens the Microsoft Visual Basic Editor and creates a new, blank module.
4.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
5.On the Debug menu, click Compile <Project Name>.
6.On the File menu, click Save <Project Name>.
7.On the View menu, click Immediate Window to view the Immediate window.
8.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.

Back to the top

APPLIES TO
•Microsoft Access 2002 Standard Edition

Back to the top

Keywords: 
kbnofix kbprb KB287455

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.