Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

Passing arguments to a passthrough query

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
Eric_Trogdon
Asked:
Eric_Trogdon
  • 6
  • 5
1 Solution
 
Eric_TrogdonAuthor Commented:
I found an example out there that might help me but it requires ADODB.

Do I need ADODB to do this?
0
 
MikeTooleCommented:
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
 
Eric_TrogdonAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eric_TrogdonAuthor Commented:
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
 
MikeTooleCommented:
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
 
Eric_TrogdonAuthor Commented:
I have the opposite problem.  I've already got the reference for  DAO, I can't find the one for ADODB.
0
 
MikeTooleCommented:
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
 
MikeTooleCommented:
... Forgot to add, before Access 2005 the reference to ADODB should already be there.
0
 
Eric_TrogdonAuthor Commented:
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
 
MikeTooleCommented:
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
 
Eric_TrogdonAuthor Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now