Open backend on front end start-up

[Ruegen]

Is there a way to, when opening up the front end, ask for the backend file and then navigate to it to load it into the front end?

[Ruegen]

bump*

anyone?

[amrut]

you need to use VBA code. Here is the one http://access.mvps.org/access/tables/tbl0009.htm
Some tweaking is required for you to use in your case.

[NTC]

A front end linking to the back end ; which is the fundamental architecture & set up of the product - makes available all the tables of the back end. So your question is quite baffling.....

[rpeare]

Are you talking about re-establishing links to your back end, so that if you copy your front end/back end to a new location it will automatically re-link the data?

That's fairly easy I have an example that relies on the front end/back end being in the same folder if you want to see it, but like NTC, your post is pretty vague.

[rapsr59]

If you are tring to link a back end Db tables to the front end DB then this code will do the job.

Note: TargetFile contains Path to the back end db(s).

Note: By opening "My Recent Document" and right clicking the back end file, and selecting "Properties" the path to the back end file will be in the "Target" property.

Note: BackEndFilePath(I) is an array containing the pathes of all BackEnd Db. NrOfFiles contains the number of Back End Dbs.

Private Sub cmdLinkTables_Click()

Call LinkBackEndTables

End Sub

Private Sub LinkBackEndTables()

'This procedure creates linked tables in this database to all the
' tables in the Back End databases
Dim I As Long
Dim BackEndDatabase As String
Dim db As DAO.Database

Set db = CurrentDb 'This Database

'MsgBox BackUpFilesCount
For I = 1 To NrOfFiles
'Compute the name of the BackEnd Database
Call LinkTables(BackEndFilePath(I))
Next I

'Show the Tables.
db.TableDefs.Refresh

End Sub
Private Sub LinkTables(TargetFile As String)

'This procedure creates linked Tables to the Backend Databases in this
' Database
Dim dbs As DAO.Database
Dim db As DAO.Database
Dim tblDef As TableDef
Dim tblDefs As TableDefs
Dim tblLinked As TableDef
Dim I As Integer
Dim IntResponse As Integer
Dim strSQL As String

On Error GoTo ErrHandler

'Set db equal to this database.
Set db = CurrentDb

'Open the Backend Database
Set dbs = DBEngine.Workspaces(0).OpenDatabase(TargetFile)

'Set tblDefs = the Database's tables.
Set tblDefs = dbs.TableDefs

'Enumerate the tables and create A Linked Table.
For I = 0 To tblDefs.Count - 1
Set tblDef = tblDefs(I)
If Not Left(tblDef.Name, 4) = "MSys" Then 'Do not include MSys tables
'Add the linked table from the Backend database to this database.
GoSub LinkTable
'Add the linked table to the BackUp Database
End If
Next I

Exit Sub
ErrHandler:
Err.Clear
Resume Next
Exit Sub
LinkTable:
'Link-----------------------------------------------------------------
' Create a new Table named the same as the table from the linked Database
' and set its Connect and SourceTableName properties and append it
' to the TableDefs collection in this database.

' Create a new Linked Table in this database using the same table name as in
' the linked Database
Set tblLinked = db.CreateTableDef(tblDef.Name)
'Set its Connect property to the target file
tblLinked.Connect = ";DATABASE=" & TargetFile
'Set this db Table Name to the Source Table Name
tblLinked.SourceTableName = tblDef.Name
'Append it to this db TableDef collection
db.TableDefs.Append tblLinked

Return

End Sub

Good Luck - Richard

[rapsr59]

Since New Tables May be added in the back end DB, then I would delete the front end Linked Tables I would delete the present linked tables first, This I would link the back tables.

The code below delete the links to the back end db(s).


Private Sub DeleteLocalTables()

'This procdure deletes the tables located this Database
Dim dbs As DAO.Database

Dim tblDef As TableDef
Dim tblDefs As TableDefs
Dim I As Integer

On Error GoTo ErrHandler

'Open this Database
Set dbs = CurrentDb

'Set tblDefs = this Database's tables.
Set tblDefs = dbs.TableDefs

'While running the below code, all tables are not deleted on the first pass
' so the code is RunAgain until only the 5 MSys tables remain.
RunAgain:

'Enumerate the tables and delete the tables.
For I = 0 To tblDefs.Count - 1
Set tblDef = tblDefs(I)
If Not Left(tblDef.Name, 4) = "MSys" Then
dbs.TableDefs.Delete tblDef.Name 'Delete the table.
End If
Next I

If tblDefs.Count > 5 Then GoTo RunAgain 'Rerun code if tables remain.

Set dbs = Nothing
Set tblDef = Nothing
Set tblDefs = Nothing

