A Script to Create a Recordset and Import Data to a Spreadsheet
 

Hi,

I wrote a script to create a recordset and import it into Excel. This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing


End Sub

A Script to Create a Recordset and Import Data to a Spreadsheet
 

Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub


--
__________________________________
HTH

Bob

wrote in message
...
Hi,

I wrote a script to create a recordset and import it into Excel. This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing


End Sub

A Script to Create a Recordset and Import Data to a Spreadsheet
 

On Oct 29, 8:33 am, "Bob Phillips" wrote:
Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create a recordset and import it into Excel. This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

Thanks Bob. I'm still getting the same error message. Would it be
because I don't have a specific reference I need? This is what I show
for my enabled references for activex data objects:

Microsoft ActiveX Data Objects (Multi-Dimensional) 2.8 Library
Microsoft ActiveX Data Objects 2.0 Library

A Script to Create a Recordset and Import Data to a Spreadsheet
 

On Oct 29, 8:33 am, "Bob Phillips" wrote:
Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create a recordset and import it into Excel. This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

I am sure the filename and SQL is correct. I used it in the database
and it worked just fine...

A Script to Create a Recordset and Import Data to a Spreadsheet
 

On Oct 29, 9:15 am, wrote:
On Oct 29, 8:33 am, "Bob Phillips" wrote:



Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create a recordset and import it into Excel. This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

I am sure the filename and SQL is correct. I used it in the database
and it worked just fine...

I think I know what the problem is. I have OLEDB as my connection
provider. How would I substitute OLEDB as my connection provider in
this code?

A Script to Create a Recordset and Import Data to a Spreadsheet
 

You've lost me, why would replacing OLEDB with OLEDB do anything?

--
__________________________________
HTH

Bob

wrote in message
...
On Oct 29, 9:15 am, wrote:
On Oct 29, 8:33 am, "Bob Phillips" wrote:



Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection
Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM
Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create a recordset and import it into Excel.
This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

I am sure the filename and SQL is correct. I used it in the database
and it worked just fine...

I think I know what the problem is. I have OLEDB as my connection
provider. How would I substitute OLEDB as my connection provider in
this code?

A Script to Create a Recordset and Import Data to a Spreadsheet
 

On Oct 29, 1:22 pm, "Bob Phillips" wrote:
You've lost me, why would replacing OLEDB with OLEDB do anything?

--
__________________________________
HTH

Bob

wrote in message

...

On Oct 29, 9:15 am, wrote:
On Oct 29, 8:33 am, "Bob Phillips" wrote:

Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\" & _
"My Documents\Trades and Rejection
Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM
Orders" &
_
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create a recordset and import it into Excel.
This
is what I have. It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. Any ideas what is going wrong? Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

I am sure the filename and SQL is correct. I used it in the database
and it worked just fine...

I think I know what the problem is. I have OLEDB as my connection
provider. How would I substitute OLEDB as my connection provider in
this code?


Like so? I don't know, I'm shooting in the dark really. But nothing
is working...


Dim DBFullName As String
Dim Cnct As String, Src As String
Dim CONNection As OLEDBConnection
Dim RECORDset As RECORDset
Dim Col As Integer

Cells.Clear

DBFullName = "Z:\Drop Box\robin.tanner\Trades and Rejection
Data_2008-10-17.accdb "

Set CONNection = New CONNection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
CONNection.Open ConnectionString:=Cnct

Set RECORDset = New ADODB.RECORDset
With RECORDset

Src = "SELECT Order, Date, Time, Contract Size, Price FROM
Orders" & _
"WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
.Open Source:=Src, ActiveConnection:=CONNection

For Col = 0 To RECORDset.Fields.Count - 1

Range("A1").Offset(0, Col).Value = _
RECORDset.Fields(Col).Name
Next

Range("A1").Offset(1, 0).CopyFromRecordset RECORDset
End With

Set RECORDset = Nothing
CONNection.Close
Set CONNection = Nothing

End Sub

A Script to Create a Recordset and Import Data to a Spreadsheet
 

On Oct 29, 3:54*pm, wrote:
On Oct 29, 1:22 pm, "Bob Phillips" wrote:





You've lost me, why would replacing OLEDB with OLEDB do anything?

--
__________________________________
HTH

Bob

