Retrieve data by Self join via SQL under MS access VBA

[mark]

Hi,

I was confused by this issue for awhile. Any comments will be highly
appreciated!!!

I have a tabel (which is linked to another back-end mdb file in harddisk)
with several fields and I'd like to retrieve sorted data to an Excel workbook
via ADO + SQL
Part of the code is:

Dim mySQL As String
Dim myrecordset As New ADODB.Recordset

"SELECT Output.Dayin, Output.Shiftin, Output.Sequence, Output.Die, Output.
Diameter, Output.Machine, Output.Employeein, Output_1.Die, Output_1.Diameter,
Output.Meterin FROM [Output] INNER JOIN ([Output] AS Output_1) ON (Output.
Dayin=Output_1.Dayout) AND (Output.Meterin=Output_1.Meterout) AND (Output.
Shiftin=Output_1.Shiftout) AND (Output.Machine=Output_1.Machine) AND (Output.
Employeein=Output_1.Employeeout)"

DoCmd.SetWarnings False
myrecordset.Open mySQL, CurrentProject.Connection, adOpenDynamic
DoCmd.SetWarnings True
Dim myExcel As Excel.Application
Dim myBook As Excel.Workbook
Dim mysheet As Excel.Worksheet

Set myExcel = New Excel.Application
myExcel.Visible = False
Set myBook = myExcel.Workbooks.Add
Set mysheet = myBook.Worksheets(1)
mysheet.Range("a2").CopyFromRecordset myrecordset
myrecordset.Close

when I run the macro, an warning message pops up which reads "Run-time error
'-2147467259' (80004005): method 'open' of object '_Recordset' failed"

Strangely, I created a report and set the recordsource with the same SQL
string as following. However, it works!!!
Is Access report and Access VBA work differently regarding SQL please???
Thanks guys!

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT Output.Dayin, Output.Shiftin, Output.Sequence,
Output.Die, Output.Diameter, Output.Machine, Output.Employeein, Output_1.Die,
Output_1.Diameter, Output.Meterin FROM [Output] INNER JOIN ([Output] AS
Output_1) ON (Output.Dayin=Output_1.Dayout) AND (Output.Meterin=Output_1.
Meterout) AND (Output.Shiftin=Output_1.Shiftout) AND (Output.Machine=Output_1.
Machine) AND (Output.Employeein=Output_1.Employeeout)"
End sub

 

[marklisle via AccessMonster.com]

Help please!