Set front end to choose which back end to connect too (1 Viewer)

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
Hi All

I have a DB running in 6 of my sites.

They are all set up identically.

I have one front end that is linked to the back end.

Each user has the front end and it is set to connect to the correct file location for their site.

I also have some multi site user that have to unlink and relink the db front end when they change site.

What i would like to do is set up a button that allows them to switch site.

fore example.

If they are a Reading user the front end connects to \\networkname\reading\backenddb

If they are a Slough user the front end connects to \\networkname\slough\backenddb

If they are a multi site user they have to go into the linked table manager and relink to the site they want to connect too.

I have to do it this way due to GDPR and some rules we have in place so i cannot set up one master file and have queries pick the correct site.

I also only want one front end file so that any updates or feature adds can be rolled out in one format rather than updating 6 different front end files.

Is there a way of setting this via button so the front end can switch between different sites?

I hope this makes sense.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
8,463
I have code that I use on all my dbs when the db opens it cannot connect to the backend it prompts the user for a new backend and brings up a file browser. So they just browse to the new backend. So this may work if the reading people cannot hit the slough db and vice versa.
I do not have code at this time where you would have the users backend path in their login table and then pick the correct back end. However this could be done. I do this with a hidden form at startup (could be done with autoexec macro). It checks all the links and if it cannot find them it prompts you. It also works with multiple backends and will relink each separate backend. You want that?

I would never make the user go to the link table manager though. I would always give them simply a file browser.
 

Minty

AWF VIP
Local time
Today, 18:03
Joined
Jul 26, 2013
Messages
10,355
If the button can connect to any site, then I don't see the difference between restricting access that way or simply querying the correct data?
 

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
If the button can connect to any site, then I don't see the difference between restricting access that way or simply querying the correct data?
I would have the button locked with a password that only certain people can access.
 

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
I have code that I use on all my dbs when the db opens it cannot connect to the backend it prompts the user for a new backend and brings up a file browser. So they just browse to the new backend. So this may work if the reading people cannot hit the slough db and vice versa.
I do not have code at this time where you would have the users backend path in their login table and then pick the correct back end. However this could be done. I do this with a hidden form at startup (could be done with autoexec macro). It checks all the links and if it cannot find them it prompts you. It also works with multiple backends and will relink each separate backend. You want that?

I would never make the user go to the link table manager though. I would always give them simply a file browser.
I kind of want it to be a bit simpler than that. Just a button that runs a macro.

If you wouldnt mind sharing your code i might be able to make it work with the code builder.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:03
Joined
Oct 29, 2018
Messages
21,358
I would have the button locked with a password that only certain people can access.
I think the question was what is the difference between using a button and using a query? Does GDPR require the data to be physically stored in separate locations? Or, is the requirement simply to not allow access to other data?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
8,463
Just a button that runs a macro
You want a macro that can determine what backend a user is authorized and relink to the proper backend?:ROFLMAO::ROFLMAO:
 

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
I think the question was what is the difference between using a button and using a query? Does GDPR require the data to be physically stored in separate locations? Or, is the requirement simply to not allow access to other data?
Hi

The requirements we have are to store the data indipendantly and control access.

Being able to do this solves the issue and still sticks with the guidelines
 

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
You want a macro that can determine what backend a user is authorized and relink to the proper backend?:ROFLMAO::ROFLMAO:
again simpler lol

Just need a button that relinks to a specific location and i will create 6 of them to change as required.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
8,463
Here is what I use, I think this may have a few more features in that it tests and prompts if a link is broken. It supports multiple backends. The method you want would be the relinkTables and path it the correct path.

Code:
Option Compare Database
Option Explicit

Dim UnProcessed As New Collection
Public strPath As String

Public Sub linkToBackend()
  MsgBox "Pick the location of your backend database.", vbInformation, "Find Database"
  strPath = GetFileDialog()
  ReProcessTables
End Sub

