Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 4th, 2008, 09:01 AM
Authorized User
 
Join Date: Mar 2007
Location: Sydney, NSW, Australia.
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

Cheers
Darren

Reply With Quote
  #2 (permalink)  
Old February 6th, 2008, 09:59 AM
Authorized User
 
Join Date: May 2006
Location: , , India.
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

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.

Ashfaque

Reply With Quote
  #3 (permalink)  
Old February 8th, 2008, 12:28 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
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
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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 chrisangus@btinternet.com Beginning VB 6 2 June 23rd, 2003 12:28 PM



All times are GMT -4. The time now is 05:40 PM.


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