How to run query with huge table

[adnancanada]

Hi ,


I have table which has almost 1 billion records. Whenever I make any query on Access and run it. It takes forever. It freez my computer. Is there any way to run query. I have link table coming from SQL Server.

Kindly help.

[ssanfu]

As I understand it, running a query in Access on a linked table requires Access to transfer the entire table to the local computer - 1 billion records. No wonder your computer freezes.
That means you must have 2 - 3 times the table size of free space on the hard drive and have the RAM maxed out.


I would suggest trying a saved query on SQL server or an Access pass through query. Both will execute the query on SQL server - which SQL server is far better suited for running such a large data set.


I am headed that way but so far don't have a lot of experience in large data sets. I will be moving to SQL Server Express, then to SQL Server. But after nearly 4 years my largest Access table only has Approx 2.3 million records.

Good luck .......