A user-friendly way to Link Tables in a Split Database

[vbaDev]

Hi, all.

I have a "client/server" split Access 2003 database with an Interface file containing all the code/forms/queries and some system tables and also a Data Backend file, with all the data tables and table structure.

This is being deployed to multiple sites, each getting their own version of the backend, so sites will not be using the same backend between them all, rather multiple users in each site will all share that site's backend file.

Since users will deploy in different network locations, etc. they will have to re-link all tables. Is there a meaningful/user-friendly way of doing that without relying on user's knowledge of "Linked Table manager", etc.?

Thanks!

 

[Moniker]

Here's how you'll do it:

DoCmd.TransferDatabase acLink, "Microsoft Access", strLinkTo, acTable, strSrceTableName, strDestTableName, False

strLinkTo - The full path and filename of the DB that contains the tables you are linking to.
strSrceTableName - The name of the table you are linking to in the BE
strDestTableName - The name of the table as it will exist in the FE

What you'll have to do here, obviously, is programmatically get to each location's path to the BE, and then you'll want a list of sorts that contains the names of the tables to link (both the BE and FE names). A few suggestions:

1) Store the BE's location for each office location in each individual FE. Make a table (if one that doesn't already exist) and call it "t_System_Settings" or whatever makes sense to you. Then, you can just read that table in code to get the BE's location. You could also let an admin or other programmer at another location set the value in this table for you if you don't know the path of where it's being stored.

2) Store the source and destination table names in one table local to the FE. Then you can just loop through the table and relink everything.

3) If you want to get extra fancy, you can loop through the tables in the FE and figure out which ones are local and which ones are linked dynamically. This way, you don't have to maintain a separate FE/BE tablename table. However, it requires a little more elbow grease.

A quick tip on that method: If this returns something greater than zero, then it's a linked table:

If Len(CurrentDb.TableDefs("t_Export_BatchProcess").Connect) > 0 Then
--- This is a linked table, so relink code here ---
Else
--- It's a local table ---
End If

A linked table will look something like this:
;DATABASE=YourPathHere\SomeSubfolderHere\Name_Of_BE_DB.mdb

You'll obviously want to snip off the first ten characters:

strPath = Replace(strPath,";DATABASE=","")

You should be able to figure out the rest from here.

 

[vbaDev]

Awesome answer, Moniker! One of the best replies I ever got, thanks!

Yes, I can use the Common Dialog to get users to "browse" to the BACKEND location, capture the path and use it.

Also great ideas about the list of linked table names.

Awesome!

 

[vbaDev]

Hey, Moniker, thanks again! a quick question:

where is it best to place the code to prevent the multiple error messages (due to missing links to BE)?

I find that if I place it in the frmMain's "On Load" event, I get MANY of the same error messages BEFORE that event even fires.

I need the system to check/ensure whether or not the BE is missing (in the expected location) and THEN to offer user to browse and link.

thanks!

 

[vbaDev]

bumped (just in case).

any ideas? where do you place the code which will run FIRST, before anything else happens?

 

[DJNickWatt]

Create a module and create a procedure in there that contains the code you want to run upon starting up. Example

Public Function Startup()
' Your code Here
End Function

Then create a macro and call it AutoExec. Select "Run Code" from the actions and set it to run the function you just created. Then from actions select "Open Form" and set it to open your main form.

Change the options for database startup so that it doesn't load your main form as you are doing so in the macro.

 

[vbaDev]

Thanks! That should work, I dunno why I didn't think of that.

Any idea how to "check" whether or not the BE is missing (before the error messages start)? My frmMain (main form) is based on tables in BE, so if I open frmMain and BE is missing (not linked), it's error messages galore!

Thanks!

 

[DJNickWatt]

Use the DIR function to see if the backend database file exists

DIR("F:\Users\Nick\Documents\DB.mdb")

It will return the filename if the file exists or an empty string if it doesn't

IF DIR("F:\Users\Nick\Documents\DB.mdb") = "" Then ERROR!!!

Refreshing links on tables can be slow if other people are connected to the backend so don't try and do this everytime your database starts. In fact once you have linked the tables, unless you are moving the backend or want to connect to a different back end you never need to do it again.

Here's what I would do. Create a local table. Call it whatever you want. For the sake of argument "Settings". Add a field there of type "Yes/No" and call it "RefreshLinks". Now when you run the Startup function as I mentioned above you would check this local table/field to see if it is assigned "Yes", if it is then do the refresh linked tables part. If it is "No" then don't do anything and continue your startup procedure.

