Solved Linked Tables Closing Access 2013

[Cliff67]

Hi All
I've got a BE db for my front end, I have a splash screen that just shows the db name and welcome message, times out and opens a switchboard.

the problem I'm having is that even though I have specified the linked table location as \\server\foldername\subfoldername\BEname.accdb when run an runtime from a different machine (using the correct version of Runtime) that the tables are not found, and access does not open.

this is also true for the full version of Access I'm using to develop the front end

I have inserted a function to test for linked table as below

Private Function CheckLink(strTable As String) As Boolean

Dim varRet As Variant

On Error Resume Next

' Check for failure. If can't determine the name of
' the first field in the table, the links must be bad.
varRet = CurrentDb.TableDefs(strTable).Fields(0).Name
If Err <> 0 Then
CheckLink = False
Else
CheckLink = True
End If

End Function

after this function is run I get the db directory (based on the FE directory which is local)
Private Function GetDBDir() As String

On Error GoTo GetDBDirErr

Dim dbCurrent As Database
Dim strDbName As String


Set dbCurrent = CurrentDb
strDbName = dbCurrent.Name

Do While Right$(strDbName, 1) <> ""
strDbName = Left$(strDbName, Len(strDbName) - 1)
'MsgBox strDbName
Loop

GetDBDir = UCase$(strDbName)

On Error GoTo 0
Exit Function

GetDBDirErr:

strSEM = dfSEM(Err.Number, Err.Description, "GetDBDir", "GetDBDirErr")
MsgBox strSEM, vbInformation, "Technical Support and Repairs Database"
Err.Clear
'this is safe error handling
Exit Function

End Function

I then verify the link and call a common dialog box (contained within a module of common dialog features.

the db has been complied and compacted etc, however, it doesn't get that far and just closes Access both full version and runtime.

Any suggestions whats going on here

many thanks in advance

 

[arnelgp]

put some break point and step through the code. then you will be able to identify those variables and which line it breaks.

 

[Cliff67]

Hi

I've tried that Access just doesn't open to get that far. The system status says that it is compacting and then closes from what I can see

 

[Cliff67]

Hi All
after trying several methods to link tables I have used a macro that opens the Link Table Manager with a message box advising use. (Not perfect but manageable)

however I still have the same problem that the db will not open when the be has been moved/renamed. I get the open database "standard opening" page sorry don't know the name for it.

any help would be great..I'm going balder than I already am

 

[Cliff67]

It would appear that the Common dialog box I use is no longer supported and kills access when run - so this has been removed.

thanks to everyone who looked it this for me

 

[gemma-the-husky]

Maybe your new access is 64 bit, and dialog you are using is 32 bit. That might cause this sort of issue, I imagine.

 

[Cliff67]

Maybe your new access is 64 bit, and dialog you are using is 32 bit. That might cause this sort of issue, I imagine.

Hi Gemma

Its a bit strange as my PC is a 64 bit machine but Access is the 32 bit version (others within the company have 32bit machines) and I didn't have a problem on my old laptop, running Access 2007 but who knows with Microsoft

 

[gemma-the-husky]

How do you call your common dialog.

Is there a reference to an external library? It does sound like that is the culprit

 

[Cliff67]

How do you call your common dialog.

Is there a reference to an external library? It does sound like that is the culprit

Hi Gemma

Yes it was calling the cmdlg32.dll I think it was called Windows 10 doesn't support it

 

[Pat Hartman]

Here's two functions, one to choose a file and the other to choose a directory that are part of Office. You will need a reference to the MS Office object library for your version of Office.

Public Function fChooseFile()
 
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .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 our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function

Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

 

[Cliff67]

Here's two functions, one to choose a file and the other to choose a directory that are part of Office. You will need a reference to the MS Office object library for your version of Office.

Public Function fChooseFile()

   ' Requires reference to Microsoft Office 11.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant


   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
           
      ' Set the title of the dialog box.
      .Title = "Please select one file"

      'starting location
      .InitialFileName = CurrentProject.path
     
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .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 our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
       
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function

Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
   
    Dim fd As Object
   
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

Thanks Pat

that is great I will give it a try but it might not be for a while at the moment due to my work load. The Database is working after a fashion and the boss wants me to do my real job