ADO code error (Join Expression Not Supported) HELP PLEASE

[Phutile]

What is wrong with this ADO code? It says "Join Expression Not Supported". Can someone look at my code and tell me what I might be doing wrong? Thanks!

Private Sub Submit_Click()
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

'Database Info
DBFullName = ThisWorkbook.Path & "\APAD01query.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create Recordset
Set Recordset = New ADODB.Recordset
With Recordset

'SQL
Dim sStartDate As String
Dim sEndDate As String
Dim sDept As String

sStartDate = #3/1/2003#
sEndDate = #3/6/2003#
sDept = "180"


Src = "SELECT APAD_APAD_DETAILS.APAD_DATE_CMPLT AS [Date], APAD_APAD_DETAILS.APAD_DEPT AS Dept,"
Src = Src & "APAD_APAD_DETAILS.APAD_OPER AS Opn, APAD_APAD_DETAILS.APAD_GRADE AS Grade,"
Src = Src & "APAD_APAD_DETAILS.APAD_PROD AS Prd, APAD_APAD_DETAILS.APAD_SIZE AS [Size],"
Src = Src & "APAD_APAD_DETAILS.APAD_MI AS MI, IIf([APAD_APAD_DETAILS]![APAD_PROD_GROUP]='901',"
Src = Src & "'Z',[RAWMATL_RM_GRADE].[BASE_METAL]) AS BMT, APAD_APAD_DETAILS.APAD_MOT AS MOT,"
Src = Src & "APAD_APAD_DETAILS.APAD_HEAT AS Heat, APAD_APAD_DETAILS.APAD_WT_IN AS [Act LBs In],"
Src = Src & "APAD_APAD_DETAILS.APAD_WT_OUT AS [Act LBs Out], APAD_APAD_DETAILS.APAD_WT_OUT_STD AS [Std LBs Out],"
Src = Src & "APAD_APAD_DETAILS.APAD_YLD_ACT AS [Act Yield], APAD_APAD_DETAILS.APAD_YLD_STD AS [Std Yield],"
Src = Src & "APAD_APAD_DETAILS.APAD_HRS_ACT AS [Act HRs], APAD_APAD_DETAILS.APAD_HRS_STD AS [Std HRs],"
Src = Src & "APAD_APAD_DETAILS.APAD_LB_HR_ACT AS [Act LBs/HR], APAD_APAD_DETAILS.APAD_LB_HR_STD AS [Std LBs/HR],"
Src = Src & "APAD_APAD_DETAILS.APAD_LB_HR_VAR AS [LBs/HR % Dev], APAD_APAD_DETAILS.APAD_VARIABLE_COST_ACT AS [Act Var Proc Cost],"
Src = Src & "APAD_APAD_DETAILS.APAD_VARIABLE_COST_STD AS [Std Var Proc Cost],"
Src = Src & "APAD_APAD_DETAILS.APAD_VARIABLE_COST_DIFF AS [Diff Var Proc Cost],"
Src = Src & "APAD_APAD_DETAILS.APAD_VARIABLE_COST_LB_ACT AS [Act Var Proc Cost/LB],"
Src = Src & "APAD_APAD_DETAILS.APAD_VARIABLE_COST_LB_STD AS [Std Var Proc Cost/LB],"
Src = Src & "[APAD_APAD_DETAILS].[APAD_VARIABLE_COST_LB_STD]-[APAD_APAD_DETAILS].[APAD_VARIABLE_COST_LB_ACT] AS [Var Proc Cost/LB Diff]"
Src = Src & "FROM APAD_APAD_DETAILS INNER JOIN RAWMATL_RM_GRADE ON APAD_APAD_DETAILS.APAD_GRADE = RAWMATL_RM_GRADE.GRADE"
Src = Src & "WHERE (((APAD_APAD_DETAILS.APAD_DATE_CMPLT) Between #" & sStartDate & "# And #" & sEndDate & "#) AND"
Src = Src & "((APAD_APAD_DETAILS.APAD_DEPT)=" & sDept & "));"
.Open Source:=Src, ActiveConnection:=Connection

'Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next

'Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

 

[davers5]

Does the query run by itself? It seems like it doesn't like your SQL. I've gotten lots of ADO errors, but never that one. Where does the error occur?

Dave S.

 

[Phutile]

It craps out at the .Open Source line....but i think it is JUST getting to that and it actually screws up at the SQL.

 

[davers5]

You should try to run the querry at the database level, if possible. I'm guessing, it doesn't like the "inner join" syntax (since it talks about the join expression not being supported). Try using this syntax: Select A, B From tblA, tblB Where tblA.C=tblB.C.

Dave