MS Access FE/BE multiple Tables Multiple links

Question

I have a large complex multiple table in one database that customers use with a FE/BE link over some LAN networks which occasional corrupt the database

I was wondering if grouping the tables into separate multiple databases and using separate links would help limit the damage?

Note: I have no control over customers network quality and most customers have limited computer skills. Over all things work great but occasional glitches cause corrupt databases that may or may not be fixable.

Thk Sherwood

Answer 1

You dont say what version of Access you are using.

I used to use Access 97 and Access 2003 with a FE/BE situation and I have a lot of disconnects and corruptions. We paid for M$ technical support after many months of mission critical apps just failing or getting corrupt daily.

What we were told was for mission critical, use Access as your FE and some other RDMS as your backend. We already had Oracle running for other things so we used that. MySQL would work (free) and there are others still. Switching solved our issues completely.

Our databases were anywhere from 5mb to 1.2gb and they all were getting corrupt. We did also have about 20 users in the apps too. We have about 45 apps as well. I did Y2K on all of these What a drag that was!

Hit em up if you need anything else....

Answer 2

No, moving the tables into multiple BEs will make things worse in other areas and won't help with the corruption problem.

The problems with moving the tables into multiple BEs will mean relational integrity can not be enforced between the tables. Also folks will be tempted into restoring one MDB that gets corrupted not realizing that various updates that occured in other MDBs will now exist that aren't in the restored MDB. Things will get real messy very quickly.

If you have no control over the network quality then it's time to consider moving to SQL Server Express. This will require an admin to install on a computer in the network somewhere.

Some would suggest MySQL but SQL Server Express is also free and better integrated with Access. You will likely get a lot more assistance with SQL Server and Access in the newsgroups and such than you would with MySQL.

You should also think about how to run the SQL Server database without an DBA present. And assume that the users won't have a clue how to do backup. Make sure that their backup solutions will somehow handle SQL Server Express backups or run a task on a daily basis which places a backup file on a server somewhere which the users backs up. Or even consider making a copy on a "power users" local PC to at least help with hard drive failures on the SQL Server Express system.

And, of course the daily tasks such as truncating the logs, etc, etc. Such daily tasks of which I know next to nothing about.