VBA no errors, but no table is created?? help?

[timber910]

Ok, I have a button on a form that I'm using to create another table I will need later in my form. I have created a reference table called Ref_Table (holds my table names - using this as the tables with the orginal data changes from project to project), I have another reference table called Ref_Fields (this holds the fields I will need to pull out of the tables they are in.)

I know there is a better way to code some of this so if you happen to have a more effective way of doing this that would be wondeful to learn as well. I have put a breakpoint when at the end to see if all my variables were populating correctly. They were. The SQL20 at the end of the code gives me the SQL statement I need and I can type this directly into an SQL windown in access and run the query without any errors and the table is created. I pasted the SQL statement at the very end to show what is being generated by the code.

Ok here is the VBA code for this button and the Ref_Table and Ref_Field

Ref_Table
Field | Data
source_zone_tab le_name | zone
source_processi ng_rule_name | processing_rule
Zone_table_name | Zones
join_system_no | system_no

Ref_Field
Field | Data
processing_fiel ds | processing_rule .zonestate_id, event_id, glsched_id, sched_no, gldisp_id, dispage_no, zone_to_restore , alt_cs_no

zone_fields | zone.system_no, zone_id, alarmgrp_no, comment, camera_zone_id

each of the above have the processing_rule . or zone. before them, just left them off for typing.

Private Sub btnStep20_Click()
    ' Create the Zones table from the sellers MMMe processing rule and zone table
    Dim SQL20 As String
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim rs3 As New ADODB.Recordset
    Dim rs4 As New ADODB.Recordset
    Dim rs5 As New ADODB.Recordset
    Dim rs6 As New ADODB.Recordset
    Dim strConn As String ' stores the connection string
    Dim srcZoneTbl As String
    Dim srcProcessingTbl As String
    Dim srcSystemNum As String
    Dim srcZoneFields As String
    Dim srcProcessingFields As String
    Dim srcZones As String
 
    strConn = CurrentProject.Connection
    ' set the srcZoneTbl variable to name of the table given
    Set rs1 = New ADODB.Recordset
        rs1.Open "SELECT source_zone_table_name FROM Ref_Data", strConn, adOpenDynamic
 
        If rs1.RecordCount <> 0 Then
            rs1.MoveFirst
                srcZoneTbl = rs1("source_zone_table_name").Value
            Else
                Debug.Print rs1("source_zone_table_name").Value
        End If
    Set rs2 = New ADODB.Recordset
    ' set the srcProcesingTbl variable to the name of the table given
        rs2.Open "SELECT source_processing_rule_name FROM Ref_Data", strConn, adOpenDynamic
 
        If rs2.RecordCount <> 0 Then
            rs2.MoveFirst
                srcProcessingTbl = rs2("source_processing_rule_name").Value
            Else
                Debug.Print rs2("source_processing_rule_name").Value
        End If
 
    Set rs3 = New ADODB.Recordset
    ' set the srcSystemNum variable to the join value being used between the two tables
        rs3.Open "SELECT join_system_no FROM Ref_Data", strConn, adOpenDynamic
 
        If rs3.RecordCount <> 0 Then
            rs3.MoveFirst
                srcSystemNum = rs3("join_system_no").Value
            Else
                Debug.Print rs3("join_system_no").Value
        End If
 
    Set rs4 = New ADODB.Recordset
    ' set the array to the fields needing to be pulled from the table
        rs4.Open "SELECT processing_fields FROM Ref_Fields", strConn, adOpenDynamic
        Dim arrProcessingArray As Variant
        arrProcessingArray = rs4.GetString(adClipString, , "; ", ", ")
        arrProcessingArray = Left(arrProcessingArray, (Len(arrProcessingArray) - 2))
 
    Set rs5 = New ADODB.Recordset
    ' set the array to the fields needing to be pulled from the table
        rs5.Open "SELECT zone_fields FROM Ref_Fields WHERE ((zone_fields) Is Not Null)", strConn, adOpenDynamic
        Dim arrZoneArray As String
        arrZoneArray = rs5.GetString(adClipString, , "; ", ", ")
        arrZoneArray = Left(arrZoneArray, (Len(arrZoneArray) - 2))
 
    Set rs6 = New ADODB.Recordset
    ' set the srcZones variable to the name of the table being created. using a variable
    ' as we will not alwyas get the previous two tables.
        rs6.Open "SELECT Zone_table_name FROM Ref_Data", strConn, adOpenDynamic
 
        If rs6.RecordCount <> 0 Then
            rs6.MoveFirst
                srcZones = rs6("Zone_table_name").Value
            Else
                Debug.Print rs6("Zone_table_name").Value
        End If
 
 
Me.Message = "A query will open for you to use..."
Me.Repaint
    SQL20 = "SELECT " & arrZoneArray & ", " & arrProcessingArray & " INTO " & srcZones & " FROM " & srcZoneTbl & " INNER JOIN " & srcProcessingTbl & " ON (" & srcZoneTbl & "." & srcSystemNum & "=" & srcProcessingTbl & "." & srcSystemNum & ") AND (" & srcZoneTbl & ".zone_id=" & srcProcessingTbl & ".zone_id);"
 
    'DoCmd.OpenQuery ("qryMakeZonesTablefromMMMe"), acViewDesign, acEdit
Me.Message = SQL20 ' normally has a phrase that the step is done, but set to this variable to see SQL statement being sent to DB
Me.Repaint
rs1.Close
rs2.Close
rs3.Close
rs4.Close
rs5.Close
rs6.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set rs4 = Nothing
Set rs5 = Nothing
Set rs6 = Nothing
 
End Sub
 

SQL Statement for SQL20

SELECT zone.system_no, 
zone.zone_id, 
zone.alarmgrp_no, 
zone.comment, 
zone.camera_zone_id, 
processing_rule.zonestate_id, 
processing_rule.event_id, 
processing_rule.glsched_id, 
processing_rule.sched_no, 
processing_rule.gldisp_id, 
processing_rule.dispage_no, 
processing_rule.zone_to_restore, 
processing_rule.alt_cs_no 
INTO Zones 
FROM zone 
INNER JOIN processing_rule ON (zone.system_no=processing_rule.system_no) 
AND (zone.zone_id=processing_rule.zone_id);
 

Any ideas? Thanks so much in advance!!!