Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,420
Default 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





  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default 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







  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,420
Default 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











  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default 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











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wscript and stored procedure Ralf Meuser Excel Programming 0 June 9th 08 10:11 AM
Accessing Arrays stored as a Name [email protected] Excel Programming 2 May 3rd 07 07:30 PM
Executing Stored Procedure Jenise Excel Programming 1 October 6th 06 03:10 AM
accessing an address stored in another cell SteveR Excel Worksheet Functions 11 August 20th 05 07:50 AM
execute stored procedure Mark Goldin Excel Programming 3 April 7th 04 03:03 PM


All times are GMT +1. The time now is 12:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"