Question Loading back end database

[Eddie Mason]

I’m using Access 2003, each year I archive the last years back end database and then start a new back end database. From time to time I want to review an archived database and what I do is re-attach the tables, which is time consuming. Is there a way that I can select the back end database I want to work on when starting up the front end database?

Hope someone can help with this.

Kind regards

Eddie

 

[vbaInet]

Here is a FE/BE tool from one of the forum's Mods:

http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

 

[DCrake]

Lets work with an example.

First create a public string variable in a standard module

Public MyDataPath As String

On your login screen you have a textbox/combo that allows you to enter the name of the back end database you want to link to.

You enter/select Data2009.mdb
You know that the location/path to this mdb is Z:\Databases\Archives\

So you need to concat them together and use them later.

So: MyDataPath = "Z:\Databases\Archives\" & Me.TxtBackEnd

Now you need to first check if the file actually exists in the first place. No use trying to link to a non existant file


'Place this on the Login Command button On Click Event or on the After Update Event of the textbox/comboBox

'Does file Exist

If Dir(MyDataPath) <> "" Then
   Call [B]RefreshLinks[/B] ' See below
Else
   MsgBox "The file you have chosen does not exist",vbExclamation+vbOkOnly, "Invalid File Name"
   Exit Sub
End If

'This function can be saved in the same standard module as the public variable.

Function RefreshLinks() As Boolean
' Refresh links to the supplied database. Return True if successful.

DoCmd.Echo True, "Refreshng table links, please wait..."
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            'Only need to reconnect if it is different to to MyDataPath
            If tdf.Connect <> ";DATABASE=" & [B]MyDataPath [/B]Then
                tdf.Connect = ";DATABASE=" & [B]MyDataPath[/B]
                Err = 0
                On Error Resume Next
                
                tdf.RefreshLink         ' Relink the table.
                If Err <> 0 Then
                    RefreshLinks = False
                    Exit Function
                End If
            End If
            
        End If
    Next tdf
DoCmd.Echo True, "Done"

    RefreshLinks = True        ' Relinking complete.
    
End Function

Steps
1:Accepts the name of the mdb
2:Validates it as being a valid file name
3:Checks to see the current link is the same
4:Refresh the link if needed

Hope that helps

 

[Eddie Mason]

Hi David,

Many thanks for your help; the solution you gave works brilliantly.

Kind regards

Eddie