?
Solved

ODBC Issues with Microsoft Access 2010

Posted on 2014-12-04
6
Medium Priority
?
414 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1400 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 600 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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 38

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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