[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-02-22
4
Medium Priority
?
669 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

656 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