CODEWITHSUNDEEP
excelvbaadodbms-jet-ace
Krisztian
Krisztian

Reputation: 21

connecting to a read-only Access db from Excel VBA

I have been a long-time visitor to stackoverflow, but this is my first question.

I'm trying to query an Access 2010 database in a folder to which the user has only read-only access.

     strDBPath = <full path of accdb>

     Set myConn = New ADODB.Connection
     myConn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _ 
                 "Mode=Read; Data Source=" & strDBPath

     Set myRS = New ADODB.Recordset
     myRS.ActiveConnection = myConn

The myConn.Open line gives a "file currently in use" error. If I move the accdb to a folder that the user can write, the code runs fine.

Thanks in advance for any advice!

Upvotes: 2

Views: 3240

Answers (2)

AllanBradley
AllanBradley

Reputation: 11

Access to database in a read only folder is possible (of course Mode=Read) but only if this database is not used by any other user (it means there is no .ldb file). But then, there is only one user able to have an access. So anyone calling database (even if it is done by excel) locking it for any others. :-(

Upvotes: 0

Kjenos
Kjenos

Reputation: 559

Wie have also a connection with an Access DB but im quite sure we have also write permissions on the folder.

The Code we use is here.

   Dim DB As DAO.Database
   Dim QRY As DAO.QueryDef
   Dim Rs As DAO.Recordset
   Public Ersteller As String

   'Prüft die Verbindung zur DB
   Public Function OpenDB() As Integer

      'Informationen über Database'
      Dim Database As Variant
      Database = Worksheets("Anträge").Range("B3").Value

      'Check Datenbank Verbindung
      On Error Resume Next
      Set DB = CreateObject("DAO.DBEngine.120").OpenDatabase(Database)
      If Err.Number <> 0 Then
          MsgBox "Keine Verbindung zur Datenbank möglich!"
      End If
      OpenDB = Err.Number

   End Function

Mayebe it helps. :)

Upvotes: 0

Related Questions