Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Variables is Pass-Through SQL

Status
Not open for further replies.

nmair

MIS
Mar 30, 2011
22
CA
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,
 

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
 
Hi Andy,

thanks so far.

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

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
 
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:
Code:
  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
 
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.
 
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
 
That was great!!!

Thanks all for you help!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top