[dba-SQLServer] Trouble Establishing a Connection to Remote SQL Server Tables Using DAO 3.6

Dear Experts:

I am having a terrible time with a Visual Basic code module which accesses three (local) Access 2000 tables and three [remote] SQL Server 2000 backend tables.  I am using the DAO 3.6 object model.  (I know, I should have used the ADO object model instead, but the "dirty deed" has already been done ...)

I have dimensioned three DAO recordset objects, (i.e. rst1, rst2, and rst3) to hold the records from the three [local] Access tables.  I am attempting to get the records from the three [remote] SQL Server tables into rst4, rst5, and rst6 DAO recordset objects.  The problem I'm having is getting a "connection" established to the SQL Server back end so that I can use "Set" statement assignments to the rst4, rst5, and rst6 recordset objects.

I would be very grateful if a sharp troubleshooter could take a look at the following code snippet and impart some wisdom as to why the [recordset] "Connect" method is not working.

Thanks,

Alan C. Lawhon

-----------------------

Option Compare Database

Sub MERGE_NEW_HMIS_RECORDS()
'
'         NOTE1: Initially, until this code has been throughly debugged and checked out,
'                data merge read/writes will be to (local) copies of the Production tables,
'                and NOT to the actual production tables!  Once it is determined that the
'                code is working properly, we'll change the references from the local
'                tables to the actual SQL Server remote back-end tables and add
'                "connection strings" to the rst4, rst5, and rst6 recordset object variables.
'
'
Dim dbs As DAO.Database
'
Dim rst1 As DAO.Recordset       ' Will hold "HMIS_NSN_MAIN"              (HMIS NSN) records
Dim rst2 As DAO.Recordset       ' Will hold "HMIS_NSN_PRODUCTS"          (HMIS Products) records
Dim rst3 As DAO.Recordset       ' Will hold "HMIS_NSN_CONSTITUENTS"      (HMIS Constituents) records
'
Dim rst4 As DAO.Recordset       ' Will hold "EDS_NSN_MAIN_TABLE"         (Production table) records
Dim rst5 As DAO.Recordset       ' Will hold "EDS_NSN_PRODUCTS_TABLE"     (Production table) records
Dim rst6 As DAO.Recordset       ' Will hold "EDS_NSN_CONSTITUENTS"       (Production table) records
'
Dim Temp_PRODUCT_Rst As DAO.Recordset
Dim Temp_CONSTITUENT_Rst As DAO.Recordset
'
'
Dim NSN_Var As String
Dim PRODUCT_NUM_Var As Long
Dim MSDSSRNO_Var As String
'
'
Set dbs = CurrentDb
'
'
Set rst1 = dbs.OpenRecordset("HMIS_NSN_MAIN", dbOpenTable)
Set rst2 = dbs.OpenRecordset("HMIS_NSN_PRODUCTS", dbOpenTable)
Set rst3 = dbs.OpenRecordset("HMIS_NSN_CONSTITUENTS", dbOpenTable)
'
MsgBox "Connection to [local] HMIS tables successfully established."
'
'   Execution "blows up" (halts) on the following statement.  (I'm attempting to access the remote SQL Server back end tables via a "Connect" method.)
'
'
rst4.Connect = "ODBC;DRIVER=SQL Server;SERVER=ETT-SQL;UID=sysuser;PWD=;DATABASE=EDS_REVIEW;Address=ETT-SQL,1433"
rst5.Connect = "ODBC;DRIVER=SQL Server;SERVER=ETT-SQL;UID=sysuser;PWD=;DATABASE=EDS_REVIEW;Address=ETT-SQL,1433"
rst6.Connect = "ODBC;DRIVER=SQL Server;SERVER=ETT-SQL;UID=sysuser;PWD=;DATABASE=EDS_REVIEW;Address=ETT-SQL,1433"
'
'
Set rst4 = dbs.OpenRecordset("EDS_NSN_MAIN_TABLE", dbOpenTable)
Set rst5 = dbs.OpenRecordset("EDS_NSN_PRODUCTS_TABLE", dbOpenTable)
Set rst6 = dbs.OpenRecordset("EDS_NSN_CONSTITUENTS", dbOpenTable)
'
MsgBox "Connection to production tables successfully established."
'
'
rst1.Close
rst2.Close
rst3.Close
rst4.Close
rst5.Close
rst6.Close
'
'
Exit Sub   '  Temporarily exit subroutine until we get connection to production
               '  tables established.
               '
'
'