ADO to pull data from Access query

[BillyRogers]

I'm trying to connect to an Access database and pull the data from an access
query into a resordset and copy that recordset into excel.

This code stops when the connection tries to open. I have several
spreadsheets where I do this with SQL Server, but can't seem to get it right
when connecting to Access.



Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Documents
and Settings\brogers\Desktop\Brad and Mary's DB.mdb;")

Dim cmd As New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "[S_08]" ' this is the name of the Access query
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.CommandTimeout = 0

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.Open (cmd) '''''''''''''''''''''''''''' this is where the
code stops


Worksheets("Sheet1").Range("A2").CopyFromRecordset rst1


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Kevin B]

I believe our open command should reference an adCmdTable and not a
AdCmdStoredProc

rst1.open "query_name_here",,,,adCmdTable

 

[BillyRogers]

Here's what I finally got to work.

SQLcmd = "SELECT * FROM [S 08]"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Documents and Settings\brogers\Desktop\Brad and Mary's DB.mdb" + _
"; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate
'*************

ActiveCell.CopyFromRecordset rs
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

I believe our open command should reference an adCmdTable and not a
AdCmdStoredProc

rst1.open "query_name_here",,,,adCmdTable

--
Kevin Backmann

I'm trying to connect to an Access database and pull the data from an access
query into a resordset and copy that recordset into excel.

This code stops when the connection tries to open. I have several
spreadsheets where I do this with SQL Server, but can't seem to get it right
when connecting to Access.



Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Documents
and Settings\brogers\Desktop\Brad and Mary's DB.mdb;")

Dim cmd As New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "[S_08]" ' this is the name of the Access query
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.CommandTimeout = 0

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.Open (cmd) '''''''''''''''''''''''''''' this is where the
code stops


Worksheets("Sheet1").Range("A2").CopyFromRecordset rst1


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[BillyRogers]

Here's another version that also paste the fieldnames.

SQLcmd = "SELECT * FROM [Query Name Here]"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim fld As ADODB.Field
Dim Row As Integer
Dim Column As Integer


rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data
Warehouse\Dallas\Brad and Mary's DB\Brad and Mary's DB.mdb" + _
"; User Id=admin; Password="


Column = 1
Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name

Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

Here's what I finally got to work.

SQLcmd = "SELECT * FROM [S 08]"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Documents and Settings\brogers\Desktop\Brad and Mary's DB.mdb" + _
"; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate
'*************

ActiveCell.CopyFromRecordset rs
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

I believe our open command should reference an adCmdTable and not a
AdCmdStoredProc

rst1.open "query_name_here",,,,adCmdTable

--
Kevin Backmann

I'm trying to connect to an Access database and pull the data from an access
query into a resordset and copy that recordset into excel.

This code stops when the connection tries to open. I have several
spreadsheets where I do this with SQL Server, but can't seem to get it right
when connecting to Access.



Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Documents
and Settings\brogers\Desktop\Brad and Mary's DB.mdb;")

Dim cmd As New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "[S_08]" ' this is the name of the Access query
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.CommandTimeout = 0

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.Open (cmd) '''''''''''''''''''''''''''' this is where the
code stops


Worksheets("Sheet1").Range("A2").CopyFromRecordset rst1


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003