msquery to as400 using a cell as criteria (From FAQ) 2

[scotsql]

Hi there,

I am trying to use VBA to query and as400 system using a cell as a criteria. i got the following from an FAQ here:

Sub query()
'
' query Macro
' Macro recorded 01/04/2005 by Blount
'

'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=Data.xls;DefaultDir=I:\P&P PRO" _
        ), Array( _
        "CEDURES\Shared reports\New Period Sales\2005\March 2005\Nottingham;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A1"))
        .CommandText = Array("SELECT DATA.LHENDT, DATA.MQUSER, DATA.`00003`, DATA.ITEGNG" & Chr(13) & "" & Chr(10) & "FROM DATA DATA" & Chr(13) & "" & Chr(10) & "WHERE (DATA.LHENDT Like " & Sheets("Sheet1").Range("f1"))
        .Name = "Query from Excel Files_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

but i am getting a runtime error 1004 sql syntax error. on the

.Refresh BackgroundQuery:=False

line.

i am so stuck!! any help would be greatly appreciated.

thanks all.

 

[scotsql]

sorry this is not querying as400 this is querying excel. sorry again my head is not screwed on right today.

 

[HarleyQuinn]

Hi,

Don't know if this is the cause but you appear to have no wildcard qualifiers in the criteria for the LIKE clause.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[SkipVought]

Hi,

I always calrify my macro recorded code...

    Dim sSQL As String
    sSQL = "SELECT DATA.LHENDT, DATA.MQUSER, DATA.`00003`, DATA.ITEGNG "
    sSQL = sSQL & "FROM DATA DATA "
    sSQL = sSQL & "WHERE (DATA.LHENDT Like " & Sheets("Sheet1").Range("f1")

More than likely, your LIKE statement should be...

    sSQL = sSQL & "WHERE (DATA.LHENDT Like '*" & Sheets("Sheet1").Range("f1") & "*') "

or something like that. BTW, you ALSO missed the CLOSING PARENTHESES.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[scotsql]

thankyou so much for your posts. skip im still having trouble with this . what should the whole code look like? i really appreciate your help.

 

[HarleyQuinn]

You can use Skip's code to create the SQL statement anywhere in yours as long as it is above the

.commandtext

line in your code. Personally I would create it before you execute the line.

With ActiveSheet.QueryTables.

Then change your line to read

.CommandText = Array(sSQL)

and that should be in the right direction.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[SkipVought]

You ADD your query ONLY ONCE -- so why code it?

I redid BOTH an AddQuery and an ExecuteQuery so that you can see the difference.

Sub AddQuery()
    Dim sConn As String, sSQL As String
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=Data.xls;"
    sConn = sConn & "DefaultDir=I:\P&P PROCEDURES\Shared reports\New Period Sales\2005\March 2005\Nottingham;"
    sConn = sConn & "DriverId=790;"
    sConn = sConn & "MaxBufferSize=2048;"
    sConn = sConn & "PageTimeout=5;"
    
    sSQL = "SELECT DATA.LHENDT, DATA.MQUSER, DATA.`00003`, DATA.ITEGNG "
    sSQL = sSQL & "FROM DATA DATA "
    sSQL = sSQL & "WHERE (DATA.LHENDT Like '*" & Sheets("Sheet1").Range("f1") & "*' "
    
    With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))
        .CommandText = sSQL
        .Name = "Query from Excel Files_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
Sub ExecuteQuery()
    Dim sConn As String, sSQL As String
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=Data.xls;"
    sConn = sConn & "DefaultDir=I:\P&P PROCEDURES\Shared reports\New Period Sales\2005\March 2005\Nottingham;"
    sConn = sConn & "DriverId=790;"
    sConn = sConn & "MaxBufferSize=2048;"
    sConn = sConn & "PageTimeout=5;"
    
    sSQL = "SELECT DATA.LHENDT, DATA.MQUSER, DATA.`00003`, DATA.ITEGNG "
    sSQL = sSQL & "FROM DATA DATA "
    sSQL = sSQL & "WHERE (DATA.LHENDT Like '*" & Sheets("Sheet1").Range("f1") & "*' "
    
    With Sheets("YourSheetName").QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[scotsql]

that has worked great!! now i want to try so that DATA.LHENDT is equal to cell F1 and all my trying just keeps on getting errors. any ideas? thanks again for everyones help guys.

 

[HarleyQuinn]

Replace the LIKE with = and then remove the *'s.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[scotsql]

thanks again.

with this

Dim sSQL As String
    sSQL = "SELECT DATA.LHENDT, DATA.MQUSER, DATA.`00003`, DATA.ITEGNG "
    sSQL = sSQL & "FROM DATA DATA "
    sSQL = sSQL & "WHERE (DATA.LHENDT = '" & Sheets("Sheet1").Range("f1") & "') "

i am getting a general odbc error??

so sorry to be a pain. thanks again...

 

[SkipVought]

and
[tt]
Sheets("Sheet1").Range("f1")
[/tt]
has valid data?

Select sheet1!A1

Data/Get External Data/Edit Query -- and get into the MS QBE Grid.

Can you return any rows?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}