Problems with Split Database

[cbriscoe]

I've worked with Access for a long time - but usually just stand alone databases.
Here is what I need. I need the FrontEnd of the database to be separate from the Backend, so of course I split the database. But with each person that installs the program, they ideally should have their own "backend" - there is no reason why they would need to see everyone else's data - and in some cases this may let loose a flurry of complaints with privacy issues. Also, many of the people would use the program in places with no internet access - so the location cannot be on a server. So I wanted to keep the back end on the hard drive of each user and have updates sent to a main computer (with a button click on their screen this would happen).
The problem I am running into is the linked tables. People have different drive letter assignments so the link table location changes with the computer.
Is there a way to generically put in the location of the backend?
I am definitely a newbie with modules as I tend to rely on the shortcuts that Access gives. So if the answer is a Module, you may lose me

Any help or insight would be awesome!
Thanks!

 

[Mr. B]

If you do not need to have the application share data between users, then you can simply put the backend file in the same folder as the frontend and use the Linked Table Manager to link the frontend to the backend.

Hope this helps.

 

[Pat Hartman]

I've attached a very old sample that should get you started with relinking.

 

[jonathanchye]

From what I understand this is a pure standalone single user database. You simply need to gauge if there is a need for splitting in the first place. I reckon it will be easier as a single database but with "Compact On Close" selected.

Keep in mind though everytime you update the front end the user's data might be lost.

 

[Pat Hartman]

Keep in mind though everytime you update the front end the user's data might be lost

That's why you split the database. You do not want to be responsible for converting the user data. You don't want the user to have to send his database to you for upgrades. He would have to remember to not update anything while it was in your custody because if he did, the upgraded database would be missing that data and the user might not notice it immediately. All in all a very dangerous situation. Split the database. It is the "best practice" method.

 

[jonathanchye]

That's why you split the database. You do not want to be responsible for converting the user data. You don't want the user to have to send his database to you for upgrades. He would have to remember to not update anything while it was in your custody because if he did, the upgraded database would be missing that data and the user might not notice it immediately. All in all a very dangerous situation. Split the database. It is the "best practice" method.

If you bothered to read his post (or have proper English comprehension) you will notice the OP mentioned that "each user has their own backend".

Yes, split databases have their benefits but sometimes you need to analyse the requirements and not just blindly advocate it.

And to actually provide a helpful answer, I would need to know if you plan to regularly update the frontend and how would you do it. Also need to understand what the OP meant by "updates sent to main server".

Perhaps another solution to consider if you are using Access 2010 is to package your database.

 

[Pat Hartman]

You're getting snippy with the wrong person.

I did read the original post and if you had read my answer (and have proper English comprehension) you would have understood the point I was making about converting the customer's data. It is not relevant whatsoever whether the database is shared or not. If the database contains the customer's data, you are responsible for converting it.

 

[jonathanchye]

You're getting snippy with the wrong person.

I did read the original post and if you had read my answer (and have proper English comprehension) you would have understood the point I was making about converting the customer's data. It is not relevant whatsoever whether the database is shared or not. If the database contains the customer's data, you are responsible for converting it.

I do apologise. Initially I read your reply as targetted towards my response.

Reading OP's initial post made me question his intentions. He seems to want data stored locally while still having the function to send data to a main server.

 

[Pat Hartman]

Apology accepted. I did quote your response so I can understand the confusion but I was simply trying to emphasize the point and explain why even single-user applications should be split. If the data does not need to be retained on upgrade then it is certainly easier to go with a monolithic app but that is rarely the case.

 

[cbriscoe]

jonathanchye assessed what I needed dead on (I realize I am bad at explaining, probably why I'm stuck behind a computer.
Even with the backend database in the same file I still run into problems installing the Runtime version on other computers. Front End loads fine, but all linked tables search for the original location of the backend database.
So here's a quirk, possibly, the computer I am developing the program on has I:\\ instead of C:\\ for the main drive. Every computer I am trying to install the program on tends to have the C drive assigned as main. Do you think it would be as easy as moving the development to a computer with a C:// drive assigned as main? I would still, of course, run into problems with computers that do not have the C drive assignments, I would imagine.
Another quirk, the original program was written in mdb. That absolutely linked fine no matter which drive the front end and back end were on. With updates and using new capabilities, I moved the development to accdb. That is when the problems began. When I searched for solutions on the internet, I just saw a lot of other people were having the same issues, but no real solutions
Thanks for everyone's response - I really appreciate all of your help

 

[Pat Hartman]

cbriscoe,
There is no magic to relinking. Access will not do it automatically. It can't. The only time you can move from computer a to computer b and not "loose" links is if both computers have identical path structures to where the BE is located. Short of that, YOU need to provide some means for the user to "find" the BE. In the case of a single user FE, you could write the code that assumes the BE will be in the same folder as the FE but a more generally useful solution is the relinking example I posted.

 

[AHearnden]

@briscoe - could one solution be, as soon as the user has opened the frontend, store their current folder (e.g. extracted from CurrentDB.Name) then run some code that deletes the linked tables and relinks them from the backend file in that folder.

 

[Solo712]

I've worked with Access for a long time - but usually just stand alone databases.
Here is what I need. I need the FrontEnd of the database to be separate from the Backend, so of course I split the database. But with each person that installs the program, they ideally should have their own "backend" - there is no reason why they would need to see everyone else's data - and in some cases this may let loose a flurry of complaints with privacy issues.

First issue: what you are saying indicates that you are thinking multiple front-ends and not back-ends. The BE portion is tables only and if you restrict users to access data only through froms and reports in the FE you have no problem with people seeing parts of database they should not be seeing. You design multiple front ends that access select portions of a single BE.

Also, many of the people would use the program in places with no internet access - so the location cannot be on a server. So I wanted to keep the back end on the hard drive of each user and have updates sent to a main computer (with a button click on their screen this would happen).
The problem I am running into is the linked tables. People have different drive letter assignments so the link table location changes with the computer.

Looks like your application will need to be able to handle UNC notation. Look here for sample code to convert from drive letters to UNC style locators. I have not tested it but there are plenty of utilities like this around.

Is there a way to generically put in the location of the backend?
I am definitely a newbie with modules as I tend to rely on the shortcuts that Access gives. So if the answer is a Module, you may lose me

Any help or insight would be awesome!
Thanks!

There is an excellent utility for relinking tables for multiple versions of the same app (for dev, test and production) in Chapter 12 of Andrew Couch's book Access 2010 VBA Programming Inside Out. You definitely want to check it out. It is on line here with the code included ! Andrew placed it in public domain, great guy that he is.

Best,
Jiri

 

[gemma-the-husky]

one simple way is to have a config file storing the path to the back end

when the front end opens check

a) whether tables are connected
b) whether they are connected to the back end required in the config file

if either of these tests fail, reconnect the tables with suitable code