Solved

Passing arguments to a passthrough query

Posted on 2008-06-11
11
760 Views
Last Modified: 2013-12-25
My question is similar to question 21687496 but not exactly the same.  What I'm trying to do is actually simpler but I'm still having trouble.

All I want to do is execute a stored procedure that inserts one row in a table using a passthrough query.  The SP has one parameter that must be passed in and an integer return code that must be passed back, indicating whether it succeeded or failed.

The examples only confuse me because they are doing different things and not passing back a return value.

If somebody could post the passthrough query as well as the VBA code I would be very appreciative.

Thanks.
0
Comment
Question by:Eric_Trogdon
  • 6
  • 5
11 Comments
 

Author Comment

by:Eric_Trogdon
ID: 21764020
I found an example out there that might help me but it requires ADODB.

Do I need ADODB to do this?
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21767506
Eric,
Getting the return code via a Pass Through query will be the show-stopper, I've never seen a method to do it. Also, the question you cited distilled down to adding the parameter to the pass-through sql by string concatenation, so the only benefit of using a query seems to be that it's somewhere to store the connection string.
The attached snippet runs the SP via ADO. It stores the connection as a module level variable so that it needs to be opened only once.

Private mcn As ADODB.Connection

Public Function ADOConnection() As ADODB.Connection

    If mcn Is Nothing Then

        Set mcn = New ADODB.Connection

    End If

    If mcn.State = adStateClosed Then

        mcn.ConnectionString = "DRIVER={SQL Server};SERVER=MyServer;Trusted_Connection=Yes;DATABASE=SandPit"

        mcn.Open

    End If

    Set ADOConnection = mcn

End Function
 

' Runs Stored Proc via ADO

Public Function ExecSP(parm As Integer) As Integer

    Dim cn As ADODB.Connection

    Dim cmd As New ADODB.Command

    With cmd

        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("RC", adInteger, adParamReturnValue)

        .Parameters.Append .CreateParameter("P1", adInteger, adParamInput)

        .Parameters!P1 = parm

        .CommandText = "dbo.TestIt"

        Set .ActiveConnection = ADOConnection()

        .Execute

        ExecSP = .Parameters!RC

    End With

End Function

Open in new window

0
 

Author Comment

by:Eric_Trogdon
ID: 21774047
If I understand you correctly you are saying I can do what I need to do using an ADODB connection instead of a pass-through query.  Is that correct?

If so I have a follow up question.  What do I need to do get Access 2003 to recognize ADODB objects?

If I add a line that say's:

Dim cn as ADODB.Connection

I get a compile error that says "user defined type not defined" ???
0
 

Author Comment

by:Eric_Trogdon
ID: 21774184
A few more follow up questions.

1. Can I use DAO instead of ADODB?

2. My compiler doesn't like:

"Private mcn As DAO.Connection"

outside of a sub; but it also doesn't like:

"Set mcn = New DAO.Connection"

if I put

"Dim mcn As DAO.Conneciton"  

inside the sub.  Any suggestions?

3. I already have my connection string stored in a file DSN.  Is there a way to reference the file DSN in the code rather than hard coding the connection string?

Thanks for your help.  I'm getting closer ...
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21774236
If you want to use DAO then you need to add a reference, =go to the tools menu and choose References... your can then add a refernce to MicrosoftD DAO
 
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Eric_Trogdon
ID: 21780420
I have the opposite problem.  I've already got the reference for  DAO, I can't find the one for ADODB.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21781230
If you're using Access 2005 you need to add a reference to ADO, in your code project choose References from the Tools menu and check the box next to Microsoft ActiveX Data Objects - there may be several of these, choose the latest version.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21781241
... Forgot to add, before Access 2005 the reference to ADODB should already be there.
0
 

Author Comment

by:Eric_Trogdon
ID: 21781596
Thanks.  I added the ADO reference and got the code to compile with one change.

I know I'm pushing my luck but I have three more follow-up questions.

1.  I had to change "Private mcn As ADODB.Connection" to "Dim mcn As ADODB.Connection" and put it inside the ADOConnection function.  Is this what you meant?  The compiler will not accept "Private mcn As ADODB.Connection".

2. Dumb question perhaps, but where does the stored procedure name go?

3. Can I substitute a file dsn for the hard coded connection string?

Thanks for your help.

Sorry about the multitude of questions.  I'll stop now.

0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 21788360
1. The purpose of declaring mcn at the module level is so that it remains in existence and doesn't need to be reopened each time it's used. If you Dim it inside AdoConnection() it will be deleted by VB as soon as the last reference to the object is released - which will be on exit from ExecSP - and then need to be recreated/reopened next time it's needed. If this isn't a performance hit for you then it doesn't matter and you can carry on.
It compiled fine for me - what's the message from the compiler?

2. It's asssigned in the CommandText parameter:
             .CommandText = "dbo.TestIt"

3. I believe that would be done this way:
        mcn.ConnectionString = "FileDSN =C:\...Yourpath...\YourFile.dsn"

By the way, what version of Access are you using?
0
 

Author Closing Comment

by:Eric_Trogdon
ID: 31466283
Thanks!  I've got it working now.  The reason I was getting the compile error was because I had the code under the form instead of in a module.  I created a new module and stuck it there and the error went away.  Good thing you included the word "module" in your note.  That's what clued me in.  I haven't done much with modules (yet), since I've always been able to do everything I wanted inside the forms (until now).

To answer your other question I am running Access 2003 SP1.

I am very happy with your answers to all my questions.  If I could give you extra points I would, since you actually answered a whole series of questions; but I don't see a way I can do that.

Thanks again.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 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

21 Experts available now in Live!

Get 1:1 Help Now