[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Stored procedure equivalent of ADODB query/recordset

Posted on 2003-11-12
2
Medium Priority
?
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 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