HELP!!! Passing unbound parameter to pass-through query

[Krelian]

I'm really new to SQL, and has a very basic question that need the experts help here.

I'm trying to pass a user input variable from a unbound input box in a form in MSAccess to run a pass-through
query running MS-SQL7. But it looks like there is no way to pass the variable. Below is the simple SQL code for the Pass-thourgh query:

SELECT ArchiveCDTable.ArchiveCDNo,
ArchiveCDTable.DirectoryAndFile
FROM ArchiveCDTable
WHERE ArchiveCDTable.DirectoryAndFile Like '%"&[ArchiveCDTable.DirectoryAndFile]&"%'
ORDER BY ArchiveCDTable.ArchiveCDNo, ArchiveCDTable.DirectoryAndFile

I have search the Internet and it appears that you can't run the Pass-through query this way since with Pass-through query, MSAccess will send this literal commands directly to the server and it will not understand the parameter calls. So what is the simple way to tackle this?

Thanks in advance for any ideas.

 

[nealvenables]

I tackle this by reconstructing the passthrough query in code.

Assuming your query is called qryPT...

private sub CommandButton_Click()

dim daoDB as dao.Database
dim daoQDF as dao.QueryDef
dim strSQL as String

set daoDB = CurrentDB()

strSQL = "SELECT Col1, Col2, Col3... " & _
"FROM YourTables " & _
"WHERE SomeColumn = '" & YourTextBox & "'"

set daoQDF = daoDB.QueryDefs("qryPT&quot
daoQDF.SQL = strSQL
daoDB.QueryDefs.Refresh

doCmd.OpenQuery "qryPT"

End Sub

I've posted this on the fly, so you'll need to tweak/debug.

 

[Krelian]

Nealv,

First of all, thanks for your help.

So I assume the code is for the command button. But what should I SQL statements should I have for "qryPT" I still not sure how the code for the CommandButton_Click() is related to the actual past-though query. Do I even need to have a past-through query defined?

Dim daoDB As dao.Database
Dim daoQDF As dao.QueryDef
Dim strSQL As String

Set daoDB = CurrentDb()

strSQL = "SELECT ArchiveCDTable.ArchiveCDNo, ArchiveCDTable.DirectoryAndFile " & _
"FROM ArchiveCDTable" & _
"WHERE ArchiveCDTable.DirectoryAndFile Like '%" & InputSearchCriteria & "%'"



Set daoQDF = daoDB.QueryDefs("QueryPT&quot
daoQDF.SQL = strSQL
daoDB.QueryDefs.Refresh

DoCmd.OpenQuery "QueryPT"

 

[nealvenables]

Hi,

This code is a general example that would allow your users to do something along the lines of analysing data, i.e. they put a criteria into a text-box, click a command button, and up pops a query showing the results.

To answer the question "But what should I SQL statements should I have for "qryPT". It's the code itself that creates the SQL within the passthrough query - so long as the query exists with ANYTHING in it before the code is executed this will work.

You're right though - a passthrough query isn't an essential part of getting a criteria up to SQL. If you wanted to load your data into a recordset, something along the following lines would work:

private sub CommandButton_Click()

dim adoCNN as New adodb.Connection
dim adoCMD as new adodb.Command
dim adoRST as adodb.Recordset

dim strSQL as String

adoCNN.open (insert connection string here)


strSQL = "SELECT ArchiveCDTable.ArchiveCDNo, ArchiveCDTable.DirectoryAndFile " & _
"FROM ArchiveCDTable" & _
"WHERE ArchiveCDTable.DirectoryAndFile Like '%" & InputSearchCriteria & "%'"

adoCMD.activeconnection = adoCNN
adoCMD.commandtype = adCmdText
adoCMD.commandtext = strSQL

set adoRST = adoCMD.execute

End Sub

However, in this scenario, what would work MUCH better is using a parameter based stored procedure to get data into your recordset (happy to give you a few pointers if SQL BOL isn't clear).

The only time I would use a passthrough query is in cases where I have to bind an Access control to a query, for instance a listbox or combobox.

Otherwise, I always use stored procedures to fetch data from SQL as they are a lot more efficient.