Split database front end back end

[CD Tom]

I've split my database so have a front end and back end. Now my question is if a user decides to install the application on a different drive than C: which is where the back end of the database shows is the a way in VBA to take care of the situation?
Thanks

[gnawoncents]

There are a number of different ways to do this, depending on your scenario. Each of these events would be triggered by a form that loads in the background at startup and checks for broken links to the back end tables.

1) If the front end and back end will always be positioned relative to each other, you can use VBA to get the path of the front end and append whatever extra path needed from there and use that to relink your tables.

2) If there is no way to know where they will place the front end in relation to the back end, you can have them manually select the location by using VBA to pull up the file picker and use that location to relink your tables.

Like I said, there are plenty of other ways to do this, but these are two of the simplest.

[CD Tom]

Thanks for the quick reply. I like the option 1 but have no idea how to do this. Do you have some examples of how I can accomplish this.

[PhilOfWalton]

An approach that I use (It's a bit more complicated that what I shall describe here) is this.

In the FRONT End, create a table that has all the computer names that will use the program and the corresponding paths to the BE databases.

This function will give the name of the current computer.

Option Compare Database
Option Explicit
 
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSMachineName() As String
    'Returns the computername
 
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
 
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
 
End Function
 

Basically when staring the database, have a startup form that runs code to
1) Detach all the tables
2) Find out the current computer name using the above function.
3) Read the table to find the path
4) Verify the path exists using the Dir() function
5) Link the tables

Phil

[CD Tom]

I don't want to sound dumb but there are some more questions. When you say all computer names, I'm not sure if you mean just the names of the front end and back end or what, and what paths do I put in the table. Also how do I detach all tables, this is something I've never done before.
Like I said before this is all new to me, so thanks for the help.

[gnawoncents]

Phil's solution is valid and will work so long as you know in advance where the back end will be relative to each computer (unless I'm missing something).

This might work perfectly for you, but we would need to know your type of setup first. If, for example, you will have only one copy of the front end for each copy of the back end, and the back end resides in a subfolder from where the front end is installed, you could use the code below to determine the path.

    Dim strTablePath As String
    strTablePath = CurrentProject.Path _
    & "\Tables\Name of Your Back End.accdb"
 

In this example, the user can drop the folder anywhere they want, so long as the folder containing the Front End has a subfolder (named "Tables" in this case) containing the Back End.

Again, this might not suit your needs at all, but the right solution for you will depend on how and where your users are installing the database and whether or not you have any control over the process.

[CD Tom]

I use the standard package option that comes with Access 2007 developer. Both the front end and back end are installed in the same directory. If the user decides to put the application in say the D: drive and my program has the back end linked on the C: drive. I need to be able to relink the back end to the D: drive in order for the program to find the back end.

[jforbes]

There's some relinking code that might be helpful: Working with Front and Back-Ends

[PhilOfWalton]

Sorry, I wasn't clear on my post. My assumption is that a FE database (and the table I mentioned) is sitting on each computer.

The Computer name can be found by typing
? fOSMachineName
in the immediate window.

So that is the name of the computer the FE is residing on.
Thereafter the BE database holding the tables can be on any computer you like including the same computer, a network computer, Dropbox etc.

Phil

[CD Tom]

I was looking at the link Working with front and Back End. I can probably get this to work. Here's what I would like to do. After the installation of the program when the user first opens the program I would check to see if the BE is located on the same drive as the FE (this is how the installation puts them). If the linked BE shows the C: drive and the program is located on the D: drive then the program would re-link the BE to the current location on the D: drive. If everything is OK then the program opens with no re-linking. So the re-link would only happen if the program was installed on a different drive than C:
I hope this makes sense and thanks again for all help.

[CD Tom]

Ok, I found a way to do this, seeing I already had a re-link section in the program with just a few modifications I was able to do exactly what I wanted. So now no matter which hard drive the program is installed in the program will automatically link the BE with the database. Thanks for all the help and suggestions.