ACC97: Run-Time Error '3027' Using ODBCDirect to Open RecordSet

Last reviewed: December 16, 1997
Article ID: Q161252

The information in this article applies to:

Microsoft Access 97

SYMPTOMS

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

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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

CAUSE

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:

   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, or dbOptimisticBatch

For example, the following sample code fragment 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.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

The following example assumes that you have an ODBC data source that opens the Pubs database in Microsoft SQL Server:

Open the sample database Northwind.mdb.

Create a module and type the following procedure:

      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

To test this Procedure, type the following line in the Debug window, and then press ENTER.
NOTE: Substitute your own username, ODBC data source name and password for the UID, DSN and optional PWD arguments in the following example:
```
      ConnectAndAddRecords "TestUser","TestDataSource","TestPwd"
```
Note that the code fails on the rstTmp.AddNew line with the following error message:
```
      Run-time Error '3027':
      Can't update. Database or object is read-only.
```

REFERENCES

For more information about ODBCDirect errors, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q169276
   TITLE     : ACC97: OpenRecordset Method Options Incorrect for ODBCDirect

For more information about ODBCDirect workspaces, search the Help Index for "ODBCDirect workspaces," or ask the Microsoft Access 97 Office Assistant.

For more information about using the OpenRecordset method, search the Help Index for "OpenRecordset method."

Additional query words: prb
Keywords : MdlRcd OdbcHowto OdbcOthr kberrmsg kbusage
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Solution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.