Access table opens slowly

[BigJimSlade]

Hello, Big Jim here:

I have an Access table with 135K records in it which opened very slowly. Could take 30 seconds for the hour glass to finally pop up and then open the table. When I made a new copy of the table, records and all, this delay went away.

I'm running a process that executes SQL against the table and accesses it through DAO recordsets thousands if not millions of times in a session. So I found a solution in copying the table, but I was wondering if anyone else perhaps knew why a table would become so slow to open.

Thanks in advance,

Big Jim

 

[llkhoutx]

How was the table originally created? Try compacting the database to see if that makes a difference. I had a similar problem with an Access table upsized to SQL Server. Compactng the SQL Server database cured the problem.

I suspect that there is excess overhead created by the manner in which the table was created.

I don't now how to otherwise explain the issue.

 

[BigJimSlade]

Thanks for the response. The table was manually created in design view.

It appears that I had a recordset which I wasn't closing out properly. I was resetting the recordset over and over. I guess after doing this 10,000 times, it left some behind-the-scenes overhead which slowed down the table.

Thanks again for the help,

Big Jim