循环DAO Recordset以加入我的表

Question

我有VBA访问循环通过我的表2记录集，并与我的主表连接每一行，并创建新的工作表。我是新手，不懂这个问题;然后呢?这个时候运行时错误3296 JOIN表达式不支持，你可以专家检查我的代码和帮助我吗?

Here is My [sampleDB] https://drive.google.com/file/d/0B980etBxqQuzTGxiS1g3eUlLcHc/edit?usp=sharing

谢谢。

 Sub ExportReport()
Dim dbsReport As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstSKSF As DAO.Recordset
Dim strSQL As String
 Dim xlsxPath As String
On Error GoTo ErrorHandler
   Set dbsReport = CurrentDb
   xlsxPath = "I:ProjTr_Rep " & Format(Now(), "mm-dd-yyyy hhmmss AMPM") & ".xlsx"

   'Open a recordset on all records from the SkillSoft Request table that have
   'a Null value in the ReportsTo field.
   strSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"
   Set rstSKSF = dbsReport.OpenRecordset(strSQL, dbOpenDynaset)
   'If the recordset is empty, exit.
   If rstSKSF.EOF Then Exit Sub
   With rstSKSF
      Do Until .EOF
      'join report table with SKSF_request table's Rows
      'Create newworksheet for each report joint with SKSF rows
            Set qdf = dbsReport.CreateQueryDef("Training_Report", _
                "SELECT Report.Name, Report.[Employee Role], Report.[Employee Location], Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], Report.[Completion Stat]FROM Report LEFT JOIN SKSF_Req ON Report.[Asset Title] = rstSKSF(SKSF_Req.[Course Name]) WHERE (((Report.[Asset Title]) = rstSKSF([SKSF_RequestForm].[Course Name])) And (rstSKSF((SKSF_Req.Role) Like " * " & [Report].[Employee Role] & " * ")) GROUP BY Report.Name, Report.[Employee Role], Report.[Employee Location], Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], Report.[Completion Stat], Report.[EMP ID]")
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Training_Report", xlsxPath, True
            DoCmd.DeleteObject acQuery, "Training_Report"

         .Edit
          rstSKSF![Flag] = "Y" 'Set Flag
         .Update
         .MoveNext
      Loop
   End With
   rstSKSF.Close
   dbsReport.Close
   Set rstSKSF = Nothing
   Set dbsReport = Nothing
   Exit Sub
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   End Sub

Answer 1

我不是100%确定这是你的错误的来源，但你有双引号内的SQL语句((SKSF_Req.Role) Like " * " & [Report].[Employee Role] & " * "，肯定会导致代码失败。此外，您不能引用这样的SQL语句中的记录集(是否有可能将记录集直接包含在SQL中，而不是迭代它?那会快得多)

"SELECT Report.Name, Report.[Employee Role], Report.[Employee Location], Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], Report.[Completion Stat]FROM Report LEFT JOIN SKSF_Req ON Report.[Asset Title] = rstSKSF(SKSF_Req.[Course Name]) WHERE (((Report.[Asset Title]) = rstSKSF([SKSF_RequestForm].[Course Name])) And (rstSKSF((SKSF_Req.Role) Like " * " & [Report].[Employee Role] & " * ")) GROUP BY Report.Name, Report.[Employee Role], Report.[Employee Location], Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], Report.[Completion Stat], Report.[EMP ID]"

应该更像:

"SELECT Report.Name, Report.[Employee Role], Report.[Employee Location]," & _
" Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
" Report.[Completion Stat] " & _
"FROM Report LEFT JOIN SKSF_Req ON Report.[Asset Title] = '" & rstSKSF![Course Name] & "'" & _
" WHERE (((Report.[Asset Title]) = '" & rstSKSF![Course Name] & "'" & _
" And '" & rstSKSF!Role & "' Like ' * ' & [Report].[Employee Role] & ' * ')) " & _
" GROUP BY Report.Name, Report.[Employee Role], Report.[Employee Location], " & _
" Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
" Report.[Completion Stat], Report.[EMP ID]"

Answer 2

我明白了。DAO不接受"' Like ' * ' & [Report].[Employee Role] & ' * '，我从查询中读取，不得不在s trSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"语句中将其更改为实表。