Solved

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

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

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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.

772 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

14 Experts available now in Live!

Get 1:1 Help Now