Switching DSNs in a pass thru query multiple times (Access 97)

[wyld]

In my Access 97 database, I have a table 'Claim_Files' that contains information about specific people in multiple groups. I want to take this table and get all their claim information. Their claim information is stored based on what group they belong to. Each group is a separate table stored elsewhere. Because of size limits (it's a long story), I have 10 DSNs to chose from based on the group number. When I try to change from one DSN to another, I get an ODBC error saying the table cannot be found:
Run-time error '3146' ODBC-- call failed

For example, all the A1xx tables are processed, but when it tries to switch to table A200, it give the ODBC error message. (The input is stored on group number so it doesn't keep switching between DSNs every time)

I know it's because the .CONNECT isn't changing the DSN when appAll_Claims tries to execute (appAll_Claims uses qryPassThru as the input table).

Any suggestions??? Thanks in advance



Function Build_All_Claims_file()
'On Error GoTo Err_Build_All_Claims
Dim strSQL As String
Dim strSQL2 As String
Dim rstTbl As Recordset
Dim rstSSN As Recordset
Dim qryPassThru As QueryDef
Dim strTbl As String
Dim cntConnect As Connection

'Create recordset of all tables in Claim_Files so can link to the proper table
Set rstTbl = CurrentDb.OpenRecordset("Select orig_table From Claim_Files Group By orig_table&quot

DoCmd.SetWarnings False

'Cycle through all the tables in Claim_Files (stored in rstTbl recordset)
Do While Not rstTbl.EOF
'Create recordset of all individuals in Claim_Files so can get all claims from each unique table
strSQL = "Select GROUP, PARTIC, DEPNO From claim_Files Where orig_table = '" _
& rstTbl("orig_table&quot & "' Group by GROUP, PARTIC, DEPNO"
Set rstSSN = CurrentDb.OpenRecordset(strSQL)
'Get data from each unique table for the individual (stored in rstSSN recordset)
Do While Not rstSSN.EOF
'Rebuild qryPassThru with actual SSN and dep code
strSQL2 = "Select * From " & rstTbl("orig_table&quot & " where " _
& "clpartic = '" & rstSSN("PARTIC&quot & "' And cldepno = '" & rstSSN("DEPNO&quot & "'"
'Set reference to qryPassThru
Set qryPassThru = CurrentDb.QueryDefs("qryPassThru&quot
'Change code in qryPassThru (used in appAll_Claims)
qryPassThru.SQL = strSQL2
'Determine which link to use based on 1st digit of group number
strTbl = Mid(rstTbl("orig_table&quot, 2, 1)
Select Case strTbl
Case 0, qryPassThru.Connect = "ODBC;DSN=dsm0"
Case 1, qryPassThru.Connect = "ODBC;DSN=dsm1"
Case 2, qryPassThru.Connect = "ODBC;DSN=dsm2"
Case 3, qryPassThru.Connect = "ODBC;DSN=dsm3"
Case 4, qryPassThru.Connect = "ODBC;DSN=dsm4"
Case 5, qryPassThru.Connect = "ODBC;DSN=dsm5"
Case 6, qryPassThru.Connect = "ODBC;DSN=dsm6"
Case 7, qryPassThru.Connect = "ODBC;DSN=dsm7"
Case 8, qryPassThru.Connect = "ODBC;DSN=dsm8"
Case 9, qryPassThru.Connect = "ODBC;DSN=dsm9"
End Select
'Run query appAll_Claims (uses qryPassThru as input)
DoCmd.OpenQuery "appAll_Claims"
'Go to next SSN record
rstSSN.MoveNext
Loop
'Go to next Tbl record
rstTbl.MoveNext
Loop

'Get rid of reference points
Set rstTbl = Nothing
Set rstSSN = Nothing
Set qryPassThru = Nothing
DoCmd.SetWarnings True

Exit Function

'Err_Build_All_Claims:
' Resume Next
End Function