?
Solved

Using sp value in access vba

Posted on 2009-05-13
3
Medium Priority
?
415 Views
Last Modified: 2013-12-05
I have a stored procedure in SQL Server 2005 that return a value,I  want to use that value the access VBA 2007 without using ADO. Is is possible? or there is no way I should use ADO.
0
Comment
Question by:Yadtrt
[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
3 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24379197
Returns a value how?

If you return it as a recordset (i.e. SELECT the value rather than return it) then you could use the Access Application object to return it - but you'd be implementing default ADO methods from that application (so not circumventing ADO).

If you simply return the value then you'll not necessarily even get that value at all!

Ideally you'd return it as an Output parameter - but you will then need be using ADO explicitly to retrieve that value.
0
 
LVL 6

Author Comment

by:Yadtrt
ID: 24379300
I want to return single value just like this procedure,

create procedure testD
@nVal int
@Dval int output
as
begin
If EXISTS (select Ncol from Table1 where F1=@Nval)
set @Dval=2
end
 

How can I use the @Dval value in the Access VBA
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 24379445
By using ADO.  
To be fair, IMO, if you're going to work in ADPs then you need to be familiar with ADO.
That's less true for DAO in MDBs - but I feel you're tying one hand behind your back by avoiding it.
OK - maybe two hands. ;-)

    Dim cmd As ADODB.Command
 
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "testD"
        
        .Parameters.Append .CreateParameter("@nVal", adInteger, adParamInput, 4, intSomeValue)
        .Parameters.Append .CreateParameter("@Dval", adInteger, adParamOutput, 4)
        .Execute
        
        'Grab the output parameter value
        Debug.Print .Parameters("@Dval")
    End With
    
    Set cmd = Nothing

Open in new window

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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