VBA/Excel/Access/Word/Access/OLEDB

Opening a Database in Read-Only Mode

   <source lang="vb">

Sub Open_ReadOnly()

  Dim conn As ADODB.Connection
  Dim strDb As String
  On Error GoTo ErrorHandler
  strDb = CurrentProject.Path & "\mydb.mdb"
  Set conn = New ADODB.Connection
  With conn
     .Provider = "Microsoft.Jet.OLEDB.4.0;"
     .Mode = adModeRead
     .ConnectionString = "Data Source=" & strDb
     .Open
  End With
  Debug.Print "Database was opened for read-only access."
  conn.Close
  Set conn = Nothing
  Debug.Print "Database was closed."
  Exit Sub

ErrorHandler:

  MsgBox Err.Number & ": " & Err.Description

End Sub

</source>
   
  

Opening a Database in Read/Write Mode

   <source lang="vb">

Sub Open_ReadWrite()

  Dim conn As ADODB.Connection
  Dim strDb As String
  On Error GoTo ErrorHandler
  strDb = CurrentProject.Path & "\mydb.mdb"
  Set conn = New ADODB.Connection
  With conn
     .Provider = "Microsoft.Jet.OLEDB.4.0;"
     .Mode = adModeReadWrite
     .ConnectionString = "Data Source=" & strDb
     .Open
  End With
  Debug.Print "Connection was opened."
  conn.Close
  Set conn = Nothing
  Debug.Print "Connection was closed."
  Exit Sub

ErrorHandler:

  Debug.Print Err.Number & ": " & Err.Description

End Sub

</source>
   
  

Opening a Database Secured at the User Level

   <source lang="vb">

Sub Open_WithUserSecurity()

  Dim conn As ADODB.Connection
  Dim strDb As String
  Dim strSysDb As String
  On Error GoTo ErrorHandler
  strDb = CurrentProject.Path & "\mydb.mdb"
  strSysDb = CurrentProject.Path & "\Security.mdw"
  Set conn = New ADODB.Connection
  With conn
     .Provider = "Microsoft.Jet.OLEDB.4.0;"
     .ConnectionString = "Data Source=" & strDb & ";" & _
        "Jet OLEDB:System Database=" & strSysDb
     .Open , "Developer", "WebMaster"
  End With
  conn.Close
  Set conn = Nothing
  Exit Sub

ErrorHandler:

  MsgBox Err.Number & ": " & Err.Description

End Sub

</source>
   
  

Opening a Password-Protected Database

   <source lang="vb">

Sub Open_WithDbPassword()

  Dim conn As ADODB.Connection
  Dim strDb As String
  On Error GoTo ErrorHandler
  strDb = CurrentProject.Path & "\mydb.mdb"
  Set conn = New ADODB.Connection
  With conn
     .Provider = "Microsoft.Jet.OLEDB.4.0;"
     .ConnectionString = "Data Source=" & strDb & ";" & _
          "Jet OLEDB:Database Password=secret;"
     .Open
  End With
  Debug.Print "Password protected database was opened."
  conn.Close
  Set conn = Nothing
  Debug.Print "Database was closed."
  Exit Sub

ErrorHandler:

  MsgBox Err.Number & ": " & Err.Description

End Sub

</source>
   
  

Use OLEDB to connect with mdb file

   <source lang="vb">

"Microsoft ActiveX Data Objects Recordset 2.7 Library Sub ConnectionExample1()

  Dim conn As ADODB.Connection
  Set conn = New ADODB.Connection
  With conn
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & _
       CurrentProject.Path & "\mydb.mdb"
  End With
  conn.Open
  MsgBox "Connection was opened"
  conn.Close
  Set conn = Nothing
  MsgBox "Connection was closed"

End Sub

</source>