Database Splitter

[aTekTipsUser]

I have a database I am looking to split into a front end and back end. Instead of having the data in access though, I am putting the data into sql server. Eventually, I will convert all the queries and modules to run on the server. But for now I am just putting the tables into sql server.

From what I have read about splitting a database, I should install the frontend on each individual's computer. I make changes frequently to the forms, queries and reports. How do I update all the computers with the changes with little or no user interaction? For example, when they close the frontend access database, can it look for updates and automatically install?

 

[TeriCoombes]

I have the same situation. What I have done was have the front end on the network server and put shortcuts onto each individual computer. This makes updating a lot easier. You just have to make sure everyone has the proper permissions to access the server.

 

[lpgagirl]

Teri

You have the front end on the Network? Where is the back end?

I am looking to do the same thing. I have a multi-user data base and I would like to be able to make changes with out having to ask all users to shut down. You're answer has got me intrigued as everything I have read says back end on server, front end for users with a "development copy" for me to make changes. But if your system is working well, I would like to know about it.



Jeannie

http://thefloyds.50megs.com/index.html

 

[aTekTipsUser]

lpgagirl,
I have my backend on sql server.

What I ended up doing was putting the access database, with all linked tables to the sql server, on the network along with a program that I developed that the user selects which database they want to work with (because I have many). The program then checks their local access database, which I have a table that has the version in it, to the updated version on the network. If the versions are not the same or the database does not exist, the program will copy the database on the network to their local drive. I make all changes to the access database on my system, then copy the database up to the network with the new version in the table. The next time that the users run the program to update the database, the program will see there is a change and copy it down to their local system.

 

[aTekTipsUser]

By the way, I tried having everybody run from the database on the network, but making changes was terrible. If somebody had a form or report open, I could not make a change. Frequently users would get errors that the form/report was locked and they were asked to save it as another name. I also ran into situations where I made a change to a form, but a few minutes later it was overwritten by another user. That's why I created the program to copy it down to their local system.

 

[lpgagirl]

What are the chances of getting your copy program? Sounds like a good solution.

Jean

Jeannie

http://thefloyds.50megs.com/index.html

 

[aTekTipsUser]

The program is written in vb. Do you know vb? The program is hardcoded for my network.

 

[scsuflyboy2005]

First I want to state that I am in no way an Access guru.

With that said, what i did was put the back end on my network server.

Then I created an MDE file of the front end which I distributed to everybody.

This leaves the Original MDB file open for changes without creating havoc to other users. The only drawback to this is that each time you update your forms, or whatever you change you have to recreate a new MDE file and re-distribute it to everyone.

 

[MoLaker]

I managed an application which was set up much like the one aTekTipsUser described. However, I never got around to writing a VB program like his to handle front-end update distribution. I wish I had, because his solution sounds great. But, for those unable to develop such a program, I'll describe the process I used and perhaps it will be helpful.

I, too, had an Access FE with an SQL Server BE. The FE was linked to the SQL Server tables, so they appeared much like Access tables as far as FE development was concerned. I distributed a MDE version of the FE. To handle updates, I had a table in the BE where I stored the latest version number. I also embedded the latest version number in a startup form which was opened by the startup options settings. (The embedded version number on the startup form was actually just a label which displayed the version number.) Upon startup, the startup form performed a table relinking to ensure connectivity and to handle those few times I had to change table structure. After the relink process, the startup form would query the SQL Server table for the version number stored there and compare it with the version number embedded in the startup form label. If they did not match, a message was displayed instructing the user to "Refresh" his application and would then close the form and quit the application. The user could not continue without updating his FE. Each workstation had a shortcut to a batch file which, when triggered would copy the MDE I had just uploaded to the server down to the workstation. The user could then restart the app.

aTekTipsUser's system is definitely better, but for the less talented, my system works well.

As for whether the FE should reside on the server or workstation, I would definitely go for the workstation. Not only does it resolve updating issues with being locked by other users, it also greatly reduces FE corruption. Most commonly a single user will blow up the FE some way, somehow. If all users are sharing the same FE, nobody works. Done my way or aTekTipsUser's way, the user just "Refreshes" his copy and he's back in business and, may not even have to call the administrator. Another positive side effect was I never had to concern myself with compacting the FE's. All the user had to do was download another copy (which is why I called it "Refresh"). So, with a little training, the users became quite adapt at downloading and trying a fresh new copy before they called me with a problem - and that usually fixed their complaint.

 

[lpgagirl]

Thanks for all of the information. Evidently, I have some more research to do and some tech guys to consult. I know a little VB, certainly not enough to create an application that you are referring to. I will consider all of your suggestions. Great input - thanks.



Jeannie

http://thefloyds.50megs.com/index.html