Opening an ADO recordset
Posted by: fredometro on
May 25th, 2007 |
Filed under: MS-Access
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
No related posts.

Tags:
Add A Comment
You must be logged in to post a comment.