Currently I have been using Access as the front end and storing the data in SQL server, however I am looking to increase the speed of the database by having the SQL Server do more of the work. The main task of the database it to take text from a text box on a form and use this to limit the results returned from a query. I thought that maybe if I could create a stored procedure to run the actual query and just have the procedure return the data to the user in Access, but I have had trouble telling Access to pass the data from the text box through to SQL. I'm using a SQL Passthrough query to call the stored procedure but have been unable to tell it to use the text box as a parameter

The first question I have is am I going about this the correct way or is there an easier way to complete my task. Second, provided I am going about it the correct way how do I pass the text in my text box through to the stored procedure.

I am very good in Access and fairly knowledgeable in SQL but am new to using stored procedure.

Any help would be appreciated.

I've posted this before, but you may find it useful. It uses ADOX to dynamically set parameters for stored procedures called by pass-through queries.

The following executes a parameterized stored procedure against the SQL Server version of the Northwind database using a DSN-less ODBC connection. The query returns a single employee record using EmployeeID as criteria.

First the pass-through query get's built, then a stored procedure gets created on SQL Server which the pass-through query will execute, then some code gets written to re-build the pass-through query everytime a new employee record is requested.

The ExecutePassThrough sub recieves an Employee ID number as an argument and concatenates it to the Pass-Through query's SQL. BuildPassThrough is then called which uses ADOX to reset the pass-through query's command text.

================================================== =======
Design the Pass-Through Query in Access (.mdb)
================================================== =======

1. In Query design view, from the main menu select Query | SQL Specific | Pass-Through.

2. Type "EXEC up_parmsel_Employees" as the query's SQL statement.

3. Click the properties button on the Toolbar to open the Query properties dialog. Enter the following DSN-less connection string in the ODBC Connect Str property:

ODBC;Driver={SQL Server};Server=(local); _
Database=Northwind;Trusted_Connection=Yes

NT authentication is used here.

All the other default properties are fine. Clicking the ellipsis in the ODBC Connect Str property field will open the DSN Data Source dialog if you want to use a DSN.

4. Save the query as "qsptEmployees" and close without running (the stored procedure doesn't exit yet).

================================================== =======
Create the Stored Procedure in SQL Server 2000
================================================== =======

1. With SQL Server running open the Query Analyzer.

2. Select the Northwind database form the Toolbar's drop-down list.

3. Type the following SQL in the Query Analyzer, parse, and run:

CREATE PROC up_parmsel_Employees
   @EmployeeID int = NULL
   AS
   SELECT EmployeeID, LastName, FirstName, Title
   FROM Northwind.dbo.Employees
   WHERE EmployeeID = @EmployeeID

4. Test that the proc was created successfully by clearing the Query Analyzer window and running the following command in the Query analyzer:

Exec up_parmsel_Employees 1

A single employee record for "Nancy Davolio" should be displayed. The query and the proc it executes are now ready to go. Just need code to set the parameter that the proc expects.

================================================== =======
 Write the Parameter Setting Module in Access
================================================== =======

1. Open a new module in Access and set a Reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security) library.

2. Paste in the following two subs:

Public Sub ExecutePassThrough(lngParam As Long)
   Dim strTSQL As String
   Dim strQueryName As String

   strQueryName = "qsptEmployees"
   strTSQL = "EXEC up_parmsel_Employees " & lngParam

   Call BuildPassThrough(strQueryName, strTSQL)

   DoCmd.OpenQuery strQueryName
End Sub

Public Sub BuildPassThrough( _
   ByVal strQName As String, _
   ByVal strSQL As String)

   Dim cat As ADOX.Catalog
   Dim cmd As ADODB.Command

   Set cat = New ADOX.Catalog
   Set cat.ActiveConnection = CurrentProject.Connection

   Set cmd = cat.Procedures(strQName).Command

   ' Verify query is a pass-through query
   cmd.Properties( _
      "Jet OLEDB:ODBC Pass-Through Statement") = True

   cmd.CommandText = strSQL

   Set cat.Procedures(strQName).Command = cmd

   Set cmd = Nothing
   Set cat = Nothing

End Sub

================================================== =======
 Test the Procedures, Resetting the Queries Parameters
================================================== =======

1. In the immediate window, type the following and hit Enter:

ExecutePassThrough (1)

2. A query should open in datasheet view displaying Nancy Davolio's employee record.

Or you could return the result set to an ADO recordset with:

Set rst = New ADODB.Recordset
rst.Open strQueryName, CurrentProject.AccessConnection

You could use the following syntax:

lngParam = [Forms]![frmSearch]![txtSearchValue]
Call ExecutePassThrough(lngParam)

to set the whole thing in motion.

HTH,

Bob

Thanks for the help Bob. However when I try to run the code I get an error at the

 Dim cmd As ADODB.Command

line.

I have the Microsoft ADO Ext. 2.7 for DDL and Security library selected but it still errors on me. Am I doing something wrong?

Hi npepin,

The Microsoft ActiveX Data Objects 2.x Library (ADO) and the Microsoft ADO Ext. 2.7 for DDL and Security Library (ADOX) are two seperate object libraries. The former contains data manipulation objects; the later, data definition and security objects.

The ADO Command object lives in the ADO 2.x library. If you can't declare one, that could only be because you don't have a reference set to the ADO 2.x library (barring something really weird, of course).

HTH,

Bob

Worked great. Thanks for the help

Nathan