Results 1 to 5 of 5
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Microsoft access not a valid path error

    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 ?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    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:
    Code:
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Super !! , It works with me after adding microsoft office 16.0 object library ..Really thanks !!!

  4. #4
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    I just found one problem , when i put a password on the backend it give me an error
    Code:
    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

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-23-2018, 12:16 PM
  2. Replies: 4
    Last Post: 01-24-2017, 09:32 AM
  3. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  4. Replies: 4
    Last Post: 08-19-2014, 12:20 PM
  5. Replies: 2
    Last Post: 12-08-2011, 02:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums