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.
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.