Solved

Excel VBA Query

Posted on 2014-02-12
2
384 Views
Last Modified: 2014-02-13
Is it possible to better rewrite the query in Excel VBA using parameter query or other better way to handle multiple parameters, Tks

'Retrieve record from SQL Server
Set cnt = New ADODB.Connection
    

stADO = "Provider=SQLOLEDB.1;" & _
        "Data Source=xxxx;Initial Catalog=Dynamics_BAK;User ID=report;Password=123"

    
With cnt
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 0
End With


'SQL Statement
stSQL = "select  * from WMSORDERTRANS where SHIPMENTID = 'A10009480'  order by linenum"

Set rst = cnt.Execute(stSQL)
    

If rst.RecordCount <> 0 Then
    rst.MoveFirst
    Do
        MsgBox rst("itemid")
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
End If

Open in new window

0
Comment
Question by:AXISHK
2 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39856381
You can construct a list of values and use the IN () clause.  I assume that you would pull these values from your worksheet.
Dim strShipIDs As String
strShipIDs = "'A10009480'"
strShipIDs = strShipIDs & ", " & "'A10009481'"
strShipIDs = strShipIDs & ", " & "'A10009482'"
strShipIDs = "(" & strShipIDs & ")"

'SQL Statement
stSQL = "select  * from WMSORDERTRANS where SHIPMENTID In " & strShipIDs & "  order by linenum"

Open in new window


Also, you can use a range of values with the Between clause:
'SQL Statement
stSQL = "select  * from WMSORDERTRANS where SHIPMENTID Between 'A10009480' And 'A10009482' order by linenum"

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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,…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

21 Experts available now in Live!

Get 1:1 Help Now