Solved

ODBC Issues with Microsoft Access 2010

Posted on 2014-12-04
6
354 Views
Last Modified: 2014-12-04
Hi All,

I've been having a constant issue with multiple Access programs that I've created.  Recently we've moved our iSeries offsite about 1,000 miles away.  Before the move, I didn't have any issue running update or append queries in Access to our iSeries system.  After the move, I've had severe lag issues where what used to take at most a minute, now takes 30+ minutes.  I had a break through today on my issue.  I also use Toad Data Point and I attempted to do a simple Update query to one column using an ODBC connection through Toad and it took under 8 seconds to change 190,000 rows.  When I ran the exact same query through Access it takes over 20 minutes to complete (with the program going into a non responsive state as well).  I noticed that the SQL coding in Access is slightly different than that of Toad.  

Does anyone know why there would be such a performance issue or has anyone encountered this before?

-Anthony
0
Comment
Question by:Anthony6890
6 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 350 total points
ID: 40481676
I suspect that TOAD requires the execution on the server, whereas Access is doing the same locally. Have you tried using "Pass through queries" for your updates? These require you to use the syntax of your server, and not Access sql.

A pass through query is much like a normal query (but will need a connection string, and you cannot use the query grid), but asks the server to execute it, rather than moving the data back and forth.


Kelvin
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40481684
Thanks for the response Kelvin.  I haven't tried a pass through query.  If I do a pass through query, will I be able to update a field on the remote iSeries from a local table sitting in Access?

-Anthony
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 total points
ID: 40481691
Jet/ACE won't pass a query through to the back end unless it's a pass-through as Kelvin said, or it can translate the statement into SQL the back end will understand.

 That means no VBA expressions, JET/ACE specific SQL, or joins to local tables.   If you use any of those or JET/ACE can't figure it out, it will execute client side and pull all the BE data over the wire, work with it, then send back what it needs.  That conceivably means that it will execute one query to the BE for each row of your query.

 To be really sure it's getting passed to the back end, make sure it's pass-through, which means the SQL must be in the dialect the BE understands.

Jim.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40481706
You cannot join a local table to a remote table in a Pass through Query.

Through VBA you could extract "Values" from local tables, assemble the Pass through query sql in code, and use that to open and run the pass through query.



Kelvin
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40481715
Ok, I work with that that.  I just tried the Pass Through query and it worked!!!

Kelvin thank you very much for your response.

Jim thank you as well for the input.  I truly appreciate it!

-Anthony
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40481717
The reason that bulk updates can sometimes take an excessive amount of time when run as Access querydefs is because Access is very nice to you and wraps the update inside a transaction and gives you the option to back it out at the end.  To give you this option takes an enormous amount of resources particularly for such a large table.

I use linked tables and bound forms for all my SQL Server apps and as long as I am careful to avoid certain things that Jim mentioned, all is well.  Performance is fine.   However, bulk updates are quite a different thing.  Those, I would always do as a pass-through query.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now