FredixBlog

Need to connect to an Access database with ADO? Using workgroup security? Here is a function which will return an opened connection to the jet database:

Public Function GetJetConnection(strPath As String, _
         Optional strDBPwd As String, _
         Optional strSysDBPath As String, _
         Optional strUserID As String, _
         Optional strUserPwd As String) _
         As ADODB.Connection
   Dim cnnDB As ADODB.Connection
  
   Set cnnDB = New ADODB.Connection
   With cnnDB
      .Provider = “Microsoft.Jet.OLEDB.4.0″
      ‘.Mode = lngMode
      .Properties(”Jet OLEDB:Database Password”) = strDBPwd
      .Properties(”Jet OLEDB:System Database”) = strSysDBPath
      ‘.Properties(”Jet OLEDB:Engine Type”) = lngEngineType
      .Open ConnectionString:=strPath, _
            UserID:=strUserID, _
            Password:=strUserPwd
   End With

   Set GetJetConnection = cnnDB
End Function

Need an example? Here it is, connecting to the current database:

     Set rs = New ADODB.Recordset
    rs.Open “Select * from <TableName>”, _
        GetJetConnection(CurrentDb.Name, , <system DB path>, <user>, <password>), _
        adOpenKeyset, adLockOptimistic