sql server mseq: SQL Queries with in parameters -- sql server mseq

SQL Queries with in parameters

Rhonda Fischer
10/16/2003 8:43:27 AM

sql server mseq: Hello,

I am currently changing the Access queries in my
database to SQL Server Pass-through queries with view
to speeding up my application, by-passing the Jet engine.

I have been creating pass-through queries in Access
and linking this to my SQL Server.

I now need to convert some of the Access Queries
with in-parameters from Access forms. I'm not sure
how to do this and can only think that on a click event
I call [Event Proceedure] and code my ADO SQL Query.

I have previously used such code as the following to
use in-parameters from a form and feed back information
to a form. Would I need to do the same for all queries
selecting data for display on a form or is there an
easier way? Like creating a stored procedure to receive
values from an Access form? Not sure how?

Thank you kindly for any ideas you may have.
Rhonda

'*********************** SELECT CODE *****************

Sub displayCustomerIDNew(myForm)
On Error GoTo Err_displayCustomerIDNew
'Form: frmMgmtSupplierNew
'Button: Save

'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 ID FROM tblCustomer " & _
"WHERE customerName = '" & Forms
(myForm)!txtCustomerName & "'"
End With

Set rst = cmd.Execute
Forms(myForm)!txtCustomerID = Trim(rst!ID)

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Exit_displayCustomerIDNew:
Exit Sub

Err_displayCustomerIDNew:
MsgBox Err.Description
Resume Exit_displayCustomerIDNew

End Sub

Re: SQL Queries with in parameters

Newbie
10/16/2003 5:59:23 PM

Here is a snippet of code I use to run parameterized SP in SQL 2000 from
Access XP - looks like it is similar to what you have already . . .

Dim cnMIS As ADODB.Connection
Dim cmdPickList As ADODB.Command
Dim rsPickList As ADODB.Recordset
Dim params As ADODB.Parameters
Dim param As ADODB.Parameter

All the set statements . . .etc etc
..
..
With cmdPickList
Set .ActiveConnection = cnMIS
.CommandText = "PickList" :---name of SP
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmdPickList.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdPickList.CreateParameter("@DateTo", adDBDate, adParamInput,
0)
params.Append cmdPickList.CreateParameter("@Customer", adChar, adParamInput,
7)
params.Append cmdPickList.CreateParameter("@Route", adVarChar, adParamInput,
15)
params.Append cmdPickList.CreateParameter("@OrderNo", adVarChar,
adParamInput, 8)

' Specify input parameter values
params("@DateTo") = mDateTo
params("@Customer") = mCustomer
params("@Route") = mRoute
params("@OrderNo") = morderno

' Execute the command
Set rsPickList = cmdPickList.Execute

HTH

Al
[quoted text, click to view]