p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   VBA Passthrough Query (http://p2p.wrox.com/showthread.php?t=75177)

Rood67 July 9th, 2009 11:38 AM

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.

mmcdonal July 14th, 2009 08:37 AM

You need to post more information than this.

An example of a SELECT query would be something like:


Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
sSQL = "SELECT * FROM MyTable"
Set cn = New ADODB.Connection
cn.open "DSN=MyDSNName" 'from your ODBC setup
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
'do something iterative with the data...?



sSQL = "INSERT INTO MyTable(Username, LastName, FirstName) VALUES('mmcdonal', 'Mike', 'McDonald')"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic 'this line does the insert

Then if you want to use variables...


Dim sUsername As String
sUsername = Me.Username 'referring to a form control
sSQL = "SELECT * FROM MyTable WHERE Username = '" & sUsername & "'"
rs,Open sSQL, cn, ...

Did any of that help?

Rood67 July 14th, 2009 12:09 PM

Hi Mac,
Great to read your reply, I was getting worried no one could help me. I apologize for not inserting code in the first post, and that had to be a deterrent to others posting I'm sure.

I'm going to include links to some pictures as well as post some code of what I have now. Maybe from that you can further assist in getting me started in the right direction. Actually, you have gotten me started, but my dilemma now is that I feel just enough dangerous to blow something up without knowing how to stop it or fix it if things get away from me. Well, I can always end process here locally, and log in green screen on the AS400 and kill the active job, so let me get to it and see if you can finalize my tutelage in converting these mass amount of queries to a more simplified VBA so I can make changes and updates easier.

Case in point for making life easier... our AS400 host got me the latest iSeries ODBC, and it took me nearly an hour to open each pass through query and update the connection string. If I had this in VBA with a global variable set up for the connection string, it would have been one quick edit and all the queries would have been good to go.

Screen shot of my cluster-hump of queries:

Now, taking the first set of queries that works with the CUPMMOF table, I have the following sequence of events.
Clicking the check box to create the table runs the following from DoCmd in VBA.
  • 01a-qry_MakeCUPMMOF_d (deletes old data from local table)
  • 01a_qry_MakeCUPMMOF_pt (this is the pass through that runs on the AS400 to create a subset of data)
  • 01a-qry_MakeCUPMMOF (this is an append query that reads the pass through and inserts the subset into my local table)
Heres the info for the delete query:
which has the Access2007 generated code of

DELETE tbl_cupmmof.ItemCode
    , tbl_cupmmof.Format
    , tbl_cupmmof.Title
    , tbl_cupmmof.Y
    , tbl_cupmmof.StreetDate
    , tbl_cupmmof.Form
    , tbl_cupmmof.Box
    , tbl_cupmmof.Genre
    , tbl_cupmmof.Studio
    , tbl_cupmmof.Rating
    , tbl_cupmmof.Rank
    , tbl_cupmmof.Theme
    , tbl_cupmmof.QtyOnOrder
    , tbl_cupmmof.Type
FROM tbl_cupmmof;

Here is the pass through that I wrote:

    , trim(MOFMNM) as MOFMNM
    , trim(MOFTLE) as MOFTLE
    , MOFSTD
    , trim(MOFFRM) as MOFFRM
    , trim(MOFBOX) as MOFBOX
    , trim(MOFGNR) as MOFGNR
    , trim(MOFSDO) as MOFSDO
    , trim(MOFRTG) as MOFRTG
    , trim(MOFRNK) as MOFRNK
    , trim(MOFTHM) as MOFTHM
WHERE MOFSTD > '12/31/2007'

Heres the info for the append query:
which has the Access2007 generated code of

INSERT INTO tbl_cupmmof ( ItemCode, Format, Title, Y, StreetDate, Form, Box, Genre, Studio, Rating, Rank, Theme, QtyOnOrder, Type )
SELECT [01a_qry_MakeCUPMMOF_pt].MOFICD AS ItemCode
    , [01a_qry_MakeCUPMMOF_pt].MOFMNM AS Format
    , [01a_qry_MakeCUPMMOF_pt].MOFTLE AS Title
    , Right([MOFFRM],2) AS Y
    , [01a_qry_MakeCUPMMOF_pt].MOFSTD AS StreetDate
    , [01a_qry_MakeCUPMMOF_pt].MOFFRM AS Form
    , [01a_qry_MakeCUPMMOF_pt].MOFBOX AS Box
    , [01a_qry_MakeCUPMMOF_pt].MOFGNR AS Genre
    , [01a_qry_MakeCUPMMOF_pt].MOFSDO AS Studio
    , [01a_qry_MakeCUPMMOF_pt].MOFRTG AS Rating
    , [01a_qry_MakeCUPMMOF_pt].MOFRNK AS Rank
    , [01a_qry_MakeCUPMMOF_pt].MOFTHM AS Theme
    , [01a_qry_MakeCUPMMOF_pt].MOFQOO AS QtyOnOrder
    , [01a_qry_MakeCUPMMOF_pt].MOFTYP AS Type
FROM 01a_qry_MakeCUPMMOF_pt;

Now, this is the simple one, so you can look at it and help me get started converting it to a pure VBA database connection - recordset processing procedure.

The fun ones come in when they call functions that I've written to concatenate the date (some of the AS400 tables have each date piece in its own field because they have indexes and views that are based on pulling info soley by year, etc...)

Here is the code from the Sales Transaction pass through, append, and function.

    , INVNO
    , ORDN
    , ORDLN
    , ORDMM
    , ORDDD
    , ORDYY
    , INVMM
    , INVDD
    , INVYY
    , SHPMM
    , SHPDD
    , SHPYY
    , CMOR
    , trim(IMCD) as IMCD
    , QTY
    , COST
    , CHENB
    , SLS#
    , CRORT
    , IMDEPT
    , IMALL
    , TYPTRN
    , SAACPR
    , PRICE


INSERT INTO tbl_sapitrn ( AcctNr, InvNr, OrderNr, OrderLnNr, Ordered, Invoiced, Shipped, CorDorO, ItemCode, OrdQty, Cost, zPrice, Total, EnteredBy, Salesman, Type, ItemDeptNr, Allocated, TranType, AcctPeriod )
    , [01c-qry_MakeSAPITRN_pt].INVNO AS InvNr
    , [01c-qry_MakeSAPITRN_pt].ORDN AS OrderNr
    , [01c-qry_MakeSAPITRN_pt].ORDLN AS OrderLnNr
    , DateConcat([ORDMM],[ORDDD],[ORDYY]) AS Ordered
    , DateConcat([INVMM],[INVDD],[INVYY]) AS Invoiced
    , DateConcat([SHPMM],[SHPDD],[SHPYY]) AS Shipped
    , [01c-qry_MakeSAPITRN_pt].CMOR AS CorDorO
    , RTrim([IMCD]) AS ItemCode
    , [01c-qry_MakeSAPITRN_pt].QTY AS OrdQty
    , [01c-qry_MakeSAPITRN_pt].COST AS Cost
    , IIf([CorDorO]="C",[PRICE]*-1,[PRICE]) AS zPrice
    , [OrdQty]*[zPrice] AS Total
    , [01c-qry_MakeSAPITRN_pt].CHENB AS EnteredBy
    , [01c-qry_MakeSAPITRN_pt].[SLS#] AS Salesman
    , [01c-qry_MakeSAPITRN_pt].CRORT AS Type
    , [01c-qry_MakeSAPITRN_pt].IMDEPT AS ItemDeptNr
    , Trim([IMALL]) AS Allocated
    , [01c-qry_MakeSAPITRN_pt].TYPTRN AS TranType
    , [01c-qry_MakeSAPITRN_pt].SAACPR AS AcctPeriod
FROM [01c-qry_MakeSAPITRN_pt];


Function DateConcat(MM As String, DD As String, YY As String) As Date

    Dim CD_01 As String
    If MM = "0" Then
        MM = "12"
        DD = "31"
        YY = "2099"
    End If
    If Len(RTrim(MM)) = 1 Then
        MM = "0" & MM
    End If
    If Len(RTrim(DD)) = 1 Then
        DD = "0" & DD
    End If
    If Len(RTrim(YY)) = 1 Then
        YY = "200" & YY
    End If
    If Len(RTrim(YY)) = 2 Then
        YY = "20" & YY
    End If
    CD_01 = MM & "/" & DD & "/" & YY
    DateConcat = CDate(CD_01)

End Function


Now, what all of this desire to learn is geared toward, is instead of having to go in and edit individual queries that select subsets based on a date or maybe based on a particular item code, I could use my form with some modifications to pass parameters to the queries and make them truly dynamic instead of a complete pain to work with. Several hours of changes and modifications once vs the way it is now, I'll opt for the one time and keep some BC powerds handy.

I know it's very long, but hopefully this will be what you need. If not, let me know.

Thank you,

mmcdonal July 14th, 2009 12:43 PM

What are you doing with the recordsets once you get them?

To make the queries dynamic, you have to add the controls for your users to select parameter values.

For example, If I wanted a user to select a customer ID, I would create a combo box that was populated with valid customer ID's with some meaningful data like customer name displayed. Then when they clicked the button to run the report, if Customer ID was required, the code would be:


Dim lCustID As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
cString = conn() 'cString is a public variable, and conn() is a function that has my global connection information conn = "DSN=MyDsnName" for example.
If IsNull(Me.cboCustSelect) or Me.cboCustSelect = 0 Then
  MsgBox "Please select a Customer", vbInformation
  Exit Sub
  lCustID = Me.cboCustSelect
End If
sSQL = "SELECT * FROM tblCustomer WHERE CustomerID = " & lCustID
Set rs...

The only difference in your requirements is what type of query you are running determines how you process the resulting recordset.

If you are doing a select query, where do you want the data to go (form, report, table?)

If you are doing an insert, just opening the recordset does the insert. Do you want to check to make sure it was successful? The same with an Update or Delete query (I never allow my users to do those.)

Also, I would not process data while I was running the ADO connection, as with your date correction. I would bring my data over, and then apply the fix on the local recordset, or in the actual report Details section On Format event. Although THAT can take some time geven the amount of data. I would not call that function from the report, but put it in the report perhaps, or do a local Update query.

I would change the delete query to this(DELETE * FROM tbl_cupmmof), store it locally and then run this code:


DoCmd.SetWarnings False
DoCmd.OpenQuery "01a-qry_MakeCUPMMOF_d"
DoCmd.SetWarnings True

I am not sure what you are doing with the select query.

The Append query you show is an Insert query. You can take variables from a form for that and build the SQL string in your code from those variables as needed.

Can you post a single immediate issue that we can resolve? Then we can move on to the next...

Rood67 July 14th, 2009 04:19 PM

Thanks again for the quick reply Mac.

Here's the process that I do now.
  • Use the form (refer to first screen shot of first reply) to run the queries you see on the left side of the picture.
  • The order of operation on those is to run the delete query to empty my local Access tables.
  • Run the insert query to repopulate the local tables (the insert gets its data from my current pass through queries that run against the AS400).
  • There's one set of queries for each table, a delete, a pass through, and an insert.
The VBA code behind the form runs these inside a Sub() that does turn off warnings before each query set is run, and turns warnings back on after the run is done. I have it set that way so that I can chose to update all the local tables or just pick and chose, and always ensure the warnings get toggled off then back on.

Now, more to your question. What I want to do with the recordsets is the process above but by getting away from have 6 dozen queries to do it.

In my mind, I foresee that I can set up a Sub() or each table, and when I pull the recordset, I will have much greater flexibility to process the returned data through other Sub() or Function() and insert the adjusted data into my local tables.

That would let me get away from calling Function() inside my insert queries. And oh the headache of trying to make parameter changes, and having to open one to maybe a half a dozen queries to make those changes. And the ones that have IIF() statements in them... using VBA code on the recordset would make life so much simpler.

I want to layout a form, have some text boxes for typed input, some combo / list boxes, some radio buttons, and some check boxes to facilitate on the fly parameter changes, and have locally stored data that matches my criteria.


All of your code samples for the ADO are helping my limited knowledge expand greatly. You may have it over simplified compared to what I have looked up online for creating a recordset, but your's seems to make more sense.

My main question here for this round of post is - how do I code the setup to start a VBA pass through query and how to get started with a recordset.

I know some may look at this and go, "oh, that's so simple" and I hope that by next week, I can have that response. But today, I want to go ram my head into the grill of an oncoming semi-truck, because I can't figure out what it is that I don't understand, which is keeping me from asking the proper questions, which is a vicious cycle.

If there was a good book that dealt with this in detail instead of just glancing over it and heading back off to show how to make a Macro I'd get it. But I've looked at all kinds of books from more authors and publishers than I can recall. But none of them really seem to delve into connecting to external databases and functional use of recordsets.

But again, your code snipets are helping me grasp what I need to know so I can ask you a more direct set of questions on...
1. How does that actually work?
2. What do I need to put here to get this to stop erroring out?
3. In your snipet, you showed blah blah, can you give an actual expample of that in detail?

If I knew enough to get set up to play with it, I'd take off and run with it and get it working. By the Grace of God, I have been blessed with rapid learning once someone gets me started. Right now, the tracks closed and the starter blocks are in a vault.

I truly appreciate your help,

mmcdonal July 15th, 2009 08:28 AM

It's still a little vague: "how do I code the setup to start a VBA pass through query and how to get started with a recordset."

It sounds like you want to get data from the AS400, then process it locally and create reports from the local tables. Is that correct?

If I wanted to retrieve the Employees table from a SQL Northwind database, and populate a local Employee table in an Access Northwind database (a copy since there are PKs in the real table), and I was using a DSN called "SQL Northwind", but I only wanted employees from the UK based on a combo box on my form called cboCountry, that was not required, I would do this:


Dim sSQL As String
Dim sSQL_Local As String
Dim rs As ADODB.Recordset
Dim rs_Local As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sCriteria As String
'Take parameter
If IsNull(Me.cboCountry) or Me.cboCountry = "" Then 'Country is text field
  sCriteria = ""
  sCriteria = " WHERE [Country] = '" & Me.cboCountry & "'" 'assume UK was selected
End If
sSQL = "SELECT * FROM Employees" & sCriteria
'Clear out old data
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_Employees"
DoCmd.SetWarnings True
'Open connection to SQL Server Northwind
Set cn = New ADODB.Connection
cn.Open "DSN=SQL Northwind"
'Open recordset on Employees table in SQL Northwind
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
sSQL_Local = "SELECT * FROM Local_Employees"
'Open recordset on Access Northwind table (local connection)
Set rs_Local = New ADODB.Recordset
rs_Local.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Check for data and then transfer from SQL to Access Employees table
If rs.EOF = False Or rs.BOF = False Then
  Do Until rs.EOF
      rs_Local("LastName") = rs("LastName")
      rs_Local("FirstName") = rs("FirstName")
      rs_Local("Title") = rs("Title")
End If
DoCmd.OpenReport "rptMyCountryReport"...

This assumes that I don't need the EmployeeID.
Is that the sort of thing you want to do?

Rood67 July 15th, 2009 05:16 PM

Hi Mac,

Yes, that is exactly what I want to do. THANK YOU very much.

I want to open a "read" connection to the AS400, a "write" connection to local Access table. As I step through the AS400 recordset, i can perform calculations, my custom concatenation to join date parts back together, etc...

And the other big, BIG thing, is to write back to the AS400 small changes to text fields. Case in point, if we add a movie title mid way through the order taking period, we denote that by putting the title into the system, but the product name has ZZZ added so the proprietary order program will sort it to the end of the weeks products.

But when we get ready to ship, so the customer doesn't see something like ZZZ Haunting In Connecticut or ZZZ Knowing, I have to go in and manually edit the title to remove the ZZZ.

What I have been hoping for is a way to pull up titles with the ZZZ in the name, and programatically change the name field in the three different tables it resides in.

Another problem that this will eliminate is strictly for my OCD... when I manually edit the name field, the proprietary software defaults the name field to all caps, so ZZZ Knowing becomes KNOWING. By updating using 3 different pass through queries in Access, I've gotten around this. But it's still editing 3 different queries. That's 3 chances to make a typo. If I pick it from a list of recordset results, and write back right([Name],len([Name])-4), then there's no chance for a screw up.

You truly hit the proverbial nail on the head with this last coding example. Now to see if I can get some simple select queries written to start testing and building my way up to the read in non-local table / write out local table, and then try to write back to the AS400.

My only remaining problem, concern, question is this... is there a way to signify in VBA code that my queries to the AS400 are pass through type? Because as I've stated before... normal select queries to the AS400 from Access are SLLLOOOOWWWWW. I am pretty confident this is because Access is reading in the entire AS400 table into local memory, then culling down the displayed result set based on the query criteria.

But the pass through, as defined by selecting the Pass Through option in query builder window, and supplying the DSN that point to the AS400 is tens to hundreds times faster, again, I am pretty confident in the reason why being the nature of the beast it runs the query on the AS400, and only passes back the filtered result set.

So, how in VBA code to I specify that my query is a pass through and not just a select query? Or am I just missing the simple point that by doing the query as a result set it is a pass through by default because the connection is established with a DSN?

Thanks again for all the info and help,

Rood67 July 15th, 2009 05:40 PM

Hey Mac,

Trying to be proactive in my learning, I was searching for more and more info and examples. One forum had a post that the person said this VB6 book on ADO would work for VBA (their close enough I suppose). What would you say about this book while there's still some copies available to get?



mmcdonal July 16th, 2009 07:54 AM

I would not do any data corrections until I had the data in the local table. I would not concatenate the date fields, for example, until I got the table locally. So pull the data, then run your concatenation on the local table. This should speed things up.

You can do a Select query on the AS400 table like: "SELECT * FROM MoviesTable WHERE Title Like '%ZZZ%'" This will pull up all those records with ZZZ somewhere in the title. But if you know you only want movies whose title starts with ZZZ, then use 'ZZZ&'. Then just do this:


Dim sTitle As String
  sTitle = rs("Title")
  sTitle = Right(sTitle, Len(sTitle - 4)) 'removes the "ZZZ " to the left of the title
  rs("Title") = sTitle

There I would do the processing on the server side since you are only modifying data on the server database.

As to the last issue, DSN's can be slower, but they are more solid than using a Provider statement. I use DSN's mostly to prevent people from running my apps on an unauthorized machine. Provider statements are very portable.

Not being familiar with AS400, can you build the query on the AS400? I do that with SQL Server since it optimizes the query, as you say. I use Enterprise Manager, and create the View that way. I don't use Access to do that. Then you can even connect (link) the view directly as a Table object instead of a Query, if you want. Otherwise I use a Stored Procedure and then just pass variables to it. That is a little more complicated for starters.

Did that help?

mmcdonal July 16th, 2009 08:03 AM

As to the book, there are a lot of Access VBA books at Wrox. I have Access 2000, and 2003 VBA, and Access Application Development by Wrox, for example. I would supplement that with a Microsoft Press Windows Scripting Guide (sorry Wrox) like Windows 2000 Scripting Guide, Advanced Scripting for Windows Administrators, and IDG's Windows Scripting Secrets, which all have database scripting pointers.

I have VB6 and VB.Net books for those platforms, since VBScript, VBA and VB are all syntatically similar, but not exact. But the Wrox VBA references are more spot on than Visual Basic. The Access development books at Wrox are the best, that is why I have a 24x7 subscription, although I am always learning stuff from this forum.

See: http://www.wrox.com/WileyCDA/WroxTit...764556592.html


And the still fresh: http://www.wrox.com/WileyCDA/WroxTit...764559044.html

Did that help?

All times are GMT -4. The time now is 02:32 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.