Public Sub AppendTables()
  On Error GoTo errlbl:
    Dim db As DAO.Database, X As Variant
    Dim strTest As String
    ' Add names of all table with invalid links to the Unprocessed Collection.
    Set db = CurrentDb
    ClearAll
    For Each X In db.TableDefs
        If Len(X.Connect) > 1 And Len(Dir(Mid(X.Connect, 11))) = 0 Then
        ' connect string exists, but file does not
             UnProcessed.Add Item:=X.Name, Key:=X.Name
        End If
    Next
    Exit Sub
errlbl:
   If Err.Number = 52 Then
   MsgBox "Network not present."
   For Each X In db.TableDefs
        If Len(X.Connect) > 1 Then
        'MsgBox x.Name & " " & x.Connect
        ' connect string exists, but file does not
         UnProcessed.Add Item:=X.Name, Key:=X.Name
        End If
    Next
   Else
     Call ErrHandler(Err.Number, Err.Description, "Error in Appendtables")
   End If
End Sub
Public Function ProcessTables()
    Dim strTest As String
    On Error GoTo Err_BeginLink
    
    ' Call procedure to add all tables with broken links into a collection.
    AppendTables
    ' Test for existence of file name\directory selected in Common Dialog Control.
    strTest = strPath
    
    On Error GoTo Err_BeginLink
    If Len(strTest) = 0 Then   ' File not found.
        MsgBox "File not found. Please try again.", vbExclamation, "Link to new data file"
        Exit Function
    End If
    
    ' Begin relinking tables.
    Relinktables (strTest)
    ' Check to see if all tables have been relinked.
    CheckifComplete
    DoCmd.Echo True, "Done"
    If UnProcessed.Count < 1 Then
         MsgBox "Linking to new back-end data file was successful."
    Else
         MsgBox "Not All back-end tables were successfully relinked."
    End If
Exit_BeginLink:
    DoCmd.Echo True
    Exit Function
Err_BeginLink:
    Debug.Print Err.Number
    If Err.Number = 457 Then
        ClearAll
        Resume Next
    ElseIf Err.Number = 3043 Then
      MsgBox "Can not find the Master on the Network.  Check that you have a good network connection."
      Resume Exit_BeginLink
    Else
      Call ErrHandler(Err.Number, Err.Description, "Error in Processtables")
      Resume Exit_BeginLink
    End If
End Function

Public Sub ClearAll()
    Dim X
    ' Clear any and all names from the Unprocessed Collection.
    For Each X In UnProcessed
       UnProcessed.Remove (X)
    Next
End Sub

Public Function Relinktables(strFileName As String)

    Dim dbbackend As DAO.Database, dblocal As DAO.Database, ws As Workspace, X, Y
    Dim tdlocal As DAO.TableDef
    On Error GoTo Err_Relink
    Set dbbackend = DBEngine(0).OpenDatabase(strFileName)
    Set dblocal = CurrentDb
    
    ' If the local linked table name is found in the back-end database
    ' we're looking at, Recreate & Refresh its connect string, and then
    ' remove its name from the Unprocessed collection.
     For Each X In UnProcessed
        If Len(dblocal.TableDefs(X).Connect) > 0 Then
            For Each Y In dbbackend.TableDefs
                If Y.Name = X Then
                    Set tdlocal = dblocal.TableDefs(X)
                    tdlocal.Connect = ";DATABASE=" & strPath
                    tdlocal.RefreshLink
                    UnProcessed.Remove (X)
                End If
            Next
        End If
    Next

Exit_Relink:
    Exit Function

Err_Relink:
    If Err.Number = 3043 Then
      MsgBox "Can not find the Master on the Network.  Check that you have a good network connection."
      Resume Exit_Relink
    Else
     Call ErrHandler(Err.Number, Err.Description, "Error in Relinktables")
     Resume Exit_Relink
    End If
End Function

