Solved

Stored procedure equivalent of ADODB query/recordset

Posted on 2003-11-12
2
275 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 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now