View Single Post
  #1 (permalink)  
Old July 9th, 2009, 11:38 AM
Rood67 Rood67 is offline
Authorized User
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Cool VBA Passthrough Query

Hello all,

I use Access 2007 to connect to a hosted AS400 database (iSeries 5). I used to sit and wait nearly 17 minutes for the queries I had set up to create local tables to run an populate.

Then... oh, then I found the joy of Pass Through Queries. *pause for the angels to stop singing* Now, I have it so that my old 17 minute wait is cut down to just over 2 minutes, and that's with more queries than before.

I've even learned how to create and store personal views on the AS400 so that some of my Excel files can get up to date info (before I would run my pass through queries and let Excel pull from my local temp tables.

Here's my new dilemma. I am finding the need to run some of my pass through queries with parameter qualifications. Also, there are rare cases that I need to do an update, for which I took my old select pass through and edited it to update. But when the data update has to go to several tables... well, that's a pain and opt to have errors.

So, finally, here is the meat and potatoes of this post...
How, in the name of all that is Holy, do I set up a pass through query in VBA that will connect to the AS400 and run it or them with parameters?

And I suppose you need to know that I'm connecting through IBM's Client Access ODBC driver, although I'm finding more and more info on iSeries Access ODBC driver, but I don't have that yet. I have been to the connection string website to get the proper connection string to use with Client Access ODBC, but I have no idea what to do with it.

If anyone can help me, I will be eternally grateful.
Reply With Quote