Variables is Pass-Through SQL

[nmair]

Hello all,

I'm new to using pass-through queries from Access to SQL Server but I do have some background in writing SQL statements and programming.

I was just wondering if it's possible to write a pass-through query and also use variables? For example:

------------------------------------------------------------
DIM MYWHERECLAUSE AS STRING

MYWHERECLAUSE = "MYTABLE.NUMBER='1' OR MYTABLE.NUMBER='2' OR MYTABLE.NUMBER='3'"

SELECT MYTABLE.NUMBER
FROM DEBITHIST
WHERE MYWHERECLAUSE
------------------------------------------------------------

is this possible?

Thanks,

 

[Andrzejek]

Did you try:
[tt]
Dim strMyWhereClause As String
Dim strSQL As String

strMyWhereClause = "MyTable.Number IN('1', '2', '3')"

strSQL = "Select * From MyTable Where " & strMyWhereClause
[/tt]
and use [tt]strSQL[/tt] in your pass-through query

Have fun.

---- Andy

 

[nmair]

Hi Andy,

thanks so far.

This may be a silly question but where would I insert this code in MS Access?

 

[Andrzejek]

No question is 'silly'
I am not an Access guru, but do you have some pass-through query that you use in Access already? And if so, you may have some SQLs already in them, so just adjust your SQLs to include your variables in them.

Have fun.

---- Andy

 

[dhookom]

nmair,
It isn't clear what you have accomplished so far. If you already have a pass-through query and only want to change the SQL, you can use code like:

  Dim strMyWhereClause As String
  Dim strSQL As String
  Dim strYourQuery as String
  strYourQuery = "qsptYourQueryNameHere"
  strMyWhereClause  = "MyTable.Number IN('1', '2', '3')"
  strSQL = "Select * From MyTable Where " & strMyWhereClause
  Currentdb.QueryDefs(strYourQuery).SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[nmair]

Sorry for the confusion.

I'm wondering if a pass-through query can have variables within it. If I enter this query as a pass-through query, I receive no errors and the query runs.

SELECT *
FROM Customers
WHERE Customers.CustomerID = '1'

But when I try to use the following pass-through query with a variable inside of it, I receive an error and the query doesn't process.

dim myWhereClause as string
myWhereClause = "Customers.CustomerID = '1'"

SELECT *
FROM Customers
WHERE myWhereClause

Where do I declare my variable then? I apologize if this is still not clear.

 

[dhookom]

You can't reference any variables or controls on forms in a Pass-Through query. That's why I suggested a little code to change the SQL property of the pass-through.

Consider using faq701-7433.

Duane
Hook'D on Access
MS Access MVP

 

[nmair]

That was great!!!

Thanks all for you help!