View Single Post
  #3 (permalink)  
Old July 14th, 2009, 12:09 PM
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

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,
Reply With Quote