Trouble using select query with linked table using ODBC to link.

[Peggy Frazier]

I am using Microsoft Access 2010 and attempting to use a query to pull select data from a linked database. The link is via ODBC.

My connection with the database is fine, I have successfully pulled data from smaller tables. The table I am querying contains approx 50k records. When I write a query to pull all records, Access crashes.

I have attempted to use criteria to pull smaller chunks of data (i.e. by date) but when I run the query it is still trying to pull all data from the table.

I have done searches on using criteria for queries and am fairly confident that I have done it correctly. In the date field I have tried both " Between #date# and #date# " and also Between [Start Date] and [End Date]. Neither limited the data that the query pulled.

I am going a bit crazy here and any assistance would be greatly appreciated.

[dsatino]

Just as a guess:

It sounds less like a crash and more like impatience. While 50K records is not a lot from a database standpoint, you need to understand how your data is being returned. Although you have an ODBC connection, you are writing your query in Access and therefore the query is being processed by Access. What this means is that Access is basically dragging all the records across the network and applying the SELECT logic after the records are available to your PC. It's a bit more complicated than that, but that's the basic gist of it.

A more efficient method would be to use a Pass-through query. With a pass through, Access sends your SQL to the ODBC connected database and the logic is processed there. You'll need to write the correct SQL syntax for the server type, but you can google just about anything along those lines if you're not sure. Or you could just post the SQL with server type and someone here will almost certainly convert it for you.

[Peggy Frazier]

Thanks for responding! I could see that Access was trying to bring all the data in locally when I tried a smaller table.

I looked up information on pass through queries but could not figure how to write the query. If I want to pull all fields from the table, do I have to list them individually or is there a way to write the query to pull all?

The database is Quikbooks Point of Sale and I do not know the type....

[Peggy Frazier]

I really need to get this going as quickly as possible. Does anyone know of tutorials on writing SQL strings? Or can you provide a resource (s) that I can work with to get the SQL written? Many thanks!