Microsoft access not a valid path error

[shod90]

Dear Gents ,


I have a working access database and the only problem i need is to run a code to check if the backend is in it's valid location or changed , If changed i need a form to pop out to the user to change the backend location so that the application continue working instead of crash .
Can anyone help ?

[June7]

Why would the backend location change? Does this happen frequently?

Create a VBA function that is called by an AutoExec macro or an UNBOUND form that opens by default.

VBA like:

Dim fDialog As Office.FileDialog
Dim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
Set db = CurrentDb
strOld = Mid(db.TableDefs("yourtablename").Connect, 11)

If Dir(strOld) = "" Then
   ' set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' restrict user to single selection in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .title = "Please select one file"
      .InitialFileName = "C:\" 'use appropriate drive letter
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access file", "*.accdb"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked a file. If the .Show method returns False, user clicked Cancel
      If .Show = True Then
         strNew = .SelectedItems(1)
         'reset backend links path
         For Each td In db.TableDefs
            If InStr(td.Connect, strOld) > 0 Then
                Debug.Print td.Name
                Debug.Print "Old Link: " & td.Connect
                td.Connect = Replace(td.Connect, strOld, strNew)
                td.RefreshLink
                Debug.Print "New Link: " & td.Connect
            End If
         Next td
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End If

[shod90]

Super !! , It works with me after adding microsoft office 16.0 object library ..Really thanks !!!

[shod90]

I just found one problem , when i put a password on the backend it give me an error

Dim fDialog As Office.FileDialogDim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
Set db = CurrentDb
strOld = Mid(db.TableDefs("yourtablename").Connect, 11)

If Dir(strOld) = "" Then     '''''''''''''''''''''''''''''''Error Goes on this line 

   ' set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' restrict user to single selection in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .title = "Please select one file"
      .InitialFileName = "C:\" 'use appropriate drive letter
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access file", "*.accdb"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked a file. If the .Show method returns False, user clicked Cancel
      If .Show = True Then
         strNew = .SelectedItems(1)
         'reset backend links path
         For Each td In db.TableDefs
            If InStr(td.Connect, strOld) > 0 Then
                Debug.Print td.Name
                Debug.Print "Old Link: " & td.Connect
                td.Connect = Replace(td.Connect, strOld, strNew)
                td.RefreshLink
                Debug.Print "New Link: " & td.Connect
            End If
         Next td
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With End If

[sdel_nevo]

Hi

Access Jitsu has a video on youtube and also the code to go with it here

http://accessjitsu.com/2016/01/10/mi...n-table-links/

it looks exactly what you are after

Steve