Pass parameter to stored query

[Phillip Armitage]

Access 2000 with a stored query named "Std Reg Total"

The stored query has a parameter named "Project Number"

Intention is to run the query in vba and insert the resulting records into
an existing table named RESULTS.

In the following subroutine, with the snip representing the dim and set
statements that open the database conenctions, etc, what's the best way to
assign a value of say "05000" to the [Project Number] parameter so that it
can then be used in the [Std Reg Total] stored query?

Private Sub DoCmd_click()
<snip>

SQLstr = "INSERT INTO RESULTS SELECT * FROM [Std Reg Total]"
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = SQLstr
.CommandType = adCmdText
.Execute
End With

End Sub

I look forward to your comments and suggestions.

 

[Graham Mandeno]

Hi Phillip

What is the parameter used for? If it is only part of the WHERE clause of
your query, then I suggest you leave it out entirely and add it as a WHERE
clause in your "INSERT INTO ... SELECT" statement.

Something like this:

strProjNum = "05000" ' of course this would come from somewhere else
SQLstr = "INSERT INTO Results SELECT * FROM [Std Reg Total]" _
& " WHERE [ProjectNumberField]='" & strProjNum & "'"

[if your project number field is numeric, then omit the enclosing single
quotes]

Also, is there any reason you are using ADO, not DAO?

Having created the SQL string, I would simply say:

CurrentDb.Execute SQLstr, dbFailOnError

 

[RoyVidar]

Access 2000 with a stored query named "Std Reg Total"

The stored query has a parameter named "Project Number"

Intention is to run the query in vba and insert the resulting records
into an existing table named RESULTS.

In the following subroutine, with the snip representing the dim and
set statements that open the database conenctions, etc, what's the
best way to assign a value of say "05000" to the [Project Number]
parameter so that it can then be used in the [Std Reg Total] stored
query?

Private Sub DoCmd_click()
<snip>

SQLstr = "INSERT INTO RESULTS SELECT * FROM [Std Reg Total]"
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = SQLstr
.CommandType = adCmdText
.Execute
End With

End Sub

I look forward to your comments and suggestions.

Try something like this (air code)

dim prm as adodb.parameter
With cmd1
Set .ActiveConnection = CurrentProject.Connection
.CommandText = SQLstr
.CommandType = adCmdText
Set prm = .CreateParameter("[Project Number]", _
adVarWChar, adParamInput, 255)
' last arguement is field size, could probably be 5?
.Parameters.Append prm
prm.Value = "05000"
.Execute
End With