Accessing a Stored Procedure
 

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

Accessing a Stored Procedure
 

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

"Chad" wrote in message
...
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

Accessing a Stored Procedure
 

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

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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

"Chad" wrote in message
...
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

Accessing a Stored Procedure
 

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" wrote in message
...
Yu need to set a reference in the VBIDE (Toolsreferences) to Microsoft
ActiveX Data Objects 2.n Library

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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

"Chad" wrote in message
...
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

Accessing a Stored Procedure
 

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


--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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" wrote in message
...
Yu need to set a reference in the VBIDE (Toolsreferences) to Microsoft
ActiveX Data Objects 2.n Library

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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

"Chad" wrote in message
...
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

Accessing a Stored Procedure
 

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" wrote in message
...
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


--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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" wrote in message
...
Yu need to set a reference in the VBIDE (Toolsreferences) to Microsoft
ActiveX Data Objects 2.n Library

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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

"Chad" wrote in message
...
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

Accessing a Stored Procedure
 

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?

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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" wrote in message
...
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


--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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" wrote in message
...
Yu need to set a reference in the VBIDE (Toolsreferences) to Microsoft
ActiveX Data Objects 2.n Library

--
__________________________________
HTH

Bob

"Chad" wrote in message
...
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

"Chad" wrote in message
...
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