473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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.
Apr 20 '11 #1
3 1740
dsatino
393 256MB
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.
Apr 20 '11 #2
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....
Apr 26 '11 #3
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!
Apr 26 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Ian | last post by:
We are currently experiencing problems with our Access XP database. We are using access as the front-end and are connecting to Oracle 7.3 via ODBC. Our users are running Win 98 and Win Xp. We are...
1
by: James Bird | last post by:
Hello In order to perform a hetrogeneous join (I think that's what they're called) between MySQL and another database, I've created an Access database containing linked tables from each...
0
by: Beau Peep | last post by:
Yo Guys, Need hlp in my probs. Am tryin to insert the pics/movies(clips any formats) in the sql2000 table. Have a table with a field Image. Me love to see any code workin of these. An get...
1
by: asenthil | last post by:
Hai, Can any one tell me, How to retrive rows in alphabetcal order in the mysql database by using select query? thanks.. senthil
1
by: permanentlybaffled | last post by:
Hi, This is my first time using a forum, so here goes... I have an Access app that is in use by several people who know nothing about PC's. I included a few extra columns in each table to...
3
by: Aric Green | last post by:
I have a form that contains customer information. I want to use the ID_NUMBER to query another table (when I click a button) to return the record that matches that ID_NUMBER. What is the easiest...
30
by: iheartvba | last post by:
Hi, I already have 3 Databases running: A. they all have the same tables and the same structure B. There is no 1 Master table they are all separate tables What I want to do is to merge them...
0
by: stranyarra | last post by:
Hi I have an issue re primary key upon linking MS access 2000 to Oracle 10g. I'm going to migrate to a new Oracle server. So I have to re-link the tables to the new server. I have do the...
9
by: SusanK4305 | last post by:
I know I have asked this once before but no one answered. I have 2 tables Action Type and Reason. I took those 2 and created 1 Query1 a.k.a Action Type vs Reason. Then created a Query so that the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.