Accessing a Stored Procedure

[Chad]

Hi All,

I am just starting to get into Access and Excel. I have worked with a
couple of access databases, but I am now working with an access database
that uses stored procedures and I am stuck.

I was using Microsoft Query to get my data, but it doesn't seem to work with
stored procedures. Could someone lead me in the right direction to connect
to the DB and then access a stored procedure.

Thanks,
Chad

 

[Chad]

Looking at other samples, they all seem to start out like:

Dim c As adodb.Connection
Set c = New adodb.Connection

But I get an error saying "user defined type not defined" on the first line

 

[Bob Phillips]

Yu need to set a reference in the VBIDE (Tools>references) to Microsoft
ActiveX Data Objects 2.n Library

 

[Chad]

Thanks Bob,

It appears I am getting in way over my head. But lets keep going anyways.

So I got this part

Dim c As adodb.Connection
Set c = New adodb.Connection

So now I need to open it I am guessing. But I don't know how. It is an
Access 2000 database with stored procedures in it. I hope this helps. In
the database connection menu, it says:
1. MP_SAP
2. Use WinNT integrated security
3. Select the database = MineOperations

When I view the stored porcedure the name is Chad (dbo)

Could you please point me in the right direction again.

Thanks
Chad

 

[Bob Phillips]

Chad,

This is some pure VBA to access an Access SP, adapt for your query


Dim c As ADODB.Connection
Dim RS As ADODB.Recordset

Set c = New ADODB.Connection

c.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\bob.mdb"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = c

cmd.CommandText = "Chad"
cmd.CommandType = adcmdStoredProc

Set RS = CreateObject("ADODB.Recordset")
RS.Open cmd

Do Until RS.EOF
Debug.Print RS(0), RS(1), RS(2)
RS.MoveNext
Loop

Set RS = Nothing
Set cmd = Nothing
Set c = Nothing

 

[Chad]

My database isn't a *.mdb. It's a *.adp.
I get an unrecognized database format error

I do have a ODBC Data Source linked to it though.

 

[Bob Phillips]

What is adp, I don't think I have come across that before? But presumably if
it has stored procedures you can set up a connection, with a different
connection string to that that I gave you and run the code?