By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Split database front end back end

P: 443
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?
Dec 30 '16 #1
Share this Question
Share on Google+
10 Replies

P: 214
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.
Dec 30 '16 #2

P: 443
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.
Dec 30 '16 #3

Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Declare Function apiGetComputerName Lib "kernel32" Alias _
  5.     "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7. Function fOSMachineName() As String
  8.     'Returns the computername
  10.     Dim lngLen As Long, lngX As Long
  11.     Dim strCompName As String
  13.     lngLen = 16
  14.     strCompName = String$(lngLen, 0)
  15.     lngX = apiGetComputerName(strCompName, lngLen)
  16.     If lngX <> 0 Then
  17.         fOSMachineName = Left$(strCompName, lngLen)
  18.     Else
  19.         fOSMachineName = ""
  20.     End If
  22. 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

Dec 30 '16 #4

P: 443
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.
Dec 30 '16 #5

P: 214
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim strTablePath As String
  2.     strTablePath = CurrentProject.Path _
  3.     & "\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.
Dec 30 '16 #6

P: 443
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.
Dec 30 '16 #7

Expert 100+
P: 1,107
There's some relinking code that might be helpful: Working with Front and Back-Ends
Dec 30 '16 #8

Expert 100+
P: 1,430
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.

Dec 30 '16 #9

P: 443
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.
Dec 30 '16 #10

P: 443
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.
Dec 31 '16 #11

Post your reply

Sign in to post your reply or Sign up for a free account.