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

[nouveauricheinvestments]

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

 

[Bob Phillips]

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

 

[nouveauricheinvestments]

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

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

 

[nouveauricheinvestments]

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

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

 

[nouveauricheinvestments]

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?

 

[Bob Phillips]

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

 

[nouveauricheinvestments]

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

--
__________________________________
HTH

Bob

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

 

[Andrew]

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.