FredixBlog

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