We have a "system" which is very large and unstable. I think this is due to the sheer size of what we are trying to do in Access 97. Would very much appreciate feedback/thoughts on what I should do. This is my situation:
We extract about 900MB of data from an AS400 and 50MB of data from a SQL Server db, all of which goes into Access dbs (1 db for each source). From the AS400 we get 8 tables each with anywhere up to 255 columns and the following (approx) number of records:
350,000
178,000
37,000
429,000
432,000
432,000
429,000
17,000
We do loads and loads of processing on this data and end up with several tables of data (all stored in seperate dbs because of their size).
One of our result tables has 3.9 million records in it and the db is almost 1 GB - sometimes is maxes out.
We have so many dbs because each result db can only store 1 or 2 tables because of the number of records.
There are also several processesing dbs a lot of which link to the results dbs.
We have an overnight process which downloads new information from the AS400 and the SQL Server dbs and processes it in various ways.
This is done by having a batch file open each processing database in order.
Each processing database has an autoexec which determines if it has been given opened from command line, and if so runs the overnight process.
It sometimes falls over because of size issues and sometimes because a complex query is trying to run and we get the error message "Not enough space on temporary disk".
I think that we need to move to SQL or some other db because this project is simply too large for Access. Can you please give me your thoughts.
Thanks loads
Jo
We extract about 900MB of data from an AS400 and 50MB of data from a SQL Server db, all of which goes into Access dbs (1 db for each source). From the AS400 we get 8 tables each with anywhere up to 255 columns and the following (approx) number of records:
350,000
178,000
37,000
429,000
432,000
432,000
429,000
17,000
We do loads and loads of processing on this data and end up with several tables of data (all stored in seperate dbs because of their size).
One of our result tables has 3.9 million records in it and the db is almost 1 GB - sometimes is maxes out.
We have so many dbs because each result db can only store 1 or 2 tables because of the number of records.
There are also several processesing dbs a lot of which link to the results dbs.
We have an overnight process which downloads new information from the AS400 and the SQL Server dbs and processes it in various ways.
This is done by having a batch file open each processing database in order.
Each processing database has an autoexec which determines if it has been given opened from command line, and if so runs the overnight process.
It sometimes falls over because of size issues and sometimes because a complex query is trying to run and we get the error message "Not enough space on temporary disk".
I think that we need to move to SQL or some other db because this project is simply too large for Access. Can you please give me your thoughts.
Thanks loads
Jo