Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Passing arguments to a passthrough query

Posted on 2008-06-11
11
Medium Priority
?
791 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
[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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

670 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