Solved

ODBC Issues with Microsoft Access 2010

Posted on 2014-12-04
6
384 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 35

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 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