Public Sub CheckifComplete()

    Dim strTest As String, Y As String, notfound As String, X
    On Error GoTo Err_BeginLink
    
    ' If there are any names left in the unprocessed collection,
    ' then continue.
    If UnProcessed.Count > 0 Then
        For Each X In UnProcessed
            notfound = notfound & X & Chr(13)
        Next
        ' List the tables that have not yet been relinked.
        Y = MsgBox("The following tables were not found in " & _
        Chr(13) & Chr(13) & strPath _
        & ":" & Chr(13) & Chr(13) & notfound & Chr(13) & _
        "Select another database that contains the additional tables?", _
        vbQuestion + vbYesNo, "Tables not found")
        
        If Y = vbNo Then
            Exit Sub
        End If
        
        ' Bring the Common Dialog Control back up.
        strPath = GetFileDialog
        strTest = strPath
        If Len(strTest) = 0 Then   ' File not found.
            MsgBox "File not found. Please try again.", vbExclamation, _
            "Link to new data file"
            Exit Sub
       End If
       Debug.Print "Break"
       Relinktables (strTest)
    Else
       Exit Sub
    End If
    
    CheckifComplete
    
Exit_BeginLink:
    DoCmd.Echo True   ' Just in case of error jump.
    DoCmd.Hourglass False
    Exit Sub

Err_BeginLink:
    Debug.Print Err.Number
    If Err.Number = 457 Then
        ClearAll
        Resume Next
    End If
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_BeginLink

End Sub


Public Sub AppendAllTables()
    Dim db As DAO.Database, X As Variant
    Dim strTest As String
    ' Add names of all table with invalid links to the Unprocessed Collection.
    Set db = CurrentDb
    If Not UnProcessed Is Nothing Then
      ClearAll
    End If
    For Each X In db.TableDefs
        If Len(X.Connect) > 1 Then
        ' connect string exists, but file does not
             UnProcessed.Add Item:=X.Name, Key:=X.Name
        End If
    Next

End Sub
Public Function ReProcessTables()

    Dim strTest As String
    On Error GoTo Err_BeginLink
    
    ' Call procedure to add all tables with broken links into a collection.
    AppendAllTables
    
    ' Test for existence of file name\directory selected in Common Dialog Control.
    strTest = strPath
    
    On Error GoTo Err_BeginLink
    If Len(strTest) = 0 Then   ' File not found.
        MsgBox "File not found. Please try again.", vbExclamation, "Link to new data file"
        Exit Function
    End If
    
    ' Begin relinking tables.
    Relinktables (strTest)
    ' Check to see if all tables have been relinked.
    CheckifComplete
    
    DoCmd.Echo True, "Done"
    If UnProcessed.Count < 1 Then
         MsgBox "Linking to new back-end data file was successful."
    Else
         MsgBox "Not All back-end tables were successfully relinked."
    End If
Exit_BeginLink:
    DoCmd.Echo True
    Exit Function
Err_BeginLink:
    Debug.Print Err.Number
    If Err.Number = 457 Then
        ClearAll
        Resume Next
    End If
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_BeginLink
End Function

'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'************************************************************************  File Dialog  *********************************************************************************
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Public Function GetFileDialog() As String
 
   ' 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 Backend Database"
 
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.ACCDB"
    
      ' 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.
        GetFileDialog = .SelectedItems(1)
        ' For Each varFile In .SelectedItems
        '    GetFileDialog = varFile
        ' Next
 
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Function

Public Function GetFolderDialog() As String
    Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    GetFolderDialog = sFolder
End Function
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Sep 12, 2006
Messages
15,614
I have an external csv that lists all databases consistent with the front end, and contains the backend database path for each.
This is loaded at startup. If a user select a different database to the one that's currently connected, the data tables are reconnected according to the profile for that database. I also have options to reconnect all tables in case the backend structure gets modified.

Note that the best way is to connect to one table, create a permanent connection to that table, and then open the others, otherwise you get time delays obtaining permission for all the subsequent tables.

