I have a database that is split. The database is used by individuals not familiar with Access. I would like to allow the user to copy and paste the front end database, and have the back end database automatically tag along with the front end database. Is there vba code that would accomplish this??
Thanks for any replies, and any help would be appreciated.
I'm hoping that the problem you have is a misunderstanding of how your database is split. In an ideal world data in the back end, user interface (reports, forms etc) in the front end. Sometimes its appropriate to store some data in the front end (eg local copies of the data used in reports, temp tables, user depenedant tables)
But I'm not sure what you want to acheive by "copying & pasting" the DATABASE - do you mean you want the users to move the frontend (Application) around using copy & paste, or do you want the users to use C&P within the app to move around some data?
Adding to healdems' comments, I'm not sure what you mean either but this might clarify your thoughts....
The principle of front and back end is simple. They are typically both just ordinary access files (there's nothing mystical about them). The back end stores only data in normal tables and their would be only one copy stored in a centrally accessible place. The front end contains the reports, forms etc and their would be multiple copies of this file (one store typically on each users hard drive). I guess you know this part already !
The only subtle bit is the fact that the front end contains reference links to the tables in the back end rather than the tables themselves (that's all the splitter does! and can be done manually just by added the links manually). To all intents and purposes they work as if they were tables in the front end (except you can't change the table design from the front end). It's these references that ensure the front end sees the tables in the back end.
If you change the name of the back end file or move it to another folder, the link will not work. However, you can move the front end file around and copy it to where ever you like. It will still work providing the network is available for the link reference to be valid. If you choose to send a new version of your front end to colleagues, they can store this access file some place, open it and it will still look at the same back end (because the reference are still the same).
To facilitate this kind of distribution I put the working version of the front end on the central server. Then on each users pc I have a batch file to copy this file from the server to a local folder. Then when I want them to update to a newer version I email them and tell them to run the batch file. I'm sure there are cleverer ways but this works for me. Do make sure users don't try to run the centrally stored copy. It will work of course but it will be slowerer across the network and, more importantly, isn't designed to be opened by multiple users (so will cause problems).
I imagine if you try to automate the distribution user vba from within the front end then I guess you might have problems over-writing a file that you have open (but I've not tried).
Thanks for the response. The database I split is the template. The template database will be copied and pasted, numerous times, to the user's local drive and be used for a particular projects (one database copy for each project). The template database, which is on the public drive, is only there for the purpose of the user to copy to their local drive. Since I split the database, I wanted the front end and backend to be copied together to the user's local drive (or where ever the user pastes the copied database).
Since the user's like things to be simple, I wanted to be able to copy both the front and end databases when the user copies and pastes the front end.
Why did you need to split the front and back end ? If you are storing both files locally then I don't really see the point of splitting (although I'm sure someone will provide some good reasons ).
Anyway, take a look here It might not be exactly what you want but it should contain all the ingredients to get what you need.
If your front and back end are in the same folder then the code could get the folder path and set the links accordingly. If the backend is in another folder then clearly the path will need to be supplied by the user (using Dev's code) or hard coded.
I can't remember if I've tested this code or someone else's so I can't vouch for it.
ok, I think this is what I've had fun with in the past with success (although I can't check 'cos I'm not at home but I do vaguely rememeber the interface). I'm not very proficient with code but I managed to butcher it to suit my needs. It was a while ago mind.
will there be more than one person using any one db at the same time - ie do you have a multi user requirement. if not, then i'd suggest putting everythging back into one DB, then the user downloads the template containign the user interface AND data.
The reason -if you have users who are unfamiliar application software then in my view the scope for fowl ups is high, especially with linked tables. even if you provide good clear instructions then someone somewhere sometime is going to screw it up big time leading to others loosing their work.
you can link tables programatically, but its messy, it depends on names. you may be able to develop some means of encoding the data source. if and when it goes wrong it can be difficult to diagnose
the alternative is nbot to allow users to download on demand, but request you set up a copy for them. It may sound like you are doing their work for them, but personaly speaking I have too low a pain threshold esepcailly when it comes to having the post fowl up hanging party you know whose neck the rope will be around irrespective of who did the deed.
BTW - I disagree with the comment on the Link Steve (Howey) had - you can link multiple tables in one pass, you can't link multiple tables to multiple data sources in one pass, aside formt hat interestign none the less.
if the source code is available in that it may be a good starting point to write your own link table manager. that maybe the way to go: get you users to store the datafile.mdb name in the app, then seek to find that data, if found refresh the links, if not found.....