Database Splitting.....the cons/pros. 2

[cavery]

Hello all,
I'm using Access 97, I have a group of users, around 80. I just took over this system and I have issues with speed etc. I wanted to know what does anyone know about Database Splitting? back and front end?....I saw this option under Tools, and I've saw some threads about this but before I split my database I wanted to get some feedback, as much as possible actually....so go ahead, feel free to send me links, webpages, reference materials...whatever you want.

Thanks alot!
Clark
Honda of America, Manufact.,

 

[JeremyNYC]

Clark,

With 80 users it's mindblowing that the application is not already split. I can't imagine the headaches involved in maintaining that.

As far as I'm concerned, every application that has more than one user should be split. It's really a no-brainer.

Splitting your database will cause a small performance hit in most cases. But in a situation where everyone is using a single copy of the file sitting on the server, that's probably not true, as the performance hit of doing 80 peoples' processing on one machine has to be rather huge.

You will experience less corruption with a split database. And you will be able to copy over just the front end, not the data when it's time to roll out a new version. And you won't have to copy over anything when a user starts up the latest version of the database--it will already be sitting on his or her PC. And you will be able to roll out a new version without getting people out of the existing version. And you will learn how to deal with split databases, which are the industry norm. And much more that I (for some reason) can't think of right now.

Jeremy

And =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[WildHare]

Clark, as Jeremy noted, splitting into a FE/BE scenario is generally a good thing. However, some things to keep in mind:

Access is a FILESERVER application, not a "true" Client/Server. Thus, when a user requests a record or two from a table, the BACKEND will ship the entire table to the workstation, for the user's Access process to evaluate. As it is now, the "entire" database file is going down to the user, so splitting into data on the back and and everything else on the front end will NOT speed up the application in a very noticeable way - you'll just be reducing the size of the transmitted file by about 10% or so (the forms/queries/reports etc that are in the Front End .mdb file)

Anything you can do to DELAY the transmission of entire tables to the user is a good thing - For example, when doing DATA entry, keep a "holding" table with the same structure on the F/E for the data entry, and SHIP the records to the B/E when you're done (using an APPEND QUERY), and then erase the data out of the dummy table on the F/E. In effect, you're doing transaction processing by batching the new records and shipping them UP, rather than shipping ALL the old records down to the workstation and then "adding" more records, which, of course, still need to be shipped up.

You might also look at the database and see if there are any relatively static tables that you can keep in the FRONT end database file, and thus eliminate some network traffic.

There are actually two ways of "splitting" an application - keeping the F/E and the B/E on the network, and having each user grab a copy of the F/E when they need it, or actually PLACING the F/E file on the user's workstation. Each has advantages and drawbacks when it comes to enhancements, user workstation upgrades, etc.

Hope this gets you thinking.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[cavery]

Thanks alot to you both,....sounds like I have some options to explore. I Appreciate the replies....

Thanks,
Clark

 

[JeremyNYC]

Jim,

Interesting strategy, the local tables that mimic the tables in the back end. Two questions: how do you deal with bloat? and how do you deal with record locking?

I suppose for bloat you could just grab a fresh copy of the FE from the server every time, though I have recently started only pulling it over when a new version is needed by the user.

Just curious.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[WildHare]

Jeremy - in the app I did using local copies of static lookup tables, I set some flags whenever the "host" copy was updated, and the user needed to d/l a fresh copy before they could continue. This was a moderately complex system outage tracking system for an electric utility, and there were half a dozen or ten large tables used almost exclusively for data validation/lookup purposes. And they were fairly static - maybe three or four updates per month. So it was not a major thing to keep them local and update the F/E when necessary.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm