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?