Split Database Pathing

[mitchell1613]

I am using Access 2010 in a split database. The intent is to place the back end and associated linked files on a server and the front end on work stations for each user. The typical user will just be searching for information in the database not altering it. My current code re-links the tables once the path is known i.e. I have to hard code it into the global constant. Is there a way in VBA or utilizing VBA to read/write to an ini file (for example) that the UNC path would automatically be updated to a global constant in my current code that can be updated automatically based on where the back end is installed.:banghead:

Global Const BE_DATABASE = "\\192.168.2.3\FMC\ENFMCTABLES.accdb"
Global Const BE_PASSWORD = "***********"
Global Const BE_DATABASEPATH = "\\192.168.2.3\FMC\" 'used to open files


Public Sub CreateDatabaseLinks()

On Error Resume Next 'new
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim td2 As DAO.TableDef 'new
Dim Attrib As Long
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(BE_DATABASE, False, False, _
​

"MS Access;PWD=" & BE_PASSWORD)
​

For Each td In db.TableDefs

Attrib = (td.Attributes And dbSystemObject)
If Attrib = 0 Then ' not a system table
​

Set td2 = CurrentDb.CreateTableDef(td.Name) 'new block
td2.SourceTableName = td.Name
td2.Connect = ";DATABASE=" & BE_DATABASE
CurrentDb.TableDefs.Append td2
​

End If
​

Next

db.Close
ws.Close
Set db = Nothing
Set ws = Nothing
Set td = Nothing
​

End Sub

 

[MarkK]

What I do most commonly in the FE is link to a mapped drive, say Z:\. Then, as the network evolves, and servers are renamed, and so on, all you do is map the drive in Windows to the location of the new BE. Dead simple. Server naming issue is solved. AND, if during development you want to try sensitive data operations on a false data set, just re-map your drive in Windows to a different folder, and test away without fear of killing your real data.

Hope this helps,

 

[The_Doc_Man]

I'm a bit confused. If you are using UNC pathing, you shouldn't need to dynamically relink the tables. Why would you EVER need to know the location of the back end for linking purposes? Does the BE file move from place to place?

At worst, you might have an issue in connecting to the tables because Access might ask you where the tables are located. If you build a query and run everything through the query, Access doesn't ask. (Don't ask me WHY it doesn't ask - but it doesn't.) For instance, I have a table called tUSR - my list of approved users and their roles - but I don't ever open the table. Instead, I have a query called qtUSR that has exactly the same fields. When I try to open the (linked) table, it asks me where it is located. If I open the query, it just opens it. Access forms, reports, JOIN/UNION/IN queries, and VBA-triggered recordsets work just fine with these single-table queries.

Now, as to how to decide where the backend is installed... this confuses me a bunch. If you can't statically link the UNC path then putting your address or UNC string into a static INI file won't help either. Static is static and won't change a bit regardless of how you store the string. If you statically link the tables then they connect when you open the FE file. Dynamically linking the BE file just delays the OpenDatabase operation. If you are giving everyone a different BE file, why would you not simply put the BE on their workstation as a separate file. Linked Table manager will do that for you. And if everyone uses the same BE file, why do you not statically link to it?

If you are using the drive letter mapping, you would have to translate the letter to the UNC string. But if you are using the UNC string, there IS no drive letter. You can find the string simply by finding the part of the string that contains "\\" and then find one more "\" - and the UNC address/path/fqdn is between the double and single backslashes. This works equally well for drive letter and UNC mapping.

You also mentioned that you might wish to create files in the same location as the BE file or in a sub-directory thereof. If you use the database.Name property, the system will give you drive letters if and only if you mapped with drive letters. Otherwise, it will give you the UNC path and directory path to your BE file. All you have to do is find the rightmost "\" and drop everything to the left of that character. That is the path to your BE file. And it is correct whether you used drive letters or UNC pathing.

If this isn't consistent with your problem then you need to tell us a bit more about WHY it is not consistent.

 

[spikepl]

The code below relinks the linked tables according to the Backend location specified in an .INI file but here transmitted using
TempVars("BE_Location")

Public Function LinkTableDefs()
    Dim dbs As dao.Database
    Dim tdf As dao.TableDef
    If gcfHandleErrors Then On Error GoTo LinkTableDefs_Error

    LinkTableDefs = False

    If Dir(TempVars("BE_Location").Value) <> "" Then

        Set dbs = CurrentDb()

        ' Loop through TableDefs collection, only processing ' the table if it already has a Connection property. ' (all other tables are local ... not linked)

        For Each tdf In dbs.TableDefs
            If tdf.Connect <> "" Then

                Select Case tdf.Name
                    Case "tblMagicConfig", "tblLayouts"
                        If InStr(tdf.Connect, ".accde") Then
                            If tdf.Connect <> ";DATABASE=" & Application.CurrentProject.Path & "\Magic konfig.accde" Then
                                tdf.Connect = ";DATABASE=" & Application.CurrentProject.Path & "\Magic konfig.accde"
                                tdf.RefreshLink
                            End If
                        Else
                            If tdf.Connect <> ";DATABASE=" & Application.CurrentProject.Path & "\Magic config.accdb" Then
                                tdf.Connect = ";DATABASE=" & Application.CurrentProject.Path & "\Magic config.accdb"
                                tdf.RefreshLink
                            End If
                        End If
                    Case Else
                        If tdf.Connect <> ";DATABASE=" & TempVars("BE_Location").Value Then
                            tdf.Connect = ";DATABASE=" & TempVars("BE_Location").Value
                            tdf.RefreshLink
                        End If
                End Select
            End If
        Next

        LinkTableDefs = True

    Else

        MsgBox "Could not connect to  " & TempVars("BE_Location").Value & vbCrLf & _
               "Check location and content of  .INI file and the network", vbInformation

    End If

LinkTableDefs_Exit:

    On Error Resume Next


    Exit Function

LinkTableDefs_Error:

    Select Case Err.Number

        Case Else

            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure LinkTableDefs of Module Relinker"

            Resume LinkTableDefs_Exit

    End Select
End Function

 

[spikepl]

based on where the back end is installed

MarkK - the mapping thing is a good idea. But you need to have users that are not total [deleted by the censor]

The_Doc_Man - op asked what to do when BE is located elsewhere. I have a similar situation that I do not know what they will do: put the BE on the network or perhaps on a NAS or whatever...so I have an .INI where they write what they did and the code takes care of the rest,

 

[The_Doc_Man]

spike - I can see that the first time you connect you would not know where the BE was located, but my confusion has to do with whether it gets moved around every so often.

Once you move your BE to its final destination, link it and go on about your business. The .INI file just adds a layer that I don't see as being required. Of course, if you are going to move the BE file around, there is also the approach that you could link the DB after each move and then open the FE normally. From your opening form, use the FileSystem Object to look for your .INI file in the same folder as the BE file and if it (a) doesn't exist or (b) the creation date doesn't match up to what you expected, you can just delete the .INI file and write the new one. You could also choose to put that file in the same place as the FE file.