=> Collect inparam value from form and pass into SQL Query

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
 
M

[MVP] S. Clark

One way to solve this is to rewrite the SQL in the PTQ prior to exection.

So, open the querydef, change the SQL to include the value from the form,
close & save the querydef, then execute it. This is not graceful by any
stretch, and I'm afraid you are going to complete your operation, only to
find that the application is slower than when the SQL Server tables were
linked. YRMV.

Good luck,

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Rhonda Fischer said:
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
Click to expand...
 
R

Rhonda Fischer

Dear Steve,

Thank you very much for your reply. I'm a bit
disappointed to hear that my attempt to
convert the Access query makes for a slower
and clunky SQL query.

I'm trying a different approach and wondered if
this might improve the performance of my application.

My current attempt is as follows. Just having a problem
assigning a value to a subform?

Thank you kindly for any suggestions that you may have.

Cheerio
Rhonda

'******* Embedded SQL QUERY IN VB CODE TO DISPLAY TO FORM

Sub displayTescoLoads()
On Error GoTo Err_displayTescoLoads

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT [Del Date], [Wave Number],
RDC, [Veh Reg], [Trailer No], " & _
"[Coll Point], [Coll Point2], [Coll
Point3], [Book Time], TotalPallets " & _
"FROM [Deliveries Made] " & _
"WHERE [Deliveries Made].[Del Date]
= " & [Forms]![FrmTescoOrderPlannerSelectDate]![ViewDate]
& _
"AND RDC LIKE 'c%' ORDER BY
[Deliveries Made].RDC "
End With

Set rst = cmd.Execute
'*********** ErrMsg: Object doesn't support this
property or method *********
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads.txtDelDate =
rst![Del Date]

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Exit_displayTescoLoads:
Exit Sub

Err_displayTescoLoads:
MsgBox Err.Description
Resume Exit_displayTescoLoads

End Sub







-----Original Message-----
One way to solve this is to rewrite the SQL in the PTQ prior to exection.

So, open the querydef, change the SQL to include the value from the form,
close & save the querydef, then execute it. This is not graceful by any
stretch, and I'm afraid you are going to complete your operation, only to
find that the application is slower than when the SQL Server tables were
linked. YRMV.

Good luck,

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Rhonda Fischer said:
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
Click to expand...


.
Click to expand...
 
M

[MVP] S. Clark

One of the biggest misconceptions about Access is that just by using SQL
Server, everything will be faster. This is just not true. You may want to
consider converting your mdb to an adp, as it is designed to take advantage
of more of what SQL Server has to offer.

Rhonda Fischer said:
Dear Steve,

Thank you very much for your reply. I'm a bit
disappointed to hear that my attempt to
convert the Access query makes for a slower
and clunky SQL query.

I'm trying a different approach and wondered if
this might improve the performance of my application.

My current attempt is as follows. Just having a problem
assigning a value to a subform?

Thank you kindly for any suggestions that you may have.

Cheerio
Rhonda

'******* Embedded SQL QUERY IN VB CODE TO DISPLAY TO FORM

Sub displayTescoLoads()
On Error GoTo Err_displayTescoLoads

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT [Del Date], [Wave Number],
RDC, [Veh Reg], [Trailer No], " & _
"[Coll Point], [Coll Point2], [Coll
Point3], [Book Time], TotalPallets " & _
"FROM [Deliveries Made] " & _
"WHERE [Deliveries Made].[Del Date]
= " & [Forms]![FrmTescoOrderPlannerSelectDate]![ViewDate]
& _
"AND RDC LIKE 'c%' ORDER BY
[Deliveries Made].RDC "
End With

Set rst = cmd.Execute
'*********** ErrMsg: Object doesn't support this
property or method *********
[Forms]!FrmTescoOrderPlanner!FrmTescoLoads.txtDelDate =
rst![Del Date]

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Exit_displayTescoLoads:
Exit Sub

Err_displayTescoLoads:
MsgBox Err.Description
Resume Exit_displayTescoLoads

End Sub







-----Original Message-----
One way to solve this is to rewrite the SQL in the PTQ prior to exection.

So, open the querydef, change the SQL to include the value from the form,
close & save the querydef, then execute it. This is not graceful by any
stretch, and I'm afraid you are going to complete your operation, only to
find that the application is slower than when the SQL Server tables were
linked. YRMV.

Good luck,

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Rhonda Fischer said:
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
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

=> Continuous Form to Display output of a recordset 1
INSERT INTO where data is on an Oracle database 3
=> SQL Query to collect in parameters from Access Forms 1
Run a SQL Stored Procedure from VBA 0
Count number of Records Query always returns -1 10
Access 2003 and SQL Server 2005 and DAO recordset 1
Who Is Logged On? 12
ADO problem with Access Report 3

Top