?
Solved

Can I define variables in an AS400 query using Microsoft Query via Excel

Posted on 2011-02-22
4
Medium Priority
?
668 Views
Last Modified: 2012-08-13
I'm working with an AS400 database via Microsoft Query using Excel. Does anyone know if it's possible to define variables to allow me to utilize user input values in my query?

Thanks,
David
0
Comment
Question by:dking_wri
[X]
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
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 1500 total points
ID: 34954212
David,
Here is a code snippet I use in Excel VBA to retrieve data from an AS400 database:
'Retrieves the engineering part description and puts it into a messagebox.
    
Dim db As Object, rs As Object
Dim s As String, sDesc As String, sSQL As String
Dim i As Long

'sPartNumber = ActiveCell.Value     'I was triggering the code by right-clicking a cell containing the part number
sPartNumber = InputBox("Please enter the desired part number")  'Display a screen allowing the user to enter the part number

Set db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
db.Open "Provider=IBMDA400;Data Source=AS400PRDEXEC;", "DPAPPSERV", "DPAPPSERV"
If Err <> 0 Then
    s = "Could not retrieve description from AS/400. If you dropped your connection momentarily," & vbLf & _
        "please wait 30 seconds and try again." & vbLf & vbLf & _
        "Click 'Yes' if you still want to see the cutsheet."
    Err.Clear
Else    'Build a SQL string using the partnumber to retrieve the engineering description
    sSQL = "SELECT IM.IMDSC, ED.REEDSC FROM SIM400MFG.FKITMSTR IM" _
        & " LEFT JOIN SIM400MFG.FKITEXTD ED ON IM.IMCO=ED.IMCO AND IM.IMPN=ED.IMPN" _
        & " WHERE IM.IMCO=1 AND IM.IMPN='" & sPartNumber & "'"
    rs.Open sSQL, db, 3, 3  'Last two parameters are integer values of constants adOpenStatic & adLockOptimistic
    
    If Not (rs.BOF And rs.EOF) Then
        With rs
            s = sPartNumber & vbLf & .Fields("IMDSC") & vbLf
            For i = 1 To 100        'Allow up to 100 lines in the engineering description
                If .EOF Then Exit For
                s = s & .Fields("REEDSC") & vbLf
                .MoveNext
            Next
            .Close
        End With
    End If
    db.Close
End If

Open in new window


The AS/400 SQL strings were specific to the engineering database at a former employer. You'll need to substitute your own.

Brad
0
 

Author Comment

by:dking_wri
ID: 34954844
Hi Brad,

Thanks for the reply. Do you happen to know equivalent SQL syntax? I'm working in Microsoft Query and am not well versed in VBA.

David
0
 
LVL 81

Expert Comment

by:byundt
ID: 34955146
David,
I don't know SQL at all.

The posted code uses VBA to build the SQL string in statements 19 through 21. In VBA, the underscore character is a line continuation. So the statement may alternatively be built as:
    sSQL = "SELECT IM.IMDSC, ED.REEDSC FROM SIM400MFG.FKITMSTR IM LEFT JOIN SIM400MFG.FKITEXTD ED ON IM.IMCO=ED.IMCO AND IM.IMPN=ED.IMPN WHERE IM.IMCO=1 AND IM.IMPN='" & sPartNumber & "'"

Note that sPartNumber is surrounded by single quotes in the concatenation.

Brad
0
 

Author Closing Comment

by:dking_wri
ID: 34962376
This was helpful for VB code but I was looking for a SQL version. I may be able to use pieces of this to address my issues.

Thanks,
David
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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