Link to Large SQL Server Tables

[randy700]

My apologies if this is the wrong forum for this question, but I'm hoping someone can provide me with a solution.

I need to research records in a SQL Server database that, at present, I can only access by linking to the tables from an Access database.

The problem appears to be the size of the table. There are more than 10 million records and Access times out when I attempt to run a query, even when I provide criteria such as a range of dates (ie WHERE InvoiceDate BETWEEN now and then).

The best solution I've found is to dump the data into an access table by running a make table query and several append queries - I can get about 10 days at a time. One month totals nearly 900,000 records, so I'm thinking I'll need separate tables for additional months!

Does anyone have any idea of how I can make this process a little more efficient? At present time, I have no control over the SQL Server DB and have "read only" access to it.


Randy

 

[lespaul]

are you just running queries against the database or are you trying to create forms and reports through Access?


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[randy700]

I only need to run queries.

Randy

 

[traingamer]

If you know or can find out the indexes (indices) and use them that'll help.
Also check the CommandTimeout Property if you want to try ADO and set the CommandTimeout to 0.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

you could also download Squirrel SQL (an open source java program) that allows you to connect to a data source and run queries. It doesn't have a graphical drag and drop interface, so you have to know SQL...but it's free and it doesn't have the size limitations that you've found with access.



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

Use only pass-through queries. You can update the SQL property of these queries using a little DAO code.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

WHERE InvoiceDate BETWEEN now and then
Ask the dba to create an index on InvoiceDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Thank you, all.

I'll try some of these suggestions and re-post with what worked.


Randy

 

[JoeAtWork]

I can only access by linking to the tables from an Access database

Are you sure that connecting with ADO is not possible? Because that would be the ideal solution - you could call a stored procedure which almost certainly would be faster than a linked table.

 

[cmmrfrds]

As already suggested, use pass-thru queries. It helps to have a copy of the table with a few records so that you can generate the SQL with the QBE and then paste the SQL in as a pass-thru query.