ADO to pull data from Access query
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
"BillyRogers" wrote:
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
"Kevin B" wrote:
I believe our open command should reference an adCmdTable and not a
AdCmdStoredProc
rst1.open "query_name_here",,,,adCmdTable
--
Kevin Backmann
"BillyRogers" wrote:
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
|