R
Rhonda Fischer
Hello,
I am currently converting my Access queries into
pass-through queries using SQL Server syntax,
with view to speeding up my application.
I would like to pass user input values from my Access
forms into my pass-through queries. However I receive
the error message that I must declare the variable
@inparam. Although SQL Server does not recognise the
reference to a form value of [Forms]![FormName]!
[FormField] syntax. How can I resolve this?
My efforts are as below.
Thank you very much for any suggestions you may have.
Cheerio
Rhonda
'*************** PASS-THROUGH QUERY ****************
USE pubs
GO
CREATE PROCEEDURE qryTescoLoads
@inparam datetime
AS
SELECT [Deliveries Made].[Del Date], [Deliveries Made].
[Wave Number], [Deliveries Made].RDC,
[Deliveries Made].[Veh Reg], [Deliveries Made].
[Trailer No], [Deliveries Made].[Coll Point],
[Deliveries Made].[Coll Point2], [Deliveries Made].
[Coll Point3], [Deliveries Made].[Book Time],
[Deliveries Made].TotalPallets
FROM [Deliveries Made]
WHERE [Deliveries Made].[Del Date] = @inparam
AND [Deliveries Made].RDC Like "c*"
ORDER BY [Deliveries Made].RDC
GO
'******************** QUERY CALLED ON FORM LOAD *******
Private Sub Form_Load()
On Error GoTo Err_Form_Load
'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim ViewTescoLoadsDate As Variant
Dim inparam As Parameter
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
'Open the connection
Set cnn = CurrentProject.Connection
'THIS DONE IN PROPERTIES OF PASS-THROUGH QUERY INSTEAD *
'Specify connection string on Open method
'provStr = "ODBC;DRIVER=SQL
Server;SERVER=ZSQUIRREL;DATABASE=TurnersMgmtSystem;Trusted_
Connection=Yes"
'cn.Open provStr'***************
'Set up a command object for the stored procedure
With cmd
.ActiveConnection = cnn
.CommandText = "QryTescoLoads"
.CommandType = adCmdStoredProc
End With
'Set up a return parameter
Set inparam = cmd.CreateParameter("Input", adDate,
adParamInput)
cmd.Parameters.Append inparam
ViewTescoLoadsDate = [Forms]!
[FrmTescoOrderPlannerSelectDate]![ViewDate]
inparam.Value = ViewTescoLoadsDate
Set rst = cmd.Execute
rst.Close
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub
I am currently converting my Access queries into
pass-through queries using SQL Server syntax,
with view to speeding up my application.
I would like to pass user input values from my Access
forms into my pass-through queries. However I receive
the error message that I must declare the variable
@inparam. Although SQL Server does not recognise the
reference to a form value of [Forms]![FormName]!
[FormField] syntax. How can I resolve this?
My efforts are as below.
Thank you very much for any suggestions you may have.
Cheerio
Rhonda
'*************** PASS-THROUGH QUERY ****************
USE pubs
GO
CREATE PROCEEDURE qryTescoLoads
@inparam datetime
AS
SELECT [Deliveries Made].[Del Date], [Deliveries Made].
[Wave Number], [Deliveries Made].RDC,
[Deliveries Made].[Veh Reg], [Deliveries Made].
[Trailer No], [Deliveries Made].[Coll Point],
[Deliveries Made].[Coll Point2], [Deliveries Made].
[Coll Point3], [Deliveries Made].[Book Time],
[Deliveries Made].TotalPallets
FROM [Deliveries Made]
WHERE [Deliveries Made].[Del Date] = @inparam
AND [Deliveries Made].RDC Like "c*"
ORDER BY [Deliveries Made].RDC
GO
'******************** QUERY CALLED ON FORM LOAD *******
Private Sub Form_Load()
On Error GoTo Err_Form_Load
'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim ViewTescoLoadsDate As Variant
Dim inparam As Parameter
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
'Open the connection
Set cnn = CurrentProject.Connection
'THIS DONE IN PROPERTIES OF PASS-THROUGH QUERY INSTEAD *
'Specify connection string on Open method
'provStr = "ODBC;DRIVER=SQL
Server;SERVER=ZSQUIRREL;DATABASE=TurnersMgmtSystem;Trusted_
Connection=Yes"
'cn.Open provStr'***************
'Set up a command object for the stored procedure
With cmd
.ActiveConnection = cnn
.CommandText = "QryTescoLoads"
.CommandType = adCmdStoredProc
End With
'Set up a return parameter
Set inparam = cmd.CreateParameter("Input", adDate,
adParamInput)
cmd.Parameters.Append inparam
ViewTescoLoadsDate = [Forms]!
[FrmTescoOrderPlannerSelectDate]![ViewDate]
inparam.Value = ViewTescoLoadsDate
Set rst = cmd.Execute
rst.Close
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub