ODBC Issues with Microsoft Access 2010

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?

Who is Participating?
Kelvin SparksConnect With a Mentor Commented:
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.

Anthony6890Author Commented:
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?

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
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.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Kelvin SparksCommented:
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.

Anthony6890Author Commented:
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!

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.