Select Backend Database from Frontend combobox

Question

HI 

I already have a table split into front and backends.

However, I have several backend DB's with exactly the same fields but are used for different scopes of work around the offices.

In a DB frontend, is it possible for me the Administrator to have a combobox on my main menu to select a specific backend and have it auto refresh the new DB records?

Here's hoping

John

Answer 1

Thanks Ken

This looks about righ t for my needs.

On e question:

How do I set up the combobox, so that it lists the paths to the various back end files

Regards

John

Answer 2

Any ideas how to do this using a combobox?

Assuming the back end is a native Access database file, the following is a stripped down version of some code from one of my own applications.  Name your combo box cboBackEnd and set it up so that it lists the paths to the various back end files, or these could be in a hidden bound first column with a plain English description of each in the second visible column.  Paste the code into the combo box's AfterUpdate event procedure;

    Dim dbs As DAO.Database

    Dim tdf As DAO.TableDef

    Dim strMessage As String, strBackEnd As String

    Set dbs = CurrentDb()

    If IsNull(Me.cboBackEnd) Then

        strMessage = "Path to source database must be " & _

            "selected before updating links."

        MsgBox strMessage, vbInformation, "Warning"

        Exit Sub

    Else

       strBackEnd = Me.cboBackEnd

    End If

    strMessage = "Are you sure you wish to link to " & _

        strBackEnd & " as the source database?"

    If MsgBox(strMessage, vbOKCancel + vbQuestion, "Leave Planner") = vbCancel Then

        Exit Sub

    End If

    If Dir(strBackEnd) = "" Then

        strMessage = "The database you have specified as the " & _

            "source was not found."

        MsgBox strMessage , vbInformation, "Warning"

        Exit Sub

    End If

    For Each tdf In dbs.TableDefs

        If tdf.Connect <> "" Then

            tdf.Connect = ";DATABASE=" & strBackEnd

            tdf.RefreshLink

        End If

    Next tdf

    strMessage = "Linking completed."

    MsgBox strMessage, vbInformation, "Confirmation"

Answer 3

A combobox only allows you to select the back end. You still need to have code to do the relinking. That is the code Daniel linked to. 

There is no way to do this with just a combobox alone. If you don't want to code the relinking, then add a button to the form that launches Linked Table Manager.

Answer 4

Thanks for the reply

It is a bit beyond my experience i'm afraid, only a novice.

Any ideas how to do this using a combobox?

Regards

John

Answer 5

You could modify the code found at http://access.mvps.org/access/tables/tbl0009.htm slightly to prompt you with a dialog to locate the be you want to use, or automate it to use a combo box as is your original intent.  Wouldn't be very hard.