Search code examples
vbams-accessreadonly

MSAccess 2010 VBA Open a read-only database

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)

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.

What am I doing wrong?

Solution

  • 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

    Following is example of opening a DAO database and recordset objects in a DAO workspace which apparently is not supported after 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)

    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 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'")

    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.