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
Solved

ODBC Issues with Microsoft Access 2010

Posted on 2014-12-04
6
394 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 36

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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