Exit Sub
ErrHandler:
Err.Clear
Resume Next

End Sub

[Ruegen]

Sometimes folks at work don't turn the server on until after they have turned their pc's on which means because they haven't networked yet windows doesn't refresh the mapped drives in order to load the back end into the front end. It comes up with the usual error of "can't find backend location" in which case I would like to have a find dialog open and them to navigate to the backend etc.

Plus sometimes the boss likes to take the backend and front end home and run the server at home on the one computer - meaning I have to relink the tables every time to suit which isn't practical -I'd prefer he just linked it himself. I will have a look through your suggested code. Thanks guys

Will keep you updated. Please if you have any more suggestions, post below

[Ruegen]

I'm at a point where I think I can resolve my problem however I don't know how to make an event occur if it is an application error

since Access gives me the error "3304" I thought of an access handler - but I am not sure where to put it so that when the error occurs I can run some code.

[Ruegen]

oookay... so anyway... this is what I have made

It loads the function on the first form load and runs a check - if there are missing linked tables then you can do a search using an open dialog. Let me know what you think.

Public Function Relink_tables()
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim strNew_file_path As String
Dim varPath As Variant


Dim notConnect As Boolean
notConnect = False


Set db = CurrentDb
For Each tdf In db.TableDefs
'check to see that it is a linked tables
If Len(tdf.Connect) > 0 Then
notConnect = True


End If
Next
If notConnect = True Then
MsgBox "Missing backend server file - Locate database backend file"


'Declare a variable as a FileDialog object.
Dim fd As FileDialog


'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)


'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


'Change the contents of the Files of Type list.
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear


'Add a filter that includes access files and make it the first item in the list.
.Filters.Add "Access Dababase File", "*.accdb", 1


'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.
MsgBox "Path name: " & .SelectedItems(1)
strNew_file_path = .SelectedItems(1)
Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With


'Set the object variable to Nothing.
Set fd = Nothing

Debug.Print strNew_file_path


For Each tdf In db.TableDefs
'check to see that it is a linked tables
If Len(tdf.Connect) > 0 Then
'table is linked, replace the path with the new one

'Debug.Print tdf.Connect


tdf.Connect = ";DATABASE=" & strNew_file_path
tdf.RefreshLink

Debug.Print tdf.Connect

End If
Next



End If
db.Close
Set db = Nothing
Set tdf = Nothing
End Function

[Ruegen]

OK finished it with

Function DatabaseCheck()


On Error GoTo Err_DatabaseCheck
  'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim tdf As DAO.TableDef
    Dim db As DAO.Database
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Set db = CurrentDb
    '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
    Dim Missing_db As Boolean
  
    
    'First do a check
    For Each tdf In db.TableDefs
    
    'check to see that it is a linked tables
    If Left$(tdf.Connect, 10) = ";DATABASE=" Then
'    Debug.Print tdf.Name; " is a table"
    If Len(tdf.Connect) > 0 Then
    Debug.Print tdf.Connect
    Dim linkedtable As String
    linkedtable = tdf.Name
    Debug.Print linkedtable
    db.TableDefs(linkedtable).RefreshLink
'    Debug.Print "missing " & tdf.Name
        'table is linked, replace the path with the new one
       
    Else
'    Debug.Print "not missing" & tdf.Name
    End If
    End If
    Next tdf




Err_DatabaseCheck:
Select Case Err.Number
Case 3044, 3011, 3024




    MsgBox "Missing database backend, please locate it", vbCritical, "Tables Missing"


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


        'Change the contents of the Files of Type list.
        'Empty the list by clearing the FileDialogFilters collection.
        .Filters.Clear


        'Add a filter that includes access files and make it the first item in the list.
        .Filters.Add "Access Dababase File", "*.accdb", 1


        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If fd.Show = -1 Then
                vrtSelectedItem = fd.SelectedItems(1)
                
               'loop through any unlinked tables and link them with new location
                For Each tdf In db.TableDefs
                If Len(tdf.Connect) > 0 Then
'                Debug.Print tdf.Name
'                Debug.Print ";DATABASE=" & vrtSelectedItem
                 tdf.Connect = ";DATABASE=" & vrtSelectedItem
                 tdf.RefreshLink
                 End If
                Next
                
        'The user pressed Cancel.
        Else
        MsgBox "You have closed the search for database dialogue box, the ""frontend"" requires a backend and without one found - is not usable. This program will now close."
        DoCmd.CloseDatabase
        End If
    End With




End Select
    'Set the object variable to Nothing.
    Set fd = Nothing
    Set db = Nothing
    Set tdf = Nothing




End Function

if anyone cares to look it at I'm open to simpler and better solutions.