Creating a Access parameter query from VB 2

[jojo11]

Is there a way to create a parameter query in access from VB using the QueryDef? I am writing an app that reports from an existing un-secured Access database but I don't want to have to have the client manually add them, I want to do it through code.
Possible???

-------------------------------------------
Ummm, we have a bit of a problem here....

 

[Golom]

Sure
[blue][tt]
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
On Error Resume Next
Set qdf = db.QueryDefs("myQuery")
If Err.Number <> 0 Then
Set qdf = New QueryDef
qdf.Name = "myQuery"
db.QueryDefs.Append qdf
End If
On Error GOTo 0

qdf.SQL = "PARAMETERS [Enter Value] Text; " & _
"Select * From tbl Where [Enter Value] = ..."

[/tt][/blue]
Most of this just handles the issue of updating an existing query Vs. adding a new one.

And to run it
[blue][tt]
Dim rs As DAO.Recordset
qdf.Parameters("Enter Value") = "XYZ"
Set rs = qdf.OpenRecordset
[/tt][/blue]

 

[jojo11]

Thanks, that did the trick.
Just for future reference, If I wanted to migrate to ADO with this is there an equivlant way to do it?

-------------------------------------------
Ummm, we have a bit of a problem here....

 

[Golom]

Yes ... but you will need to come up to speed on ADOX (Microsoft Extensions for DDL and Security)

In the ADO environment you define the query as an ADODB Command and append it to an ADOX Catalog.

 

[jojo11]

Thanks again;
ADOX works well unless you need to use DSN with it. I think I need to play around with the connections string syntax.

-------------------------------------------
Ummm, we have a bit of a problem here....

 

[picazhu]

hey
is it possible to assign the parameter as:
NOT "XYZ"
so it returns all but parameter ="XYZ" ???
thanks a lot

 

[Golom]

No. You can provide only values in a parameter ... not operators. For example parameter values of "> 0", "<= 10", "NOT = 'XYZ'" will not be processed as operators but rather as strings that contain those characters.

That said, you can get the effect you want by combining parameters and IIF statements. For example
[blue][tt]
PARAMETERS [Enter '<0' or '>0'] Text;

Select ... From ...

Where IIF ( Instr ( 1, [Enter '<0' or '>0'], ">" ) > 0,

Field > 0,

Field < 0 )
[/tt][/blue]
It gets a bit more challenging if you need to parse a substring from the parameter and use it in the criteria for the query.

 

[glynch123]

Hi Golom,

I wonder would you be able to help me with something?

With the code you suggested, I created a query using QueryDef. It works, but does not use the correct parameters - it uses the previous parameters. If I overwrite the queryDef with new parameters, it is not until I close the database and then reopen it that those parameters are used, or at least the correct output is shown. So after I reopen the database and use new parameters, it uses the parameters from before (sorry if this sounds confusing! Below is the code I am using:


Private Sub CreateCategoryQuery(arr As Variant)

Dim db As Database
Dim qdf As queryDef
Dim intCheck As Integer
Dim sqlStr As String

sqlStr = ""

'-------- Create Category List Query ---------
On Error Resume Next

Set db = CurrentDb()
Set qdf = db.QueryDefs("Category List Query")

If Err.Number <> 0 Then
Set qdf = New queryDef
qdf.Name = "Category List Query"
db.QueryDefs.Append qdf
End If

On Error GoTo 0

sqlStr = "Select CategoryID, CategoryName From Categories Where "

For intCheck = LBound(arr) To UBound(arr) - 1

' Last Category; do not append "OR"
If (intCheck = UBound(arr) - 1) Then
sqlStr = sqlStr & "CategoryName = '" & arr(intCheck) & _
"' ORDER BY CategoryName;"
Else
sqlStr = sqlStr & "CategoryName = '" & arr(intCheck) & "'" & " OR "
End If

Next intCheck

qdf.sql = sqlStr

End Sub



Thanks for any help!


Germaine