Should I be using SQL or some other db?

[jojones]

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

 

[chrissie1]

We use SQL-server 2000 with access 2002 projects all the benefits from access with the power of sql-server. You dont have any problem in size because the tables are sql-server and only the forms and code are in the project. Works fine and fast because the queries are don by the sql-server and the code is done on the computer with the adp project file "What a wonderfull world" - Louis armstrong

 

[allanon]

In that situation would it not be better to go to SQL Server and have the processing done as overnite DTS packages? (Stored Procedures) Then you would have to size and power of SQL Server for both storage and processing. If re-writing is an issue then at least moving the data to SQL Server is a much better way to do things. We use SQL 7.0 and access 97. Works good. The DB here is over 10 GB and growing daily with no ill effects. We have several tables that have over 2 million records and still no problems.