Access - when will it run out of steam?

[Zukkster]

I'm currently designing a database that has litterally millions of records.

I'm using an Access front end as the query tool and linking it to SequelServer and Paradox tables. I only need to query small sections of the database at any one time, so I'm using make-table queries to select the slices of data required, before running multiple steps to create reports from these - if I use the whole table then query times to linked tables seem extremely slow.

At the moment some of the actions (based on 5-6 queries) take 15 - 20 minutes to complete and lock up the users PC.

In 6 months the database could start to use tables with 10's of millions of records.

I'm concerned that it will just stop working and give up the ghost. I'm considering putting the database on a server and giving users a VNC link so that queries will run on the server, but the downside to this is only one user at a time, so Citrix might be a better alternative.

However I'm starting to think that I might be asking too much of Access.

I'm not a developer or in IT, I'm a data analyst with a need and an interest in finding a solution ... but I'm now into unfamiliar territory and don't want to create something that can't handle the volumes I'm expecting of it. Any suggestions would be appreciated.

 

[cheerio]

The selection process needs to be carried out by the Server database and not by Access - otherwise Access could be pulling millions of records across the network to find the ones it wants.

You can make the selection operate on the server in one of two ways. The first is to create a view on the server. In reality this is a SQL query but to Access it looks like a table for most purposes.

The second route is to create Access pass-through queries. These are SQL queries using the server's dialect of SQL that Access stores and submits to the server. Note pass-through queries have some limitations - for example you cannot bind a form to them.

Finally, you need to ensure that your tables have the right indexes. Queries will run much faster if the index can be scanned to find the relevant records rather than actually scanning the records themselves.

It does not matter what front end you use - if the balance of work between the front end and back end is wrong the performance will be poor.

Ken

 

[MattReed]

You must remember too that Access has file size and table size limits, which I believe are both 2 GB. Watch the size of your file. If you start to get close to 2 GB, I would suggest using a more robust DBMS like MySQL or MS SQL Server.

 

[Hap007]

I think Ken and Matt both gave you sound advice.

It sounds like you all ready are using SQL Server, but that your SQL Server is located on the same PC as is the Access Frontend PC program.

I would suggest getting a dedicated standalone Server and put your SQL Server database on that system. This should be a Server class system running a server operating system with lot's of memory.

Then, I would load the Paradox table into SQL Server tables so everything (data wise) is in the SQL Server db.

Finally, use Pass Thru Queries and Views.

Doing this should provide enough horse power to continue using your setup.

Good Luck,
Hap




Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[MichaelRed]

also look into using stored procedures on the server side. GENERALLY, these will execute faster than pass through queries.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places