Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO to pull data from Access query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO to pull data from Access query
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO to pull data from Access query
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DSUM to pull data from Access DB | Excel Worksheet Functions | |||
Applying Criteria in Excel Cell to MS Query Pull from Access | Excel Discussion (Misc queries) | |||
Microsoft Query - pull data from Access "Too many parameters" | Excel Programming | |||
Can excel pull data out of access? | Setting up and Configuration of Excel | |||
How To Pull Access Query into Excel (2003): Reposting | Excel Programming |