?
Solved

Stored procedure equivalent of ADODB query/recordset

Posted on 2003-11-12
2
Medium Priority
?
288 Views
Last Modified: 2006-11-17
I want to create a stored procedure to replace the following VBA in an Access form:


**********************************************

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

Dim SQL
SQL = "SELECT col1, col2, col3  FROM [table] WHERE col0 = [somevalue]"

rs.Open SQL, cn, 1,3,1

MsgBox ("Column 1 data is: " & rs("col1"))

**********************************************


I also need to know how to access the stored procedure via VBA.  Can someone please take the above example and (1) convert the code so it's valid in a stored procedure; and (2) provide an example of the VBA code which will call this stored procedure and assign values to variables based on what is returned.

Thanks!
0
Comment
Question by:FunkiNATEr
2 Comments
 
LVL 3

Expert Comment

by:monvelasquez
ID: 9738029
in SQL

--[CODE]-------------------------------------------------------
Create Procedure sp_QueryData
AS
   SELECT col1, col2, col3  FROM [table] WHERE col0 = somevalue
--[/CODE]-------------------------------------------------------

in VB

--[CODE]-------------------------------------------------------
Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

Dim SQL
SQL = "execute sp_QueryData"

rs.Open SQL, cn, 1,3,1

MsgBox ("Column 1 data is: " & rs("col1"))

--[/CODE]------------------------------------------------------
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 9740532
I would modify somewhat the otherwise excellent suggestion by monvelasquez.

I suspect that [somevalue] is a variable in which case make the following changes to the stored procedure:

Create Procedure sp_QueryData (@SomeValue varchar(50)) --Change to some other character data type as appropriate.

AS
   SELECT col1, col2, col3  FROM [table] WHERE col0 = @SomeValue

And the VBA code as follows:

SQL = "execute sp_QueryData 'somecharactervalue'"

Here I am making the assumption that it is character data, if it is numeric than make the following changes:

Create Procedure sp_QueryData (@SomeValue int) --Change to some other numeric data type as appropriate.

AS
   SELECT col1, col2, col3  FROM [table] WHERE col0 = @SomeValue

And the VBA code as follows:

SQL = "execute sp_QueryData somenumericvalue"

Anthony
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question