Problem with multiple TransferDatabase - cached connection

[Gator621]

I am having an issue where it appears Access is caching DNS information for a named DNS. I have attempted to modify the DNS with each pass, but it appears that it is not working.

This code is supposed to import two different tables from nine different companies. The ODBC driver only supports one company at a time. Here is the code:

Function RefreshExtendedDescriptionData()

Dim User, Comp, Pw

Dim MASTables(2) As String
MASTables(1) = "ExtendedDescription"
MASTables(2) = "Item"

Dim Companies(9) As String
Companies(1) = "001"
Companies(2) = "002"
Companies(3) = "003"
Companies(4) = "004"
Companies(5) = "005"
Companies(6) = "007"
Companies(7) = "009"
Companies(8) = "010"
Companies(9) = "011"

For x = 1 To 9
    For y = 1 To 2
        User = "user"
        Comp = Companies(x)
        Pw = "password"
        Dim objShell
        Set objShell = CreateObject("WScript.Shell")
        RegLocate = "HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\SOTAMAS90\"
        objShell.RegWrite RegLocate & "UID", User
        objShell.RegWrite RegLocate & "PWD", Pw
        objShell.RegWrite RegLocate & "COMPANY", Comp
        Set objShell = Nothing
        DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=SOTAMAS90; COMPANY=" + Comp + "; TABLE=CI_" + MASTables(y), acTable, "CI_" + MASTables(y), Companies(x) + MASTables(y), False
    Next y
Next x

End Function

Is there a way to make Access forget the DNS? I am verifying that the DNS is actually changing, but Access continues to use the first DNS. The result is that I can only import from the first company. If I manually import from one company at a time (changing the x,y values) and close Access in between imports, then I can get to multiple companies.

Any help would be appreciated.

 

[SkipVought]

Please ask MS Access questions in MS Access forums like forum705, for best results.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gator621]

Thanks. I reposted it.