Passing arguments to a passthrough query

I found an example out there that might help me but it requires ADODB.

Do I need ADODB to do this?

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

Select allOpen in new window

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" ???

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

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
 

I have the opposite problem.  I've already got the reference for  DAO, I can't find the one for ADODB.

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.

... Forgot to add, before Access 2005 the reference to ADODB should already be there.

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.

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.