Linked Tables

[Tieval]

I have created (with a little help from Andrew Couch) a piece of code to change the linked tables.

Private Sub Command32_Click()
Dim tdef As TableDef
Dim Strnew As String
Strnew = ";database=C:\data\data2.accdb"
For Each tdef In CurrentDb.TableDefs
If Left(tdef.Connect, Len(";Database=")) = ";database=" Then
If InStr(tdef.Connect, "data1.accdb") Then
Debug.Print tdef.Name; tdef.Connect
tdef.Connect = Strnew
tdef.RefreshLink
End If
End If
Next
End Sub

Can anyone advise how I can get this to pickup the new path from a messagebox, instead of changing the path to c:\data\data2.accdb I am trying to make this user selectable.

 

[Uncle Gizmo]

Could you use a form instead of a message box?

 

[Tieval]

Yes, I already have the ability to sort the file name onto a form and now just need to insert it into the code.

 

[Uncle Gizmo]

How do you store the filename?

 

[Tieval]

I have a listbox on my form using the following code:

Private Sub Command6_Click()
'Requires reference to Microsoft Office 12.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
   'Clear listbox contents.
   'Me.FileList.RowSource = ""
 
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = True
             
      'Set the title of the dialog box.
      .Title = "Choose Database Network Location"
 
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Database", "*.accdb"
      .Filters.Add "All Files", "*.*"
 
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to the list box.
         For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
         Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Sub

 

[Uncle Gizmo]

I think this thread from ghudson may offer an alternative, although I haven't used it myself...

Browse [Find a directory or file]

http://www.access-programmers.co.uk/forums/showthread.php?t=97787

 

[Tieval]

Unfortunately the database will not open in Access 2013 but I am guessing will only return a value in the same way but possibly in a better way.

How can I get the original code I used to accept "FileList" as the path and file name?

 

[Tieval]

I should add that I force the first entry in the listbox as follows:

Me.FileList.SetFocus
Me.FileList.Selected(0) = True

 

[Uncle Gizmo]

Post a message I'm sure someone will change the file to a later format for you.

 

[Tieval]

Well I have cracked it:

Private Sub Command0_Click()
Me.FileList.SetFocus
Me.FileList.Selected(0) = True
'If file name has been selected (Default value is 1 so must be longer to show selection)
If Len(Me.FileList.Value) > 1 Then
Dim tdef As TableDef
Dim Strnew As String
Strnew = ";database=" & Me.FileList
For Each tdef In CurrentDb.TableDefs
If Left(tdef.Connect, Len(";Database=")) = ";database=" Then
If InStr(tdef.Connect, "data1.accdb") Then
Debug.Print tdef.Name; tdef.Connect
tdef.Connect = Strnew
tdef.RefreshLink
End If
End If
Next
End If
End Sub

One easy question (I hope), how can you make it change any link as long as it is an external link. Basically I want to change all links except those that are tables rather than linked tables.

 

[Tieval]

Actually I have done it now:

Private Sub Command0_Click()
Me.FileList.SetFocus
Me.FileList.Selected(0) = True
'If file name has been selected (Default value is 1 so must be longer to show selection)
If Len(Me.FileList.Value) > 1 Then
Dim tdef As TableDef
Dim Strnew As String
Strnew = ";database=" & Me.FileList
For Each tdef In CurrentDb.TableDefs
If Left(tdef.Connect, Len(";Database=")) = ";database=" Then
If Not InStr(tdef.Connect, "data1.accdb") Then
Debug.Print tdef.Name; tdef.Connect
tdef.Connect = Strnew
tdef.RefreshLink
End If
End If
Next
End If
End Sub

As usual much deep thought went nowhere and I ended up using a simple "If Not"