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