Pros and Cons about splitting a database

[TJVFree]

Hello everyone, I’m looking for a list of Pros and Cons about splitting a database. Does anyone have one or able to put one together for me? We are using MS Access 2003

Thanks
TCB

 

[MichaelRed]

Don't know many (any?) significant cons. Not JUST a pro, but a necessity for any multiuser db.

Ever so many 'examples' and discussions here (and there ... and every where) just search on the tern her (Tek-Tips fora) and the wider net




MichaelRed

 

[lameid]

There is only one Con to splitting a database file I can think of. Performance.

Access databases run fastest when everything is in the same file on the local computer.

Performance and multi-user issues aside, the Pro to splitting the database is that corruption to database file seems to most often hit the file where Front End objects live. So when the application quits working you are not at risk from losing any data if the corruption happens to be catastrophic and you have to go to the latest backup. In a multi-user environment where I keep the front-end on a server, I don't even bother with a compact and repair, I just copy the good version over the bad one.

In a multi-user environment splitting the database is a pro as it allows the Front end or application to be put on each local machine and thereby reducing network overhead (not to mention that Access 2003 does not play nice when multiple people open the same non-table objects).

That's my in a nutshell version of what you need to know. My recommendation is to always split your database in a business environment as the data should be on a Server so it is backed up (assuming the Servers are backed up... If you work at a bad place I'm sure you know it).

 

[JoeAtWork]

To me it is like asking what the Pros and Cons are of stopping at a red light. There's really no good argument for not splitting the database, and if you fail to do so it is almost inevitable that your data will get damaged.

The one argument you may hear against splitting is the difficulty in distributing the front-end to all computers when there is an update. However, this is solved by using a simple batch script to launch your front-end, which automatically copies over any new front-end file from the server to the local machine before launching it.

 

[TJVFree]

Thanks everyone, definitely going to split

 

[lameid]

To me it is like asking what the Pros and Cons are of stopping at a red light. There's really no good argument for not splitting the database, and if you fail to do so it is almost inevitable that your data will get damaged.

Great analogy!

The one argument you may hear against splitting is the difficulty in distributing the front-end to all computers when there is an update. However, this is solved by using a simple batch script to launch your front-end, which automatically copies over any new front-end file from the server to the local machine before launching it.

Access 97 I think was the last version that didn't whine and cry about locking objects when sharing the application objects... I doubt it would be a recent arguement.