wrote in message

....

On Oct 29, 9:15 am, wrote:
On Oct 29, 8:33 am, "Bob Phillips" wrote:

Try this

Sub Retrieve_AccessData()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
DimRecordsetAs ADODB.Recordset
Dim Col As Integer

* * Cells.Clear

* * DBFullName = "C:\Documents and Settings\robin.grossman\" & _
* * * * * * * * *"My Documents\Trades and Rejection
Data_2008-10-17.accdb "

* * Set Connection = New ADODB.Connection
* * Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
* * Cnct = Cnct & "Data Source=" & DBFullName & ";"
* * Connection.Open ConnectionString:=Cnct

* * SetRecordset= New ADODB.Recordset
* * WithRecordset

* * * * Src = "SELECT Order, Date, Time, Contract Size, Price FROM
Orders" &
_
* * * * * * * "WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
* * * * .Open Source:=Src, ActiveConnection:=Connection

* * * * For Col = 0 ToRecordset.Fields.Count - 1

* * * * * * Range("A1").Offset(0, Col).Value = _
* * * * * * * *Recordset.Fields(Col).Name
* * * * Next

* * * * Range("A1").Offset(1, 0).CopyFromRecordsetRecordset
* * End With

* * SetRecordset= Nothing
* * Connection.Close
* * Set Connection = Nothing

End Sub

--
__________________________________
HTH

Bob

wrote in message

...

Hi,

I wrote a script to create arecordsetand import it into Excel.
This
is what I have. *It is telling me the following Error:

"Method of Open object "_Recordset" failed."

Here is the code. *Any ideas what is going wrong? *Thanks in advance.

Sub Retrieve_AccessData()

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
DimRecordsetAs ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "C:\Documents and Settings\robin.grossman\My Documents
\Trades and Rejection Data_2008-10-17.accdb "

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

SetRecordset= New ADODB.Recordset
WithRecordset
* *Src = "SELECT Order, Date, Time, Contract Size, Price FROM Orders
"
* *Src = Src & "WHERE Date (Now()-42) ORDER BY Date, Time;"
* *.Open Source:=Src, ActiveConnection:=Connection

* *For Col = 0 ToRecordset.Fields.Count - 1
* * * *Range("A1").Offset(0, Col).Value = _
* * * * * *Recordset.Fields(Col).Name
* *Next

* *Range("A1").Offset(1, 0).CopyFromRecordsetRecordset
End With

SetRecordset= Nothing
Connection.Close
Set Connection = Nothing

End Sub

I am sure the filename and SQL is correct. *I used it in the database
and it worked just fine...

I think I know what the problem is. *I have OLEDB as my connection
provider. *How would I substitute OLEDB as my connection provider in
this code?

Like so? *I don't know, I'm shooting in the dark really. *But nothing
is working...

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim CONNection As OLEDBConnection
DimRECORDsetAsRECORDset
Dim Col As Integer

* * Cells.Clear

* * DBFullName = "Z:\Drop Box\robin.tanner\Trades and Rejection
Data_2008-10-17.accdb "

* * Set CONNection = New CONNection
* * Cnct = "Provider=Microsoft.Ace.OLEDB.12.0;"
* * Cnct = Cnct & "Data Source=" & DBFullName & ";"
* * CONNection.Open ConnectionString:=Cnct

* * SetRECORDset= New ADODB.RECORDset
* * WithRECORDset

* * * * Src = "SELECT Order, Date, Time, Contract Size, Price FROM
Orders" & _
* * * * * * * "WHERE Date " & Date - 42 & " ORDER BY Date, Time;"
* * * * .Open Source:=Src, ActiveConnection:=CONNection

* * * * For Col = 0 ToRECORDset.Fields.Count - 1

* * * * * * Range("A1").Offset(0, Col).Value = _
* * * * * * * *RECORDset.Fields(Col).Name
* * * * Next

* * * * Range("A1").Offset(1, 0).CopyFromRecordsetRECORDset
* * End With

* * SetRECORDset= Nothing
* * CONNection.Close
* * Set CONNection = Nothing

End Sub- Hide quoted text -

- Show quoted text -

In this link, http://support.microsoft.com/kb/306125, it says to use
the reference for Microsoft ActiveX Data Objects Library.