Below is an example of how to use adodb. If you put this code into a module, you can resued FOpen over and over. All you will have to do is use the returned Record Set.
Option Explicit
Public Const CONNSTRING = "Provider=SQLOLEDB.1;Password=*******;User ID=*****;"
Public cn As New ADODB.Connection
Public cnnstring As String
Public rs As New ADODB.Recordset
Public cmd As New ADODB.Command 'insert command
Public Function fOpenDB(squery As String, strDbName As String, server As String) As adodb.Recordset
Dim strCnn As String
'On Error GoTo ErrorHandler
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.CursorLocation = adUseClient
cnnstring = CONNSTRING & "Data Source=" & server
strCnn = cnnstring
cn.Open strCnn
cn.DefaultDatabase = strDbName
Debug.Print squery
rs.Open squery, cn, adOpenDynamic, adLockOptimistic
set fOpenDB = rs
End Function
Public Function CloseDB()
On Error Resume Next
If Not rs Is Nothing Then
If rs.State adStateClosed Then
rs.Close
End If
Set rs = Nothing
If cn.State adStateClosed Then
cn.Close
End If
Set cn = Nothing
End If
End Function
