MSAccess 2010 VBA打开一个只读数据库

问题描述

我有一个MSAccess数据库，我正尝试从位于只读文件夹中的其他MSAccess数据库中提取存档数据.所以...

I have a MSAccess database that I'm trying to pull archived data into, from other MSAccess databases that live in read-only folders.So ...

Dim aidbx As DAO.Database
Dim stDB as STring
 stDB = 'path to read-only database
...
Set aidbx = OpenDatabase(stDB, False, True)

因此即使'True'告诉它以只读方式打开数据库，它也会在此处弯曲.我收到运行时错误3050-无法锁定文件"错误消息.

So it craters right there, even though the 'True' is telling it to open the database read-only. I get the 'Run time error 3050 - could not lock file' error message.

我在做什么错了?

推荐答案

您已经知道如何设置ADODB连接，因为您已经在Excel代码中进行了设置.应该可以在Access VBA中使用相同的功能.使用早期绑定的示例，因此需要设置对Microsoft ActiveX Data Objects x.x Library的引用:

You already know how to set ADODB connection since you have that in Excel code. Should be able to use the same in Access VBA. Example using early binding so need to set reference to Microsoft ActiveX Data Objects x.x Library:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\June\Sample.accdb'"
rs.Open "SELECT * FROM Table1", cn, adOpenStatic, adLockReadOnly

以下是在DAO工作空间中打开DAO数据库和记录集对象的示例，在Access 2013中显然不支持该操作:

Following is example of opening a DAO database and recordset objects in a DAO workspace which apparently is not supported as of Access 2013:

Dim DAOws As DAO.Workspace
Dim DAOdb As DAO.Database
Dim DAOrs As DAO.Recordset
Set DAOws = DBEngine.Workspaces(0)
Set DAOdb = DAOws.OpenDatabase("C:\Users\June\Sample.accdb")
Set DAOrs = DAOdb.OpenRecordset("SELECT * FROM Table1", dbOpenSnapshot)

使用DAO数据库和记录集对象但不使用工作空间的示例:

Example using DAO database and recordset objects but not workspace:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\Users\June\Sample.accdb")
Set rs = db.OpenRecordset("Table1")

Access VBA可以打开一个记录集对象，该记录集对象可以使用CurrentDb对象和IN运算符从另一个数据库中获取数据，而无需连接和其他数据库对象变量.示例:

Access VBA can open a recordset object that pulls data from another database without connection and other database object variables by using the CurrentDb object and IN operator. Example:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1 IN 'C:\Users\June\Sample.accdb'")

这里是对结合使用SQL和SQL的最后一种方法的测试:

Here is a test of last approach using SQL with JOIN:

Set rs = CurrentDb.OpenRecordset("SELECT Submit.*, [103].*
         FROM Submit INNER JOIN [103] ON Submit.LabNum=[103].LabNum
         IN 'C:\Users\June\Sample.accdb'", dbOpenSnapshot)

这些方法中的任何方法都可以引用查询而不是表作为源.

Any of these methods can reference a query instead of table as source.

这篇关于MSAccess 2010 VBA打开一个只读数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！