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.