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 October 12th, 2005, 04:02 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Table Records

Hi
I've never discussed this with anyone, but I thought that I should look to a forum to see if i can find some help as no one at work appears to know much about Access and I've pretty much taught myself as a result.

My situation is that I have a database that imports data once a day about 2500 records. It currently holds over 700,000 records and it is now starting to slow down considerably whenever i carry out queries due to the increasing load. I have tried to nominalise it as much as I can for the time been, and I know there is some room for a little more. Anyway, my main question.

Does anyone recommend moving to SQL Server or should Access be able to continue to handle so many records (and 15 fields of data per record)?

Thanks

Tony

 
Old October 12th, 2005, 04:17 AM
Authorized User
 
Join Date: Jun 2003
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

The SQL server / Access debate will go on for ever.

There are many things to consider, like concurrent users, speed & cost all of which have to be weighed against each other, my 10 cents says go to SQL Server every time.

One major issue with Access (and any MS file as far as I know) is it will have a 2GB limit, what is the file size of you database.

Andy


 
Old October 12th, 2005, 06:38 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply.
Its almost 1gb. But is 700,000 records in a table a bit too much for Access?

 
Old October 12th, 2005, 07:01 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

There isn't a row limit on what an access table can hold. Its all about File size. Saying that, the more rows in your tables the slower the system will run. Before you even get to the 2gb file size limit, you will notice these effects (as you have already stated) if you are holding 700,000k records in a single table, especially in a networked, multi-user environment.

Without a doubt, starting planning to upsize to SQL server or some other server side RDBMS. Access is fine for recording your DVD collection but for storing larger amounts of data like yours, you will need to upsize sooner or later. Best do it sooner rather than later or you will be swearing alot in the not too distant future!!

J
 
Old October 12th, 2005, 10:38 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, I'll probably best start getting something planned. I've never used SQL server on a daily basis, but have tried to prepare myself via a couple of course so hopefully will put me in good stead.

Tony

 
Old October 13th, 2005, 02:37 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I do something similar to what you are trying to do here, I think. I can't imagine that you need all of that data at once. I do pulls on series of data to a local access mdb file, or pull from views on SQL Server.

You will find your performance can be stellar with SQL server. Just remember to package all your queries as views on the server since SQL will optimize them for you. If you write the queries in access and then pull from the server, you will basically have to pull the whole dataset and then parse it on the client side.

Anywho, I think you will have fun with this. You will find that for your purposes, SQL and Acess are very much the same. Have your DBA set up a database, and then just use DTS or the upsizer to pull over your stuff. I like DTS better, but this will require a rewrite of your front end. The upsizer will totally rewrite your front end so that no data should be stored or linked locally. Pretty cool.

Make the move!

HTH

mmcdonal
 
Old October 13th, 2005, 02:39 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Also, you can use the DTS wizard to create a saved package for your regular pulls of data instead of doing imports as you probably do now.

mmcdonal
 
Old October 14th, 2005, 02:32 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Cheers for that. I am mostly after a very good excuse to move to SQL, but also use Access at the same time as a front-end. So I'm going to have to put together a good plan and reasons for moving in that direction.

re the data imports, I need to pull in those records daily as they are all truelly unique and need to be monitored for statistical aswell as other business reasons.

Thanks again

Tony






Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert table records into another table. hewstone999 Access VBA 2 March 5th, 2008 11:01 AM
can't display records from Access Table. jenjenyan Classic ASP Databases 1 April 16th, 2004 02:49 AM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM
Append Records From One Table to Another Table twsinc Access VBA 4 February 29th, 2004 03:04 PM
thousands records enter one table to another table mateenmohd SQL Server 2000 3 July 17th, 2003 07:52 AM





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