Excel - MSQuery - combining parameter and cast function

[LadyLin]

I am using MSQuery to access data in a SQL database.
I am trying to combine a parameter use (from a cell in the workbook) with the Cast statement:

My statement

SELECT WORKORDER_BINARY.WORKORDER_BASE_ID,
CAST(CAST(WORKORDER_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)), WORKORDER_BINARY.BITS_LENGTH FROM TEST.dbo.WORKORDER_BINARY WHERE (WORKORDER_BINARY.WORKORDER_BASE_ID=?)

gives the error "Parameters are not allowed in queries that can't be displayed graphically".

Can anyone help?

 

[xlbo]

if you can use VBA, this is not an issue as the SQL statement can be accesses and manipulated as a string eg:

dim mSQL as string

mSQL = "SELECT WORKORDER_BINARY.WORKORDER_BASE_ID, 
CAST(CAST(WORKORDER_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)), WORKORDER_BINARY.BITS_LENGTH  FROM TEST.dbo.WORKORDER_BINARY WHERE (WORKORDER_BINARY.WORKORDER_BASE_ID=" & Range("A1").value & ")"

with sheets("Sheetname").querytables(1)
    .commandtext = mSQL
    .refresh (false)
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Turn on your macro recorder

Edit the query -- Data/Get External Data/Edit -- return data to Excel

Turn off the recorder.

Observe you code.

Assuming that your parameter is in A1...

    Dim sSQL As String
    
    sSQL = "SELECT WORKORDER_BINARY.WORKORDER_BASE_ID "
    sSQL = sSQL & ", CAST(CAST(WORKORDER_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000))"
    sSQL = sSQL & ", WORKORDER_BINARY.BITS_LENGTH  "
    sSQL = sSQL & "FROM TEST.dbo.WORKORDER_BINARY "
    sSQL = sSQL & "WHERE (WORKORDER_BINARY.WORKORDER_BASE_ID=" & [A1] & ")"

use variable sSQL to assign the .CommandText property

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LadyLin]

Thanks for the help but I don't quite understand.
I can put the code in, I used the AltF11 screen. But then what? How do I then activate the query to get the data.
Sorry to be so dense but I've always just used MSQuery to get the data. I see that the query is now in mSQL.

 

[SkipVought]

You can run code for the query from a button click event or in the Workbook_Open event.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[xlbo]

As Skip said, you can attach the code to a button or you can make it run when the workbook is opened...

One of the lines of code should "refresh" the querytable

mSQL is just a variable that holds a string (text) which can be used as a substitute for the actual text when writing code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LadyLin]

Thanks for your help, I'm just getting back to this problem.
I created a button and assigned the macro to it. Here is my code, (Right now I hard-coded the parameter, until I get the button working.)

[/code]
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/2/2005
'

'
Dim sSQL As String
sSQL = "SELECT WORKORDER_BINARY.WORKORDER_BASE_ID, WORKORDER_BINARY.BITS_LENGTH,"
sSQL = sSQL & "CAST(CAST(WORKORDER_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000))"
sSQL = sSQL & " FROM LINDA.dbo.WORKORDER_BINARY"
sSQL = sSQL & "WHERE (WORKORDER_BINARY.WORKORDER_BASE_ID='CRNew-ag03799-avio-v50287-4')"
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Linda_VMFG;Description=Lindas copy of VMFG;UID=readonly;PWD=readx;APP=Microsoft® Query;WSID=LINDALAPTOP;DATABASE=LINDA;Auto" _
), Array("Translate=No"))
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With
End Sub
[/code]

When I execute the macro I get the error:
Runtime error '1004'
Application defined or object defined error.
When I click debug

The line that reads
With Selection.QueryTable is highlighted yellow.

Any ideas?

 

[SkipVought]

It is better to write this line of code with a SPECIFIC SHEET REFERENCE as such, for instance...

   With Sheets("MyQuerySheet").QueryTables(1)
...

assuming that you have only ONE query table on this sheet.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LadyLin]

Thanks Skip,
I tried that, my code:

With Sheets("CR_WO_Specs").QueryTables(1)

Now I get SQL syntax error with the line

.Refresh BackgroundQuery:=False highlighted in yellow.

Also how do you get your code in that window, I tried using [/code] but didn't get the window.

 

[SkipVought]

You have an error in your SQL, probably a missing space like here...

    sSQL = sSQL & " FROM LINDA.dbo.WORKORDER_BINARY[red][b]"
    sSQL = sSQL & "[/b][/red]WHERE (WORKORDER_BINARY.WORKORDER_BASE_ID='CRNew-ag03799-avio-v50287-4')"

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LadyLin]

Thanks for all your help.
I finally got it, seems I missed a space when concatenating
the SQL statement. Now I've added the parameter and I've got what I wanted, amazing what a new day and fresh brain can accomplish. Thanks again!

 

[SkipVought]

Those li'le things matter!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}