×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Front End / Back End with Access

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....

RE: Front End / Back End with Access

...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: 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

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.

www.fxxxingcomputers.co.uk

RE: Front End / Back End with Access

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

RE: Front End / Back End with Access

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close