Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 4th, 2008, 09:01 AM
Authorized User
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default To Split or Not to Split

Hi Everyone,

I have a database currently which is split, however since the split performance has dropped dramatically!!! now the database is in a multi-user enviroment with up to about 20-30 concurrent users at any given time. The data database size is around the 10mb, with the front end around 3-6mb. I know the general rule is split a database in a multi-user enviroment, but do you guys think a database of this size is worth the performance lag.

General Info =
highest record count in single table = approx 9000
Table count = approx 15
queries are stored in the front end only where most of the forms get their data.
transaction based database, i.e users create transactions
vba code runs all bar one of the forms
life cycle of database 12-18 months
estimate database size at end of life = 600mb - 800mb

Any of your thoughts and experience would be great


Old February 6th, 2008, 09:59 AM
Authorized User
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque

I was running my Access db in my company for last 6-7 years. One of its table holds more than 140,000 records. Db contains lots of forms and queries with many reports and modules was successfully running.

Two of our users are about 70 mtr away from main server and were still receiving data at normal speed.

Only in mid of 2007 we started using Delphi but until that movement there was no problem at all to my Access db. The db size grown up to 14 MB and I still found no problem with it. I believe if back end is SQL Server and developer uses ADO method, there will be no problem at all.


Old February 8th, 2008, 12:28 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

The speed issue may not be a problem with the split itself, per se. It could be that your network is having issues with the data tranfer from from the network to the users' C: drive.

Also, take a look at your VBA programming and your queries. If you have intensive querying out to the network, or if your VBA functions consistently tap into the tables "out there," that could slowing you down.

Another thing in your programming and queries that can slowing you down is how efficient they are. For example, the functions DLOOKUP, DCOUNT, DMIN, DMAX, etc. may be easier to use than creating recordset objects in VBA and using them, but they are much less efficient. The same operation using DLOOKUP vs. using a recordset object is usually much slower in the former than the latter.

I had that issue, and what I did was avoid using DLOOKUP if I could help it. Also, I had a start-up routine in the front end's MDB make local copies of data tables so that when they queried things, it went MUCH faster.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Similar Threads
Thread Thread Starter Forum Replies Last Post
split() darkhalf Javascript 1 October 21st, 2005 11:34 AM
Split crmpicco VB How-To 6 May 17th, 2005 04:16 AM
split crmpicco Classic ASP Basics 2 February 14th, 2005 08:48 AM
Split database rylemer Access 1 August 30th, 2004 05:33 PM
help using split command [email protected] Beginning VB 6 2 June 23rd, 2003 12:28 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.