Pass a paramater to an SQL statement without prompting the user 1

[hcisteve]

I want to pass a paramater to an SQL statement in my VBA code but no matter what I try it always prompts the user for a value. I want to have the value passed from the VBA code and not require user input.

Steve

 

[VBslammer]

What I do is create a second dummy querydef (like a temporary QueryDef for multiple uses), and assign its SQL property with a "fixed-up" version of the parameterized query's SQL. For example, when you make a parameterized query using the query designer, your SQL statement will have a WHERE clause like this:

WHERE (((Contacts.LastName)=[LastName:]));

What you need to do in order to "fix-up" the statement is replace the [LastName:] portion with the actual LastName value and surround it with single quotes, so it looks like:

WHERE (((Contacts.LastName)='Smith'));

This is where the temp querydef comes in. I want to keep the original query's SQL intact, so I "steal" it and replace the parameter text with the actual value I want to use, then open the temporary query:

Private Sub cmdOpenQuery_Click()
On Error GoTo ErrHandler

  Dim db As Database
  Dim qdf As QueryDef
  
  Set db = CurrentDb()
  
  On Error Resume Next
  Set qdf = CurrentDb.QueryDefs("#tempQDF")
  If Err <> 0 Then
    Set qdf = db.CreateQueryDef("#tempQDF")
  End If
  On Error GoTo ErrHandler
  
  With qdf
    .SQL = Replace(db.QueryDefs("ContactsQueryParameters").SQL, "[LastName:]", "'" & Me![txtLastName] & "'")
    DoCmd.OpenQuery .Name, acViewNormal
  End With

ExitHere:
  On Error Resume Next
  Set qdf = Nothing
  Set db = Nothing
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

The following line:

Set qdf = CurrentDb.QueryDefs("#tempQDF")

...should read:

Set qdf = db.QueryDefs("#tempQDF")

VBSlammer

Unemployed in Houston, Texas

 

[chris13]

I had the same problem. There is an easy way to make this work.

Define the parameter(s) in the query (SQL-View):

PARAMETERS LastName Text;
SELECT... WHERE users.name = [LastName];

In your VB-code you can set the value of the parameter before executing it:

qdf.Parameters!LastName = "whateveryouwant"

 

[VBslammer]

Unfortunately, if you're trying to open the querydef object directly, setting the parameter using vba will still prompt the user for the parameter.

However, if you're trying to open a recordset, the parameters collection can be set in code and the recordset will be filtered correctly:

  Dim db As Database
  Dim qdf As QueryDef
  Dim rst As Recordset
  
  Set db = CurrentDb()

  Set qdf = db.QueryDefs("Query1")
  qdf.Parameters("Param1").Value = "Smith"

  Set rst = qdf.OpenRecordset()
  While Not rst.EOF
    Debug.Print rst.Fields("LastName")
    rst.MoveNext
  Wend

VBSlammer

Unemployed in Houston, Texas

 

[GComyn]

VBSlammer,

If you are doing an action query (delete, append, update) then chris13's suggestion works (that's how I do it), but for select queries, I like your Idea...

Also, I usually have a problem with time out (especially with ODBC tables) because of our network (there are so many people on it, it slows way down), so I usually use DoCMD.RunSQL with a custom built sql statement in my VBA Code. But after seeing your suggestion, I think I'll try making a "temp" query, with the timeout and transaction set the way I want, then change the SQL in it whenever I want to run it, instead of using RunSQL.

What do you think, would that be faster? Or should I keep with the RunSQL that I'm using?

Using a Temp Query and changing out the SQL would actually help with debugging and (the inevitable) changes that my bosses want me to make.... Think I'll give it a try!

GComyn

 

[chris13]

Hey, that seems to be an interesting topic

I set the parameter and run the query from a VB-module in Access:

...
qdf.Parameters!LastName = "whateveryouwant"
qdf.execute
...

The query is something like

SELECT... FROM <linked table from Oracle-DB> INTO <table in local .MDB>

Yes, that's an action-query. There is no result-set.
It's using the Jet-Engine. Perhaps there are differences to ODBC-direct workspaces?
I'll try out a query producing a resultset and the method with temporary querydefs, too.

I got problems with timeouts some time ago, too. The Oracle ODBC driver has an option to disable timeout-checking. Good to make other applications run their queries without failure...

chris