Export Data w/FSO - Open object recordset failed

[JayZoll]

Hello,
***The text in the SQL is case sensitive, the field names in my table were lower case, in the VBA SQL each were capitalized***

Please help! I am trying to export data using file system object and receive the following error:

Run-time error '-2147467259 (80004005)'"
Method 'Open' of object '_Recordset' Failed

The routine writes a blank .txt file to my desktop. When debugging, it fails rs.Open line. The query is valid and works in query window.

References selected: Visual Basic For Applications, Microsoft Access 11.0 Object Library, OLE Automation, Microsoft DAO 3.6 Object Library, Microsoft ActiveX Data Objects 2.1 Library, Microsoft Scripting Runtime



MS Access Version: 2003 Professional Edition (11.6566.8117) SP2

The "NASDAQ" table:

date	open	close
2006-01-03	10718	10809
2005-12-01	10806	10717
2005-11-01	10569	10440

 Sub XPORT()

Dim fso As FileSystemObject
Dim txt As TextStream
Dim rs As ADODB.Recordset
Dim s As String

Set fso = New FileSystemObject
Set txt = fso.CreateTextFile("C:\Documents and Settings\xjp1pyu\Desktop\xportit.txt", True)

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "SELECT Date, Open, Close FROM NASDAQ", , adOpenForwardOnly, adLockReadOnly

txt.WriteLine "Date, Open, Close"
Do While Not rs.EOF
    s = """" & FormatDateTime(rs("Date"), vbShortDate) & """, "
    s = s & FormatNumber(rs("Open"), 2, vbFalse, vbFalse, vbFalse) & ", "
    s = s & FormatNumber(rs("Close"), 2, vbFalse, vbFalse, vbFalse)
    txt.WriteLine s
    
    rs.MoveNext

Loop

rs.Close
txt.Close

End Sub

[June7]

This is resolved? Want to share the solution?

The rs.Open is missing the connection argument parameter.