Can I change a Linked Table with VB?

You have two options that I can see.

1.  Relink the tables when the database is first open pointing to the new fileDSN using VBA.

2. Use a system or user DSN and set Access to create the DSN when the database first runs.

I personally use option 2 on all my remote applications.  It first detects if the DSN exists, and if it doesn't it creates it.  It is very quick and seamless, the users won't even realise it's happening.  I'll post the code just in case you like the idea.



'@@@    REGISTRY DECLARATIONS
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
    "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
    phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
    "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
    ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
    cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal hKey As Long) As Long
'@@@    END OF REGISTRY DECLARATIONS

'@@@    ODBC DECLARATIONS
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, pcbDescription%) As Integer

Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1
'@@@    END OF ODBC DECLARATIONS

Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

Public Function CreateODBC(blnMessages As Boolean)

    'DSN Declarations
   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String

   Dim lResult As Long
   Dim hKeyHandle As Long

   'Specify the DSN parameters.
   DataSourceName = "NMC-BIS Voice"
   DatabaseName = "Voice"
   Description = "Connection for District Telecom's Database"
   'DriverPath = "SQL Server"
   LastUser = "Voice_Access"
   Server = strServerIP
   DriverName = "SQL Server"

   'Create the new DSN key.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.
   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))

   'Close the new DSN key.
   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)

    If blnMessages = True Then _
    MsgBox "The ODBC Driver was created successfully", vbInformation + vbOKOnly, "ODBC Created"
   
Exit Function

errHandler:
   
    MsgBox "The ODBC Driver was not created.  Please see your system administrator.", vbCritical + vbOKOnly, "ODBC Error"

End Function

Public Function CheckODBC(strODBC As String)
   On Error Resume Next
   
   Dim i As Integer
   Dim sDSNItem As String * 1024
   Dim sDRVItem As String * 1024
   Dim sDSN As String
   Dim sDRV As String
   Dim iDSNLen As Integer
   Dim iDRVLen As Integer
   Dim lHenv As Long         'handle to the environment

   'get the DSNs
   If SQLAllocEnv(lHenv) <> -1 Then
        Do Until i <> SQL_SUCCESS
           sDSNItem = Space(1024)
           sDRVItem = Space(1024)
           i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, iDSNLen, sDRVItem, 1024, iDRVLen)
           sDSN = VBA.Left(sDSNItem, iDSNLen)
           'check to see if ODBC exists
           If sDSN <> Space(iDSNLen) Then
                If sDSN = strODBC Then Exit Function
           End If
        Loop
   
        'ODBC Not Found, Create without any messages
        CreateODBC False
    End If

End Function

Okay Drizzt95m, I appreciate your response, but I have to say it is well above my understanding.  I have learned a good bit from experience, but I haven't been schooled on it or anything.  Specifically the registry declarations, but some of the rest too.

...in the meantime, I have solved my problem with the code I will post below.  Since you appear to have been doing this kinda thing for a while, maybe you could help me with a slightly different issue?

*******************************START******************************
Private Sub RelinkTables_Click()
Dim OpEx as Database
Dim Path as String
Dim Cont as Container
Dim Docs(3) as Document
Dim RLnk(3) as TableDef
Dim Link(3) as String

Set OpEx = CurrentDb
Path = "ODBC;DSN=OpExDevelopment;DATABASE=OpEx"
Set Cont = OpEx.Containers!Tables

Link(0) = "05a_NVH"
Link(1) = "05b_NVH"
Link(2) = "06x_VND"
Link(3) = "07a_WOS"

If opexIsTbl(Link(0)) = True Then
DoCmd.DeleteObject acTable, Link(0)
End If

If opexIsTbl(Link(1)) = True Then
DoCmd.DeleteObject acTable, Link(1)
End If

If opexIsTbl(Link(2)) = True Then
DoCmd.DeleteObject acTable, Link(2)
End If

If opexIsTbl(Link(3)) = True Then
DoCmd.DeleteObject acTable, Link(3)
End If

