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_table_name | zone
source_processing_rule_name | processing_rule
Zone_table_name | Zones
join_system_no | system_no
Ref_Field
Field | Data
processing_fields | 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!!!
