Parameterized Queries

[jdebeer]

Good Evening from San Diego


I am having the devils own time understanding parametized queries
where i can pass the parameter directly into the VBA script without a
dialogue box.

Code below works but the dialogue box comes up.

Thanks
------------------
Sub ParameterExample()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cat As New ADOX.Catalog


Set con = CurrentProject.Connection

Set cmd = New Command
Set rst = New Recordset
Set prm = New Parameter

cmd.ActiveConnection = con

cmd.CommandText = "Select * From AllCdList Where Artist=[Singer];"

' Open the Catalog
Set cat.ActiveConnection = con

' Create the new Procedure
cat.Procedures.Append "SingerID", cmd

cmd.Parameters.Refresh

Set prm = cmd.CreateParameter("SINGER", adVarChar, adParamInput, 50)
cmd.Parameters.Append prm

prm.Value = "Shakira"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"SingerID", "c:\testmeOut.xls"

cat.Procedures.Delete "singerID"
Set cat = Nothing

Set con = Nothing

Set cmd = Nothing
Set rst = Nothing
Set prm = Nothing

End Sub

 

[Eddy]

I got a parameterized query once a couple of months ago. I would suggest
you take out the following line:-

cmd.CommandText = "Select * From AllCdList Where Artist=[Singer];"

This "SELECT" query should be stored in the database not be created by code.
Your codes should execute the query (stored procedure) only. Hope it helps.

Good Evening from San Diego


I am having the devils own time understanding parametized queries
where i can pass the parameter directly into the VBA script without a
dialogue box.

Code below works but the dialogue box comes up.

Thanks
------------------
Sub ParameterExample()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cat As New ADOX.Catalog


Set con = CurrentProject.Connection

Set cmd = New Command
Set rst = New Recordset
Set prm = New Parameter

cmd.ActiveConnection = con

cmd.CommandText = "Select * From AllCdList Where Artist=[Singer];"

' Open the Catalog
Set cat.ActiveConnection = con

' Create the new Procedure
cat.Procedures.Append "SingerID", cmd

cmd.Parameters.Refresh

Set prm = cmd.CreateParameter("SINGER", adVarChar, adParamInput, 50)
cmd.Parameters.Append prm

prm.Value = "Shakira"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"SingerID", "c:\testmeOut.xls"

cat.Procedures.Delete "singerID"
Set cat = Nothing

Set con = Nothing

Set cmd = Nothing
Set rst = Nothing
Set prm = Nothing

End Sub

 

[jdebeer]

Thanks, I'll try it