This article was previously published under Q161252
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
When you use an ODBCDirect connection to open a recordset, you may receivethe 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 forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to the "BuildingApplications with Microsoft Access 97" manual.
By default, Microsoft Access opens a read-only recordset in an ODBCDirectworkspace. The read-only recordset gives better performance when you scrollthrough the recordset.
If you want to be able to modify a recordset in an ODBCDirect workspace,you must specify a LockEdits argument with the OpenRecordset method. Thefull 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 followingconstants in the LockEdits argument of the OpenRecordset method:
dbOptimistic, dbPessimistic, dbOptimisticValue, or dbOptimisticBatch
For example, the following sample code fragment opens an editable recordsetthat uses optimistic record locking:
Set RS = conPubs.OpenRecordset("Authors",dbOpenDynamic,0,dbOptimistic)
NOTE: You must supply a zero (0) for the Options argument.
This behavior is by design.
Steps to Reproduce Behavior
The following example assumes that you have an ODBC data source that opensthe Pubs database in Microsoft SQL Server:
Open the sample database Northwind.mdb.
Create a module and type the following procedure:
NOTE: When using the Visual Basic for Applications editor from within other Office 97 applications, you must first ensure that the Microsoft DAO 3.0 or 3.5 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
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: