I have the following code in my Access 2010 Database. There are 32 Linked Tables to SQL Server 2008 R2 Databases. 3 in all
I have 10 tables for strconn3, 1 table for strconn2 and the rest are strconn1. In the autoexec Macro run this code and 4 queries that store the data I need for my reports into tables. This way the data is static for the week. I have my system set up to run this Access Database Every Sunday at Noon. The problem is that it prompts me for the ODBC Driver for the first linked table which is dbo_current_membership. the connection string for that is strconn3. That particular server is set to use Windows Authentication so I'm wondering if I have the string correct. Once I've responded to the prompts the rest of it works fine. Any idea what I'm doing wrong?
Code:
Public Function LinkTablesSQL() As Boolean
On Error Resume Next
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim strConn1 As String
Dim strConn2 As String
Dim strConn3 As String
Dim strLocal As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strConn1 = "ODBC;DSN=EZCap Production;DATABASE=HVVMG;UID=username;PWD=aBCDefgh12!"
strConn2 = "ODBC;DSN=HVVHCC;DATABASE=Hccsql;UID=HPN\username;PWD=aBCDefgh12!"
strConn3 = "ODBC:DSN=HVVEZCAP;DATABASE=HVVMGRPT;UID=HPN\username;PWD=aBCDefgh12!"
' assume success
LinkTablesSQL = True
For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
Select Case tdf.Name
Case Is = "dbo_HCC"
tdf.Connect = strConn2
Case Is = "dbo_current_membership", Is = "dbo_current_pcp_list", Is = "dbo_tbl_hpcodes", Is = "dbo_V_AUTHS_SUMMARY_BY_PCP", "dbo_v_pcp_membership" _
, Is = "dbo_v_spec_auths_by_pcp", Is = "dbo_tbl_testing", Is = "tbl_diabetes_icd9s", Is = "tbl_hedis_codes", Is = "tbl_hpcodes"
tdf.Connect = strConn3
Case Else
tdf.Connect = strConn1
End Select
tdf.RefreshLink
End If
If Err.Number > 0 Then
MsgBox Err.Description, vbInformation, "Error"
LinkTablesSQL = False
Err.Clear
End If
Next
Debug.Print "Finished Relinking !!!"
End Function