MS Access/SQL Server pass thru queries

[kena1921]

Hi

I am a fairly experienced MS Access programmer but have less experience of SQL Server. I am building an Access/SQL Server client/server app and I'm looking for some advice on the best ways of dealing with parameter queries.

For example I have a form in Access with a dropdown. I need to take the dropdown value, use it as criteria in a query, send this to the SQL data and display the returned results on the form etc. I have been trying to achieve this using a pass thru query - I'm ok with getting a non paramter pass thru to work but what I'm unsure of is getting it to work whilst passing the paramter. I suspect I need to use a query def but I'm unsure how.

All advice gratefully received, this is fairly urgent - some simple examples would be of great use.

Many thanks

Ken

 

[dhookom]

I create a single function using a little DAO code:

Public Function ChangeSQL(strQueryName as String, strSQL as String) as String
    Dim qd as DAO.QueryDef
    Dim db as DAO.Database
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQueryName)
    ChangeSQL = qd.SQL
    qd.SQL = strSQL
    SET qd = Nothing
    Set db = Nothing
End Function

You can then call this function in your form like:

Dim strOldSQL as String
Dim strNewSQL as String
Dim strQueryName as String
strQueryName = "qsptMyPassThrough"
strNewSQL = "SELECT .... FROM ... WHERE ID =" & Me.txtID
strOldSQL = ChangeSQL(strQueryName, strNewSQL)

Duane
Hook'D on Access
MS Access MVP

 

[kena1921]

Hi Duane

Thanks for this - its so refreshing to get an answer with a clear and concise example.

A couple of queries...

1. In the SQL line

'strNewSQL = "SELECT .... FROM ... WHERE ID =" & Me.txtID

Does the from part refer to the pass thru query specified in 'strQueryName' or can it be any table/query?

2. Being fairly new to this it would be useful for me to know if this method is the best way of dealing with paramater queries or are there any others equally good/better. What are the pro's and cons?

Many thanks

Ken

 

[dhookom]

1. The "FROM..." is part of the SQL statement that will be executed directly on the database server. The full strNewSQL should be a SQL statement that meets the syntax of your database server. For instance, you would not want to use Nz() since this is a VBA function. Instead you would need to use IsNull() for SQL Server. Also, dates are delimited with single quotes rather than #.

If you have trouble figuring this out, you should come back with your p-t SQL statement as well as the control names and data types.

I think this is the most effective method of getting dynamic values from user input (controls on forms) into a pass-through query.

Duane
Hook'D on Access
MS Access MVP

 

[kena1921]

Thanks - again that was very useful. One supplementary question - if I was to use this method for an append query with parameters how would I return the newly created ID from the relevant table if I needed to use for a second query?

 

[dhookom]

SQL Server allows returning of the new identify column value. It's in the documentation. I think it's
SELECT @@IDENTITY
or similar.

Duane
Hook'D on Access
MS Access MVP