Pros and Cons about splitting a database

[TBC]

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

 

[pbaldy]

Here are some thoughts:

http://www.granite.ab.ca/access/splitapp/index.htm

 

[gemma-the-husky]

in general - only pros. can't think of any cons - except slightly harder to manage, for a single-user database.

 

[Galaxiom]

slightly harder to manage, .

I would say "trivially" harder to manage. The back end hardly even needs to be opened.

If a new table needs to be added just make the table in the front end, export it to the back end, delete it from the front end and then import it as a linked table.

Sometime convenient you can open the back end and define the relationships for the new tables.

Even single user databases should be split. The separation of the data from the application is a safer structure.

 

[guinness]

I'm surprised none of the above mentioned speed.

I think, in a multi user scenario, you pretty much have to split your database however expect that a form that maybe took 3 seconds to open on a joined database might take 10-15 minutes on a split one if distributed accross servers.

You can avoid this to some extent by steering clear of subforms or dropdown lists in fields and by ensuring that all your queries restrict the information that they return. My problem with that is that the whole point of the database is to run queries and to present information.

If you don't split then you run the risk that your data will become corrupt.

I've ended up with a Hybrid where users who have acceptable speed access via a frontend whilst the backend is actually a fully joined front and back that my users with slow connection issues input into directly.

the above is risky so make sure you have a good backup policy.

 

[Galaxiom]

.... expect that a form that maybe took 3 seconds to open on a joined database might take 10-15 minutes on a split one if distributed accross servers.

If you have this situation then you have something dreadfully wrong with your network.

I have a split database with subform whose SourceObject has a RecordSource query on a table that has nearly ten million records and yet it loads virtually instantly. It has several combos.

One design error is subforms using a big table as a SourceObject. If so then go and get a coffeee while it loads. Unfortunately Access's Form Wizard will spawn this hideous assembly if the main table has a subdatasheet that is a table. It is without question the most hopeless automatic decision in the whole Office Suite.

I've ended up with a Hybrid where users who have acceptable speed access via a frontend whilst the backend is actually a fully joined front and back that my users with slow connection issues input into directly.

I once suggested we needed a projectile vomit emoticon. Sorry but this is one place where I would have used it.

( o o )
>>O<<

 

[guinness]

Trust me I'm with you on the projectile vomit. If I open the database it springs to life in seconds even when I open a form which displays four subforms. Unfortunately not all users are on my server. I've had six users accross three sites with no issues yet one user with exclusive access that can make a coffee while it loads.

Needs must. I can't tell the slow users to put up with it because everyone else is fine. Maybe you can. Those that are entering straight into the joined back end will inevitably crash the whole thing but it may take weeks or months before that happens. All I'll lose is one weeks data so it's worth the risk to have everyone working inreal time.

I am of course looking at getting the backend onto something like a citrix server.

Anyone only needs to google 'split database running slow' to find out that it's not just about having a messy database. Queries, dropdown lists and subforms all contribute. There is a lot you can do at the design stage to design out the slowness but, short of having your database just displaying tables you are going to come accross some of those issues.

 

[Galaxiom]

When I got into splitting a database it was hideously slow. I Googled until I found every possible cause. By the time I had found the real problem the connections ran like lightning.

It eventtually turned out to be a problem with TCP on the clients and I had to reinstall Client For Microsoft Network, TCP/IP and File and Printer Sharing on them.

Now it boggles my mind how our Access and our network do what they do. Optimistic locking was also important too if I remember correctly.

 

[CBrighton]

If you have this situation then you have something dreadfully wrong with your network.

Until recently I had this issue, extreme performance loss through splitting.

Thankfully we have finally got our SQL server and so far using that for the BE has removed any and all slowdown.

 

[Simon_MT]

Splitting is no the problem, persistency is. Providing you have an always open table in the BE linked to the FE, there is generally no problem with performance save large record sets. Over a WAN I would always consider a Terminal Server.

FE/BE splitting is a must.

Simon

 

[kevincole]

I would never advise the splitting of a database.

If this is the kind of option you are looking at I would recommend a SQL Server back end and development of a front end to run on the clients machines. This could be Access or a .Net application.

Cheers,
Kev

 

[gemma-the-husky]

Kevin

just crazy

how do you have multi -user without a shared back end?

 

[Galaxiom]

how do you have multi -user without a shared back end?

Kevin does suggest using SQL Server as the BE. However this is overkill for most databases and requires extra knowledge and skills over a simple split Access database.

Thousands of developers successfully impliment split Access databases and recommending against it is just plain silly.

I would recommend never not splitting a database, even for a single user. This structure keeps the application and data in separate files.

 

[pbaldy]

I would never advise the splitting of a database.

The only production database I've ever built that I didn't split was one I use to manage my employer's PC inventory (and I'm the only user). I do use SQL Server a lot, but I have a number of pure Access split applications that run very well. You are definitely in the minority if you would have an un-split multi-user database.