Help with 35mil records sql to odbc= access not working

[sasha101]

Im using sql to hold data. I ODBC it to access where i have a update query everything seems right but its taking for every to run in fact i never have seen it finish. It was timing out but i changed the time out rate to 0 so i get no more (odbc--Call failed). I have the same query built on a slower machine but it runs fine.
The only thing better on the other machine is that a scsi hard drive . But the processer is slower and it has less memorie. Can some one give me advice on how to tackle 35 million recods!!!

 

[MichaelRed]

I know you don't want to here this, but the general opinion would have to be DONT.

MS Access is not known for the capability to handle large numbers of records. I have set up processes for up to approx 1 Million records. these "work" - but they are 'painfully' slow (as in taake a long lunch when running the process). I think the majority opinion will be to use an 'industrial strength' database engine for this.

If, when you say " ... sql to hold data" you are refering to SQL Server, you can place 'stored procedures' in SQL Server and just use access to send the criteria to SQL Server to actually do the updates.

For 'light weight' reporting, Ms. Access would be O.K.. For large scale updating, it will exhibit the slowness you are describing.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over