Front End / Back End with Access

[zandsc1]

All -

I have an MS Access DB that has grown well past the point where it should be split into front end and back end. The problem is that when I split the database in two, the program runs like a three-toed sloth. Events that take 1-2 seconds when the database is not split take literally 60 seconds or more when I split the database.

Every helpful article I've read on database design talks about splitting the data source and interface, so I'm being persistent about finding a way to do this. My conclusion thus far is that either (A) I did something fundamentally wrong when I split the database or (B) The company network has some flaws that will not allow us to use a database this way.

First, I consider that I might have split the db incorrectly. I took our .mdb file and moved all the tables out into a new .mdb file. I then created links to all the tables in the front end. Perhaps this was the wrong way to do this? Should I be creating and destroying connections to the back end with a database variable programatically each time I need it?

Next, I consider that our network is simply not capable of handling this setup. We're a small company and we do not have an actual windows server, just a normal desktop dedicated to general use files.

Any suggestions on turning my three-toed sloth into a cheetah? Heck, I'd settle for a really fast giraffe at this point....

 

[ChrisHunt]

You might be better off asking this in forum181

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[johnwm]

...or using a real database for the back end (MySQL is very easy to use)

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[flyboytim]

If I were splitting up an Access database into front and back ends, I would first try out the split .mdbs on the same machine before placing it on a network. I used to use the DOS SUBST command to create a drive with a letter, say M:\ to put the back-end database, which would consist of just the tables, and the relationships to preserve the "referential integrity".

Today, I'd probably put the data mdb on a USB flash drive for testing purposes. Thus the speed hit due to the network could be tested, just by mapping/unmapping a network share and plugging in or removing a removable drive. If you can't get your database on a thumb drive, you should be using SQL Server or something else.

There would be more than one "front end", a development/management mdb and working user front ends, with functionality dependent on individual user needs.

On the development/management front-end, all the structural queries, forms, reports, macros and code, user control stuff, development and experimental bits, ad-hoc queries and quality control queries etc.

Wherever possible I would avoid using macros or VBA code, managing functionality either with Access SQL or Form and Report objects. By doing this, difficult-to-find bugs are kept to the minimum, the code being totally transparent SQL statements, or Objects that are common to Microsoft Access, rather than hand-crafted, clever stuff.

When splitting mdbs, the compact and repair facilities are necessary to clean up all the 'holes' in the MDB structure, and this gives you the opportunity to make staged backups at the same time. Large files are prone to fragmentation - if moving data to a new "server" location, consider making an exclusive partition for that mdb.

http://www.fxxxingcomputers.co.uk

 

[Lilliabeth]

Please follow Chris' advice and ask this question in an Access forum. In the meantime, if you have version 2003, it's Tools > Database Utilities > Split Database.

In version 2010, it's Database Tools > Access Database.

--Lilliabeth

 

[Olaf Doschke]

Before you split the mdb and seperated the tables, where was that single mdb located? At the server, wasn't it? Then the network can't be the reason, as clients starting the mdb from it's central position did already do everything over the network. Loading both GUI and DATA from it.

Bye, Olaf.