Questions about Splitting a Database

Question

I have a few questions about splitting a database on a shared network drive.

I'm setting up a pretty small database for a corporate client.  I'm talking about maybe 30 or so ComboBox objects and a few TextBox objects, all on one form.  There will probably be only a couple of tables, maybe 3 or 4, max.  One table will feed data into the ComboBox objects and one table will be used to capture results from the users, after selections are made from each ComboBox and data is typed into each TextBox, then users will click a button, run a VBA script, and write everything to one single table.

How many users can realistically share a split database?

Is there any possibility of corruption if, let's say it is split b/w 50-60 users?

Are there any gotchas, or caveats, or anything I should be aware of before setting this up?  Again, I think this will be a VERY SIMPLE database with 2 tables at minimum, and 4 at maximum.

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 1

Each user should have a copy of the front-end on their own local PC (or drive). You will only run into a problem if more than one user accesses the same record at the same time and both try to update it. You should design your tables to minimize this possibility. I don't see any problem with that number of users provided only about 50% of them are working simultaneously.

Make sure your tables are normalized. You should probably have a separate table for each drop-down.

Answer 2

Hi. The real question is how many "concurrent" users will you have. You could give a copy of the front end to a thousand users but only up to about 50 of them should probably be connected to the data at the same time.

Answer 3

There will probably be 50-60 users, max. 

I don't see how people would access the same records at the same time.  All they will do is run a bunch of Insert statements.  No Updates will be done at all!!

How do I 'give a copy of the front end'?  I was thinking about that today.  How can I 'push' a copy of the front end to 50-60 people?  I know how to do it for myself; Database Tools > Access Database > Split Database.  How do I do this for 50-60 people?  I don't have to go to each machine and do the same manual task over and over, right.  There must be a much more efficient way of doing this, right.

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 4

Some people us a fancy approach, such as Tony’s FE updater here:

http://www.granite.ab.ca/access/autofe.htm

(above has a free trial, but is a for pay product).

Some just use a simple batch file that copies the front end from the server each time, and then runs that FE. So their shortcut is a simple batch file.

Others build their own updater – (say check a version number in a front end table, and then in the back end table – if lower version, then they shell out to a batch file and exit the application. The batch file then copies the new front end (make sure you exit the FE First!).

A detailed explain of how and why splitting works is outlined here:

http://www.kallal.ca/Articles/split/index.htm

So there are “many” ways. I for some time used a paid copy of WinZip that could “zip up” and also looked like a install. I current use Inno for this update – simply shell() out to the new version, and exit Access.

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

[e-mail]

Answer 5

Thanks guys!  It all makes sense.  One last question.  I put in some VBA code to refresh the links between the FE and BE.  I'm just asking the users to click the button to ensure the FE and BE is refreshed before doing any real work.  Does this make sense?  I think it does.  Again, this is the very first time I'm dealing with a FE and BE setup.  Even after 17 years of using Access, this is the first time I'm doing this kind of thing.  I want to make sure it's done right!!

Thanks again.

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 6

Hi,

if there is the same BE path for all users and if I have (local or remote) access to the target system I refresh and check the path myself, in the new FE model before it gets distributed. Sometimes a trusted admin user at the client does this. So it is done just one time and without having to bother or rely on the standard users.

As FE distribution tool in the last years I've frequently used and recommended Peter's APS . It is very easy to use, efficient and free.

---

cu
Karl
Access FAQ (de/it): donkarl.com
Access Lobby: AccessDevelopers.org

Answer 7

Hi. Another option is to use J Street's Access Relinker. Good luck!

Answer 8

For many years I've been distributing files which use the method illustrated in my Refesh.zip demo to automatically check and, if necessary, refresh links at start-up.  You'll find the demo in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo the RefreshLinksSingle_07.acdb file would be suitable for you, where the back end is a single Access file.  The other file in the demo illustrates a method where more than one back end file is referenced.  The demo uses a slightly modified version of Bill Wilson's BrowsForFileClass class module to browse to the back end by calling the Windows API GetOpenFileName function, but these days I'd use the FileDialog property of the Application object.  You'll find the use of the latter in a different context illustrated in the Browse.zip demo in the same OneDrive folder.

---

Ken Sheridan, Stafford, England

Answer 9

Thanks for sharing Ken!

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.