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

• Opening an ADO recordset
• Windows 7: Désactiver l’UAC
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Custom Autonumber Access 2000
• BuildQuery()

If you are like me, always needing ADO recordsets but your memory is still weak?
Here are the lines of code to open ADO recordset against a MS-SQL Server database.
In addition, it address either the development server/database if the Access frontend is stored in a development environment, or the production database/server when in production. No need to bother about the SQL-Server database: When in development, your frontend’s folder just has to contain the word “Devel” in it.

' Declaring the ADODB objects
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

' Opening the connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open "driver={SQL Server};DSN=" & Get_Db(CurrentDb.Name) & ";server=" & Get_Server(CurrentDb.Name) & ";DATABASE=" & Get_Db(CurrentDb.Name) & ";APP=Microsoft® Access;Trusted_Connection=Yes;Connect Timeout=0"

' Opening the recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from [<name of the table>]", conn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then rs.MoveFirst

Here are the two 'environment sensitive' functions:

Public Function Get_Db(dbName As String)
    If InStr(UCase(dbName), UCase("Devel")) = 0 Then
     Get_Db = "<name of the ODBC DSN for the PRODUCTION database>"
    Else
        Get_Db = "<name of the ODBC DSN for the DEVELOPMENT database>"
    End If
End Function

Public Function Get_Server(dbName As String)
    If InStr(UCase(dbName), UCase("Devel")) = 0 Then
        Get_Server = "<name of the server for the PRODUCTION>"
    Else
        Get_Server = "<name of the server for the DEVELOPMENT>"
    End If
End Function

• Get Jet Connection String
• How can I e-mail a file from Microsoft Access?
• Custom Autonumber Access 2000