Front End / Back End with Access
Front End / Back End with Access
(OP)
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....
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....
Talk To Other Members
RE: Front End / Back End with Access
-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
RE: Front End / Back End with Access
If you want the best response to a question, please check out FAQ222-2244: How to get the best answers first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
RE: Front End / Back End with Access
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.
www.fxxxingcomputers.co.uk
RE: Front End / Back End with Access
In version 2010, it's Database Tools > Access Database.
--Lilliabeth
RE: Front End / Back End with Access
Bye, Olaf.