Question about splitting my database

[mrdod]

I have a few Ignorant questions about the benefits of splitting my database.

1. Will it enhance performance? Or is performance in how I distribute the front ends?
2. If data is stored in the back end why do I still have my tables being populated in the front end?
3. I can think of a bunch more but I'm tired of embarrasing myself

Thanks

 

[lameid]

Splitting your database in a multiuser environment where the database is not on the local computer enhances performance when you put the front end on the clients. There is just less to run over the network (typically the bottleneck). Also, Front End type objects tend to become corrupted more often, so this helps keep your data from becoming corrupted. Also since the Front End is probably centrally pushed to clients, corruption is easliy fixed by replacing the file versus really trying to fix it.

The Front end still needs to use the data in the backend so the tables are linked to the backend. Access goes and reads and writes the other file but everything behaves the same in the front end. This takes longer than everthing being in the same file but is typically faster than doing the whole thing over the network.

Also in a multiuser environment Access 2000 and forward is very unhappy when you try to design an object when someone else is using the file. Previous versions were more forgiving but you had the possibility for collisions to (i.e. I change the object and after some user who had it open says yes to save some filter on it which overwrites my changes).

 

[mrdod]

Thanks lameid. So is it best to convert my mdb to mde and distribute the front end to each user and keep one back end?
I'm trying to learn this as I go so please forgive the elementary questions.

Thanks again!!

 

[MajP]

Exactly what LameID said, splitting your database is the correct way to go. But it is not always smooth. In certain versions of Access there are bugs that get amplified when you split. A2K has three issues that I have seen bring a FE/BE database to a halt (persistent connection, auto name change, subdata sheet name). These are somewhat easy to fix. If you notice some performance problems post back.

 

[mrdod]

Will do thanks for the insight.

 

[lameid]

Yes, there should be a front end on each users client. Whether it is MDB or MDE is up to you. There are some definite benefits to an MDE. Either wayn just be sure to keep an MDB available as a source to make updates to that you can push out.

 

[Susanhopes]

Glad this thread is up, I've been considering doing this for a while now and have a similar learning curve ahead of me. Can any of you who have a front-back setup please tell me how much of a pain it is to replace those front ends when you make changes to objects? I have about 15 users in two offices and make changes to forms, reports, queries, pretty constantly to meet their needs. We run a school, a summer camp, and an accompanying development program, so while some things are static others need to stay very flexible.

Also, does anyone use front-back over a VPN? We currently use replication to keep us unified. Going over the VPN with the whole enchilada is waaaay too slow to be functional. Would be glad to get back to non-replicated design if the check-in with a table over VPN for data is fast enough.

I have one more terrible old "independent" database to merge into the main system, then we might be ready to do this. Thanks!

Susan

 

[lameid]

Susanhopes,
You replicate your application objects? OUCH! Replication is perhaps the best example of when to split your database.

Replication is the only Access feature that scared me out of using it when I read up on it. The way it resolves record conflicts is just creepy.

That said, using an Access Database over the VPN is a definite problem. If you think the replication is slow, just wait until someone trys to use the database over the VPN. Access is a File Server application. This means that every client manages the file and does file scans (why it is slower over a typical LAN). Over a VPN you will want to kill yourself. If you really want one central database, I recommend using a Server database product as backend (i.e. SQL Server, Oracle, MySQL). Even so you will have some speed problems compared to a LAN. Another alternative is some sort of terminal server.

However, to push out your front end, there is a FAQ that addresses this issue (for LAN's). You might have to do something similar and push a central source to each site's server.