Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with multiple TransferDatabase - cached connection

Status
Not open for further replies.

Gator621

Programmer
Oct 6, 2010
3
US
{Reposted as suggested, originally in Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) Forum)

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:


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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top