• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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