I am trying to create a stored procedure that accepts paramters, then base 
a form on it, all in code using Access 2000 and SQL 7.

I have a table: T_CellNames with 5 fields, the searched field name is: 
CellID which is a string.

I want to make a stored procedure that will return a recordset based on an 
entry to a text box.  The user will either enter the exact string for the 
CellID that they are looking for or a partial string (for multiple 
records) and then hit a "find button."  At that time I want to pass the 
entered string to the stored procedure then base my form on that record 
set.

I would like to do it all in ADO.

The server is Mercury and the user and pasword is PMUSER, PMUSER

I have gotten close but I can't seem to pull it all together.

1. What is the code for the stored procedure?
2. How do I pass the search string?
3. How do I base the form on the returned recordset?
4. Is that recordset updateable?  I might need to update the record(s).

Thanks Mitch

Michael,

I have found an excellent reference to this subject.  I'm faced with 
the
same general problem.

The reference is:

Microsoft Access Developer's Guide to SQL Server
Mary Chipman and Andy Baron
Chapter 10 (p444 - 495)

It includes all the code you need for both parameterized and
non-parameterized stored procedures and pass through queries.

I would recommend this book to any Access developer trying to puzzle 
out SQL
Server for the first time.

Rand E. Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston, IL  60201
(xxx) xxx-xxxx

-----Original Message-----
From: Michael Mitchell [mailto:michael@m...]
Sent: Saturday, February 22, 2003 09:46
To: Access
Subject: [access] Stored Procedure with Parameters used by Form

I am trying to create a stored procedure that accepts paramters, then 
base
a form on it, all in code using Access 2000 and SQL 7.

I have a table: T_CellNames with 5 fields, the searched field name is:
CellID which is a string.

I want to make a stored procedure that will return a recordset based on 
an
entry to a text box.  The user will either enter the exact string for 
the
CellID that they are looking for or a partial string (for multiple
records) and then hit a "find button."  At that time I want to pass the
entered string to the stored procedure then base my form on that record
set.

I would like to do it all in ADO.

The server is Mercury and the user and pasword is PMUSER, PMUSER

I have gotten close but I can't seem to pull it all together.

1. What is the code for the stored procedure?
2. How do I pass the search string?
3. How do I base the form on the returned recordset?
4. Is that recordset updateable?  I might need to update the record(s).

Thanks Mitch

Dear Rand:

Please accept my profound apologies for shamelessly plagiarizing
Chipman and Baron's extremely helpful book instead of pointing you
to it immediately. I could have saved you some time. Other works I'm 
currently drawing from include:

Ken Henderson's  "The Guru's Guide to Transact-SQL" and "The Guru's
  Guide to SQL Server, Stored Procedures, XML, and HTML"
Rick Dobson's "Profesional SQL Server Development with Access 2000"
Russell Sinclair's "From Access to SQL Server"
Susan Harkin & Martin Reid "SQL: Access to SQL Server"

Chipman and Baron's is, I feel, the best if your focus us ADPs.

Again, my apologies to you and to the group, as well as my assurance of
more generous source citing going forward.

Best,

Bob



>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Stored Procedure with Parameters used by Form
>Date: Mon, 24 Feb 2003 11:15:28 -0600
>
>Michael,
>
>I have found an excellent reference to this subject.  I'm faced with the
>same general problem.
>
>The reference is:
>
>Microsoft Access Developer's Guide to SQL Server
>Mary Chipman and Andy Baron
>Chapter 10 (p444 - 495)
>
>It includes all the code you need for both parameterized and
>non-parameterized stored procedures and pass through queries.
>
>I would recommend this book to any Access developer trying to puzzle out 
>SQL
>Server for the first time.
>
>Rand E. Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston, IL  60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Michael Mitchell [mailto:michael@m...]
>Sent: Saturday, February 22, 2003 09:46
>To: Access
>Subject: [access] Stored Procedure with Parameters used by Form
>
>I am trying to create a stored procedure that accepts paramters, then base
>a form on it, all in code using Access 2000 and SQL 7.
>
>I have a table: T_CellNames with 5 fields, the searched field name is:
>CellID which is a string.
>
>I want to make a stored procedure that will return a recordset based on an
>entry to a text box.  The user will either enter the exact string for the
>CellID that they are looking for or a partial string (for multiple
>records) and then hit a "find button."  At that time I want to pass the
>entered string to the stored procedure then base my form on that record
>set.
>
>I would like to do it all in ADO.
>
>The server is Mercury and the user and pasword is PMUSER, PMUSER
>
>I have gotten close but I can't seem to pull it all together.
>
>1. What is the code for the stored procedure?
>2. How do I pass the search string?
>3. How do I base the form on the returned recordset?
>4. Is that recordset updateable?  I might need to update the record(s).
>
>Thanks Mitch
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail