Split Database OR move to SQL Server

[Dkline]

We have a database on a server which is edited by 4 people daily. It has
recently had an increasing frequency of requiring a Compact and Repair to
the point where it is now a daily occurence.

There appear to be three solutions:
1. Get everyone one on the same Access versions. Current range is 2000,
2002, and 2003 - no one is the 97 version. So the thought is get these four
people on the same version of Access and the problem goes away.
2. Split the database. Have BE on the server and FE on each local machine.
3. Move to SQL Server.

In the file in question, there is one table with about 77,000 records in it
with 30 fields. Total file size is about 45 MB. This file is mission
critical.

My opinion is #3 - take the plunge and move to SQL. Senior management wants
#1.

So my question is: how well does the FE/BE split work? Enough to not need to
go to SQL Server at all? Or does it just buy me some time until I can get up
to speed on SQL Server?

 

[Rick Brandt]

We have a database on a server which is edited by 4 people daily. It has
recently had an increasing frequency of requiring a Compact and Repair to
the point where it is now a daily occurence.

There appear to be three solutions:
1. Get everyone one on the same Access versions. Current range is 2000,
2002, and 2003 - no one is the 97 version. So the thought is get these four
people on the same version of Access and the problem goes away.
2. Split the database. Have BE on the server and FE on each local machine.
3. Move to SQL Server.

In the file in question, there is one table with about 77,000 records in it
with 30 fields. Total file size is about 45 MB. This file is mission
critical.

My opinion is #3 - take the plunge and move to SQL. Senior management wants
#1.

So my question is: how well does the FE/BE split work? Enough to not need to
go to SQL Server at all? Or does it just buy me some time until I can get up
to speed on SQL Server?

Option 2 would likely be all that is necessary. What you're describing
both in terms of size and number of users is miniscule.

Access 97 tolerated multiple users in the same file much better than the
newer versions do. I'm surprised you lasted as long as you have. I don't
know if having different versions (all later than 97) has any bearing on
the problem at all.

 

[Brian]

Option 2 would likely be all that is necessary. What you're describing

both in terms of size and number of users is miniscule.

Access 97 tolerated multiple users in the same file much better than the
newer versions do. I'm surprised you lasted as long as you have. I don't
know if having different versions (all later than 97) has any bearing on
the problem at all.

I agree, split the database. It's easy and works very well indeed. I would
encourage version standardisation for lots of good reasons, but it may not
be necessary to solve this problem. Finally, do NOT convert to SQL Server.
This Access setup with 4 users and a small database should work just fine:
if it doesn't, fix it. It's always gonna be much easier and cheaper than
converting!

 

[John Vinson]

We have a database on a server which is edited by 4 people daily. It has
recently had an increasing frequency of requiring a Compact and Repair to
the point where it is now a daily occurence.

There appear to be three solutions:
1. Get everyone one on the same Access versions. Current range is 2000,
2002, and 2003 - no one is the 97 version. So the thought is get these four
people on the same version of Access and the problem goes away.
2. Split the database. Have BE on the server and FE on each local machine.
3. Move to SQL Server.

In the file in question, there is one table with about 77,000 records in it
with 30 fields. Total file size is about 45 MB. This file is mission
critical.

My opinion is #3 - take the plunge and move to SQL. Senior management wants
#1.

I'm with Rick: split the database. 77,000 records is VERY easily
within Access' capabilities; A2000, A2002 and A2003 all use the A2000
database format as the default, so there should be no problem having
the backend in A2000 and having frontends in all three versions
connecting to the data.

As a rule "mission critical" applications should be moved to SQL or
another true client server system (to get the benefits of robust
transactions, hot backups, data logging, etc.) as soon as practical;
but - as I'm sure you know - this requires a pretty major commitment
of resources to administer the database. If you keep good current
backups and can tolerate occasional outages to restore from the
backup, Access is still a reasonable choice; splitting the database is
*universally* recommended and works very well, and will greatly reduce
the incidence of corruption.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dkline]

Thank you all for your replies. We'll have a go at the database split. I
expect at some point we will have to bite the bullet and go to SQL Server.