I imagine the code is similar to what @MajP just listed.

[edited slightly 14/1/21]
 
Last edited:

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
I have an external csv that lists all databases consistent with the front end, and contains the backend database path.
This is loaded at startup. If a user select a different database, the data tables are reconnected according to the profile for that database.

Note that the best way is to connect to one table, create a permanent connection to that table, and then open the others, otherwise you get time delays obtaining permission for all the subsequent tables.

I imagine the code is similar to what @MajP just listed.
OK thanks.

I am planning a complete rebuild later in the year to bring in user accounts , passwords etc so will think of something more perminant then.

I was just hoping for some code i could attach to a button that changes the unc path of the linked table.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
8,463
I was just hoping for some code i could attach to a button that changes the unc path of the linked table.
Out of curiosity, when you called the below function of the module and passed in your paths, which part failed?:unsure:

Code:
Public Function Relinktables(strFileName As String)
    Dim dbbackend As DAO.Database, dblocal As DAO.Database, ws As Workspace, X, Y
    Dim tdlocal As DAO.TableDef
    On Error GoTo Err_Relink
    Set dbbackend = DBEngine(0).OpenDatabase(strFileName)
    Set dblocal = CurrentDb
  
    ' If the local linked table name is found in the back-end database
    ' we're looking at, Recreate & Refresh its connect string, and then
    ' remove its name from the Unprocessed collection.
     For Each X In UnProcessed
        If Len(dblocal.TableDefs(X).Connect) > 0 Then
            For Each Y In dbbackend.TableDefs
                If Y.Name = X Then
                    Set tdlocal = dblocal.TableDefs(X)
                    tdlocal.Connect = ";DATABASE=" & strPath
                    tdlocal.RefreshLink
                    UnProcessed.Remove (X)
                End If
            Next
        End If
    Next

Exit_Relink:
    Exit Function

Err_Relink:
    If Err.Number = 3043 Then
      MsgBox "Can not find the Master on the Network.  Check that you have a good network connection."
      Resume Exit_Relink
    Else
     Call ErrHandler(Err.Number, Err.Description, "Error in Relinktables")
     Resume Exit_Relink
    End If
End Function
 

markdooler

Member
Local time
Today, 18:03
Joined
Nov 25, 2020
Messages
58
Out of curiosity, when you called the below function of the module and passed in your paths, which part failed?:unsure:

Code:
Public Function Relinktables(strFileName As String)
    Dim dbbackend As DAO.Database, dblocal As DAO.Database, ws As Workspace, X, Y
    Dim tdlocal As DAO.TableDef
    On Error GoTo Err_Relink
    Set dbbackend = DBEngine(0).OpenDatabase(strFileName)
    Set dblocal = CurrentDb
 
    ' If the local linked table name is found in the back-end database
    ' we're looking at, Recreate & Refresh its connect string, and then
    ' remove its name from the Unprocessed collection.
     For Each X In UnProcessed
        If Len(dblocal.TableDefs(X).Connect) > 0 Then
            For Each Y In dbbackend.TableDefs
                If Y.Name = X Then
                    Set tdlocal = dblocal.TableDefs(X)
                    tdlocal.Connect = ";DATABASE=" & strPath
                    tdlocal.RefreshLink
                    UnProcessed.Remove (X)
                End If
            Next
        End If
    Next

Exit_Relink:
    Exit Function

Err_Relink:
    If Err.Number = 3043 Then
      MsgBox "Can not find the Master on the Network.  Check that you have a good network connection."
      Resume Exit_Relink
    Else
     Call ErrHandler(Err.Number, Err.Description, "Error in Relinktables")
     Resume Exit_Relink
    End If
End Function
Hi

It came up with an error on this line on the vba editor

tdlocal.RefreshLink

Im sure its something i have done so will review again when i have time next week.
 

Users who are viewing this thread

Top Bottom