VBA/Excel/Access/Word/Access/OLEDB

Opening a Database in Read-Only Mode

 
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

Opening a Database in Read/Write Mode

 
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

Opening a Database Secured at the User Level

 
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

Opening a Password-Protected Database

 
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

Use OLEDB to connect with mdb file

 
"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