If you refreshed the links on the tables and they have done so without error then set the field "RefreshLinks" to "No" so that the next time the database is loaded it doesn't try and refresh the links again.

 

[vbaDev]

Ok, I tried the TransferDatabase method and what it did was create additional linked tables in the FE (like if there was a LinkedTable, it also added LinkedTable1) instead of re-linking or re-freshing the existing linked tables.

I'm pretty sure that I had it coded right, because I ran the iterations with "debug.print" before doing the actual transferdb method and all the oject names were correct.

Isn't there a way to re-assign the CurrentDB.TableDefs("TableName").connect property?

I tried this:

CurrentDb.TableDefs("tblAssessments").Connect = ";DATABASE=C:\Documents and Settings\C9975004\Desktop\TEST\MESH_DATA.mdb"

and it doesn't seem to work.

any other ideas?

thanks!

 

[vbaDev]

Ok, I found something online and was able to make it work (with the code below). The trick is that when you are relinking to a different location of BE (and not just refreshing) - you are deleting and re-creating a TableDef. I got it to work, yes, but the problem is that the Relationships are not preserved.

I wonder how I can preserve Relationships? That's why Linked Table Manager was so nice - you just select, browse, click and everything stays the same. I wish I could do that through code! I really don't see why not?

Any ideas?

Thanks!

Public Function Startup()
On Error GoTo Err_Handler
Dim dbs As Database
Dim tdf As TableDef             'used to iterate through tables
Dim tdfLinked As TableDef       'used to re-link tables
Dim strTableName As String      'stores linked table name to be re-linked
Dim strSrceTableName As String  'holds name of Source Table being linked (in the Back End)
Dim strDestTableName As String  'holds name of Destination Table being linked (in the Front End)


Dim strBEpath As String         'holds path to Back End file
Dim dlgOpen As FileDialog       'declares the Browse Dialog object
Dim varSelectedItem As Variant  'variable needed to iterate through Commond Dialog selection(s)

    'check linked tables (based on 1 sample table):
    
    strBEpath = CurrentDb.TableDefs("tblAssessments").Connect
    Debug.Print strBEpath
    
    strBEpath = Right(strBEpath, Len(strBEpath) - 10)   'trims off ";DATABASE="
        
    If Dir(strBEpath) = "" Then 'Back End is missing or not accessible, need to re-link
        'RE-LINK CODE HERE
        'Have user browse to Back End location and capture the path/filename:
        Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
        
        With dlgOpen
            .Title = "Link Back-End Database"
            .AllowMultiSelect = False
            
            If .Show = -1 Then
                For Each varSelectedItem In .SelectedItems
                    strBEpath = CStr(varSelectedItem)
                
                Next varSelectedItem
        
            Else    'user cancelled
                'do nothing
                
            End If
        
        End With

    'ITERATE THROUGH ALL TABLES, DETERMINE LINKED TABLES, RE-LINK THEM:
        Set dbs = CurrentDb()
        With dbs
            For Each tdf In .TableDefs
                If Len(tdf.Connect) > 0 Then  'Linked table, must re-link:
                    
                    'NEW CODE
                    strTableName = tdf.Name
                    
                    Set tdfLinked = New TableDef
    
                    With tdfLinked
                        .Name = strTableName & "_Temp"
                        .SourceTableName = strTableName
                        .Connect = ";DATABASE=" & strBEpath
                        
                    End With
                                
                    CurrentDb.TableDefs.Append tdfLinked
                    'If the new tabledef was successfully created, delete the old TableDef
                    CurrentDb.TableDefs.Delete strTableName
                                
                    'Change the name of the new tabledef to that of the old TableDef
                    CurrentDb.TableDefs(strTableName & "_Temp").Name = strTableName

                    'END NEW CODE
                    
                    'DoCmd.TransferDatabase acLink, "Microsoft Access", strBEpath, acTable, tdf.Name, tdf.Name, False
                    'Debug.Print strBEpath
                    'Debug.Print "Relinking " & tdf.Name & "..."
            
                Else
                    'Table is local, NO RELINKING needed
                    'Debug.Print tdf.Name & " is LOCAL"
                    
                End If
            
            Next tdf
            
        End With
        
    End If

Exit_Function:
    Exit Function

Err_Handler:
    MsgBox Err.Description, vbInformation, Err.Number
    Resume Exit_Function
    
End Function