{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:
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.
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.