question about the need to split Access databases

[Paul James]

I am working with several databases in a multi-user environment, and I am
trying to figure out whether it's always necessary to split the database
into a front end and back end. All the manuals say we should do this, and
everyone always tells me I should do it.

I understand that one advantage to splitting the database is that I can
continue to do work on a copy of the front end while the database is in use,
and I would be the only person in the front end while I'm making the
changes. But the disadvantage to having a split database is that it can
substantially slow things down. Sometimes to an unacceptable level.

What I have started doing is keeping the unsplit database in use while I
work on a *copy* of it. Whenever I want to put the development version into
production, I import the tables from the production db into the development
version during the off hours at night, then replace the (older) db with the
new one. This way, my production dbs don't have to suffer the performance
hit they would experience if they were split. It seems to work quite well,
and I'm not aware of any problems of doing it this way.

Is there any reason I shouldn't be doing this?

Thanks in advance.

 

[Allen Browne]

Hi Paul.

If that works for you, there is no problem.
My personal approach is to split right near the end of the development
process, before it goes into production. Even for single-user applications,
I still split because it makes it easy to deliver updates to a remote site
without overwriting their data.

Performance should not take a huge hit when you split. The issue is greater
in Access 2000/2002 than in previous versions, and Tony Toews has some ideas
to consider about how to reduce the performance hit at:
http://www.granite.ab.ca/accsmstr.htm

 

[Paul James]

Thanks, Allen.

Looks like a good site.

 

[Stacy Ouellette]

Paul,

I am doing the exact same thing that you are doing. I
have a couple of databases that are being used by
multiusers across the network. I have a backend, frontend
and .mde file. I can go into the the backend and frontend
at any time and update them. Then I simply recreate
the .mde file for the user. The next time they log on,
their forms are updated and wala, all is well. You are
doing the right thing in my opinion. However, I'm not a
developer so someone with a lot more experience may tell
us otherwise.

Stacy

 

[Paul James]

Hello Stacy,

Thank you for the info. Now that you mention it, saving the db as an MDE
file might be a good idea for me as well. As Allen pointed out in his post,
splitting the database saves you from having to overwrite the tables when
you deliver an upgrade, but this would seem to be more of an issue for dbs
that operate continuously. In my case, I can overwrite those files after
normal business hours. Maybe you also have down periods where you can do
that as well.

I did find a caveat in one reference source, "Building Applications with
Microsoft Access 97." On page 407, it says:

"Saving a database that contains tables as an MDE file creates complications
reconciling different versions of the data if you need to modify the design
of the application later. For this reason, saving a database as an MDE file
is most appropriate for the front-end of an application that has been split
into a back-end database that contains only tables and a front-end database
that contains the remaining objects."

My feeling is that performance slows down to an unnacceptable level when the
db is split, so I'd probably be willing to risk some complications
reconciling different versions of the data. However, I'm not sure what they
mean by "reconciling different versions of the data." Do you know what
they're referring to?

Paul

 

[John Vinson]

But the disadvantage to having a split database is that it can
substantially slow things down.

yes... but multiple users attempting to use the same database slows
things down too, and markedly increases the bloating and risk of
corruption.

 

[Paul James]

But the disadvantage to having a split database is that it can

yes... but multiple users attempting to use the same database slows
things down too, and markedly increases the bloating and risk of
corruption.

Does splitting the database reduce that bloating and risk of corruption?