Solved

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

Posted on 2011-02-22
4
657 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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