You write your SQL with question marks in place of the parameters. The DE automatically creates parameter objects corresponding to your question marks. Then you assign values when you execute the query at run time. From the Help file:
Creating a Parameterized Query
Create a new project and add a DE designer.
On the DE designer, create a Connection object and configure it to connect to the SQL Server Pubs database.
Right-click the Connection object and click Add Command.
Rename the Command object CommandQuery.
Right-click the Command object and click Properties.
On the General tab, click SQL Statement and type the following:
SELECT * FROM Authors Where Au_ID = ?
Click the Parameters tab. (A message will appear informing you that not enough information has been supplied. Click OK.)
A new Input parameter will have been created for you. You can create further parameters, if needed, or set properties of the parameter.
Click OK to close the dialog box.
On the default form, draw two TextBox controls and a CommandButton control.
Add the code below to the Form object's code window:
Option Explicit
Private Sub Command1_Click()
' You must close the recordset before changing the parameter.
If DataEnvironment1.rsCommandQuery.State = adStateOpen Then
DataEnvironment1.rsCommandQuery.Close
End If
' Reopen the recordset with the input parameter supplied by
' the TextBox control.
DataEnvironment1.CommandQuery Text1.Text
With Text2
.DataField = "AU_LName"
.DataMember = "CommandQuery"
Set .DataSource = DataEnvironment1
End With
End Sub
Private Sub Form_Load()
' Supply a default value.
Text1.Text = "172-32-1172"
' Change the CommandButton caption.
Command1.Caption = "Run Query"
End Sub
Run the project and click the button. Change the text in Text1 to run another query.