Set RLnk(0) = OpEx.CreateTableDef(Link(0))
      RLnk(0).Connect = Path
      RLnk(0).SourceTableName = Link(0)

Set RLnk(1) = OpEx.CreateTableDef(Link(1))
      RLnk(1).Connect = Path
      RLnk(1).SourceTableName = Link(1)

Set RLnk(2) = OpEx.CreateTableDef(Link(2))
      RLnk(2).Connect = Path
      RLnk(2).SourceTableName = Link(2)

Set RLnk(3) = OpEx.CreateTableDef(Link(3))
      RLnk(3).Connect = Path
      RLnk(3).SourceTableName = Link(3)

OpEx.TableDefs.Append RLnk(0)
OpEx.TableDefs.Append RLnk(1)
OpEx.TableDefs.Append RLnk(2)
OpEx.TableDefs.Append RLnk(3)

Set Docs(0) = Cont.Documents(Link(0))
      Docs(0).UserName = "RestrictFull"
      Docs(0).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(1) = Cont.Documents(Link(1))
      Docs(1).UserName = "RestrictFull"
      Docs(1).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(2) = Cont.Documents(Link(2))
      Docs(2).UserName = "RestrictFull"
      Docs(2).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(3) = Cont.Documents(Link(3))
      Docs(3).UserName = "RestrictFull"
      Docs(3).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

MsgBox "All tables have been successfully relinked.", vbOkonly
End Sub

'''opexIsTbl (used above) is another function to see if a Table exists in the current database.
********************************END*******************************

This code is working for me, but I trimmed it up a bit to post here and show you.  I actually have 14 linked tables, instead of the 4 shown above.  With all that repetition, I would think there HAS to be a better way to structure it.  I was trying to decipher the help on "For...next" and "For....each....next", but I'm not quite getting it.

From what you see here, can you tell me a better way to write it, that would take up less space?

Drizzt95

Below is the code I was going to settle on, but yours looks like sound advice too.  Is this the type of thing that you've found "does not always work?"

The Container was necessary for setting the permissions after the links are created:
ie. Set Docs(x) = Cont.Documents(Link(x))

*******************************START******************************
Private Sub RelinkTables_Click()
Dim OpEx as Database
Dim Path as String
Dim Cont as Container
Dim Docs(3) as Document
Dim RLnk(3) as TableDef
Dim Link(3) as String

Set OpEx = CurrentDb
Path = "ODBC;DSN=OpExDevelopment;DATABASE=OpEx"
Set Cont = OpEx.Containers!Tables

Link(0) = "05a_NVH"
Link(1) = "05b_NVH"
Link(2) = "06x_VND"
Link(3) = "07a_WOS"

for x = 0 to 3
  If opexIsTbl(Link(x)) = True Then
    DoCmd.DeleteObject acTable, Link(x)
  End If

Set RLnk(x) = OpEx.CreateTableDef(Link(x))
      RLnk(x).Connect = Path
      RLnk(x).SourceTableName = Link(x)

OpEx.TableDefs.Append RLnk(x)

Set Docs(x) = Cont.Documents(Link(x))
      Docs(x).UserName = "RestrictFull"
      Docs(x).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

next x

MsgBox "All tables have been successfully relinked.", vbOkonly
End Sub
********************************END*******************************

Thanks for all your help,
Chris

No, that would also work fine, I wasn't sure which way you would prefer.  The concept is similar to the function I created, it's just a matter of personal preference.

I would also recommend if you get a chance to check the code (auto ODBC Creation) out that I originally posted if you get any spare time.  It's not as confusing as it looks, and can save you a lot of time, but you still have to relink the tables, so it doesn't remove that step.

If you do get around to it, the only lines you have to understand is:

 'Specify the DSN parameters.
   DataSourceName = "NMC-BIS Voice"
   DatabaseName = "Voice"
   Description = "Connection for District Telecom's Database"
   'DriverPath = "SQL Server"
   LastUser = "Voice_Access"
   Server = strServerIP
   DriverName = "SQL Server"

Thanks
Drizzt