Solved

Stored procedure equivalent of ADODB query/recordset

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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