_________________________________________
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.
Expand|Select|Wrap|Line Numbers
- Private Sub btnStep20_Click()
- On Error GoTo ErrorHandler
- ' 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
- ExitCode:
- Exit Sub
- ErrorHandler:
- MsgBox "Error " & Err.Number & " - " & Err.Description
- Resume ExitCode
- Exit Sub
- End Sub
Expand|Select|Wrap|Line Numbers
- 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);