ACC2000: Run-Time Error '3027' Using ODBCDirect to Open RecordSet (209943)


The information in this article applies to:

  • Microsoft Visual Basic for Applications 6.0
  • Microsoft Access 2000
This article was previously published under Q209943
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you use an ODBCDirect connection to open a recordset, you may receive the following error message if you use the .AddNew method:
Run-time Error '3027': Can't update. Database or object is read-only.

CAUSE

This behavior can occur because, by default, Microsoft Access opens a read-only recordset in an ODBCDirect workspace. The read-only recordset gives better performance when you scroll through the recordset.

RESOLUTION

If you want to be able to modify a recordset in an ODBCDirect workspace, you must specify a LockEdits argument with the OpenRecordset method. The full syntax for the OpenRecordset method is as follows: 
Set recordset=object.OpenRecordset(source, type, options, lockedits)
You create an editable recordset when you use one of the following constants in the LockEdits argument of the OpenRecordset method:

dbOptimistic
dbPessimistic
dbOptimisticValue
dbOptimisticBatch

For example, the following line of sample code opens an editable recordset that uses optimistic record locking: 
Set RS = conPubs.OpenRecordset("Authors",dbOpenDynamic,0,dbOptimistic)
NOTE: You must supply a zero (0) for the Options argument.

MORE INFORMATION

Steps to Reproduce Behavior

The following example assumes that you have an Open Database Connectivity (ODBC) data source that opens the Pubs database in Microsoft SQL Server.
  1. Start Access and open the sample database Northwind.mdb.
  2. Create a module, and then type the following procedure: NOTE: When you use the Visual Basic Editor from within other Microsoft Office 2000 programs, you must first ensure that the Microsoft DAO 3.6 object library is included in your References. 
    Sub ConnectAndAddRecords(UID as String, DSN as String, _
                         Optional PWD as String)
   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim rstTmp As Recordset

' Create an ODBCDirect workspace.
   Set wrkODBC = CreateWorkspace _
       ("NewODBCWorkspace", "admin", "", dbUseODBC)

' Open a connection to an ODBC data source.
   Set conPubs = wrkODBC.OpenConnection("Connection1", _
       dbDriverNoPrompt,,"ODBC;DATABASE=pubs;UID=" & UID _
       & ";PWD=" & PWD & ";DSN=" & DSN)

' Open a recordset which is read-only by default.
   Set rstTmp = conPubs.OpenRecordset("Authors", dbOpenDynamic)

   rstTmp.AddNew
   rstTmp!au_id = "111-11-1111"
   rstTmp!au_lname = "Dickens"
   rstTmp!au_fname = "Charles"
   rstTmp!Contract = 0
   rstTmp.Update
   rstTmp.Close
   conPubs.Close
   wrkODBC.Close
End Sub
  3. To test this procedure, type the following line in the Immediate window, and then press ENTER: NOTE: Substitute your own username, ODBC data source name and password for the UID, DSN, and optional Password arguments in the following example:

    ConnectAndAddRecords "UID","DSN","Password"

Note that the code fails on the rstTmp.AddNew line with the following error message:
Run-time Error '3027': Cannot update. Database or object is read-only.

REFERENCES

For more information about ODBCDirect, click Microsoft Visual Basic Help on the Help menu, type differences between odbc drivers and built-in drivers for external data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the ODBCConnectStr property, click Microsoft Visual Basic Help on the Help menu, type odbcconnectstr property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kberrmsg kbprb kbProgramming kbusage KB209943
©2004 Microsoft Corporation. All rights reserved.