Steps to Reproduce Behavior

最新推荐文章于 2025-08-08 21:58:17 发布

zdliang

最新推荐文章于 2025-08-08 21:58:17 发布

阅读量754

点赞数

CC 4.0 BY-SA版权

分类专栏： VBA 文章标签： behavior microsoft database string object library

本文链接：https://blog.youkuaiyun.com/zdliang/article/details/919794

VBA 专栏收录该内容

3 篇文章

订阅专栏

本文提供了一种在Microsoft Access中使用VBA连接外部ODBC数据源并添加记录的方法，但遇到“数据库或对象为只读”的运行时错误3027。通过示例代码详细展示了如何创建ODBC连接及可能的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.

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

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.