Setting up a recordset - How do I connect to a data source on a server?

[Sandra Walsh]

Hello -

I am trying to speed up my forms by setting up a record set to get a sub-set of data out of several very large ODBC-connected tables.

I am trying to follow the suggestions at
{Illegal link removed}

My code is as follows, but I am getting this error:

Run time error '-2147467259 (80004005)
ODBC--"Connection to 'InterAction Connection' failed.

"InterAction Connection" is the name of the datasource for my ODBC linked tables and requires an SQL Server login when I first load the database.

How do I set up the connection so that I can pass the password (or set up an input box for the password ) so that I can connect to the linked ODBC tables?

Interestingly, I still get this error even when I am connected and have already entered the SQL password when I started the database.

My full code is:

Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & " Data Source=\\Tortaxfs01\market\WorkFlows\US_IMI_Visits\TEST_Recordset_US_IMI_Tracker.accdb;" _
        & " Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "SELECT dbo_INT_AUX_DIR_LIST.LISTING_ID, q_FolderList.DIRECTORY_NM" _
        & " FROM (dbo_INT_AUX_LISTING LEFT JOIN dbo_INT_AUX_DIR_LIST " _
        & " ON dbo_INT_AUX_LISTING.LISTING_ID = dbo_INT_AUX_DIR_LIST.LISTING_ID) " _
        & " LEFT JOIN q_FolderList " _
        & " ON dbo_INT_AUX_DIR_LIST.CONTAIN_DIR_ID = q_FolderList.DIRECTORY_ID" _
        & " WHERE (((q_FolderList.DIRECTORY_NM) Not LIKE '*" & "firm lawyers" & "*' " _
        & " And (q_FolderList.DIRECTORY_NM) Not LIKE '*" & "Companies (Public)" & "*') " _
        & " AND ((dbo_INT_AUX_LISTING.LISTING_TYP_IND)=1))" _
        & " ORDER BY dbo_INT_AUX_DIR_LIST.LISTING_ID "
 
rst.ActiveConnection = conn
 
'to open a recordset a source (strsql) and an ActiveConnection(conn) are provided.
 
rst.Open strsql, conn
 
MsgBox ("open")
 
Set rst = Nothing
Set conn = Nothing
 
End Sub

Much appreciated,
Sandra

[patjones]

Hi,

It looks like you're trying to connect to an Access back end, which in turn contains linked tables to SQL Server.

Have you considered linking to the SQL Server tables directly from your front end? In this way, you can initialize a DAO recordset (for instance) using your SQL string just as you would with any local tables.

Pat