=> SQL Query to collect in parameters from Access Forms

[Rhonda Fischer]

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

 

[Tom Ellison]

Dear Rhonda:

One thing that may work for you is to set a form's (or subform's)
RecordSource to the SQL you generate in code. Depending on the
effects you desire, you may be able to recover a large number of
fields in a single operation this way. In addition, you can operate
continuous forms using this technique, so that you display multiple
rows of a query as well as multiple columns, all from just one query.

It may also be a good idea if you consider using an ADP for your
project. This facility, available in Access 2000 and later, is built
around the concept of pass-thru queries to a SQL Server back end, and
shortcuts some of the steps necessary to build an application around
SQL Server (or MSDE) storage.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts