Solved

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

Posted on 2011-02-22
4
653 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
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2016 - Black cell borders 11 26
SQL Statement to Update Email Domain 2 19
Updating a table from a temp table 4 25
Access 2010 Query Syntax 5 15
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

932 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now