Solved

Using sp value in access vba

Posted on 2009-05-13
3
410 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
  • 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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