Access Database - 450 MB, 700000 records - Time to upgrade? To what? 2

[tweeds]

I work for a small company that runs an Access Database, 450 MB, 700,000 records and growing by 25,000 records per month. We have a P133 server and 3 users run queries at various times. Queries are taking 5-10 minutes to run. We are upgrading our server to a PII 450. Do I need to start looking at a more powerful database? How many records can Access handle? Is my next step Visual Basic, SQL server, Oracle, etc.? Any guidance is greatly appreciated!

 

[zazz]

I would suggest SQL server. Visual Basic is a great language to use with databases. WROX has a great book called VB6 database that has helped me in many times of need.<br>
<br>

 

[bsilvert]

Which way to go depends. Do they need a more powerful database for other apps, or just this one? If this is the only app, you could handle 2x your size database with faster machines and some VBA coding. I have 1.5 million record database that I can get results on in a respectable time.

 

[jefferytmc]

It definitely depends. Access can handle up to a 1 GB database. If the database goes over that then Access will not allow more data to be added. Based on the data you gave your records are approximately 675 bytes long. Based on this information combined with the 25K per month, technically you would take another 35 months to get to 1GB, if you did not change the record length or add anymore data or increase the rate that you are adding records. Also, the query time will continue to degrade.<br>
<br>
Depending on what you needs are, there are several solutions. Foxpro is better with larger recordsets than is Access. The new Visual Foxpro 5/6 is very fast. However, If you are doing some long term planning, I would probably research the SQL Server idea. It probably gives you the most scalability (short of going to a mid-range system).<br>
<br>
Jeffery

 

[bookor]

It would probably pay to leverage your access/vb knowledge by upgrading to sql server. Like the post above points out you're going to run out of room in a couple years unless theres a possibility of purging old records to trim it down. You can build your front end in visual basic for an immediate performance boost and then replace the back end of the vb front end with sql server when the time comes. There's not many alternatives. A switch to Oracle for example would be extremely expensive and a totally new environment for you. <br>
<br>
<br>
<br>

 

[MapMan]

One thing to consider when moving from Access to SQL. The queries that are built in Access won't necessarily work in SQL. Nor will the modules. Start learning SQL now, before you have to.<br>
<br>
From my recent experience it's not a short trip. So pack a lunch. If you know what I mean.<br>
<br>
HTH,<br>
<br>
MapMan

 

[Darran]

We've been looking into upgrading to SQL server 7 as well, according to Microsoft the new Access 2000 can be used to maintain an SQL7 database. If this is true then you can use the upsize wizard for Access to convert to SQL then look into Access2000 to maintain the database.<br>
<br>
This is the theory anyway, if I'm wrong then please let me know.

 

[dblake]

We recently had to make the jump to SQL, since Access was giving us strange locking errros when doing update queries on a table that had only 32 000 records (but 20 users!).<br>
<br>
MapMan's advice refers, since it took me about 6 months of idle fooling around with SQL to get the hang of it all. Access 97 and especially Access 2000 is very well suited to integrate with SQL Server. The upsizing wizard is great, and you'll find Transact SQL, procedures and views what you've been looking for long ago!