By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,612 Members | 1,632 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,612 IT Pros & Developers. It's quick & easy.

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

P: 39
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
  1. Private Sub btnStep20_Click()
  2.     ' Create the Zones table from the sellers MMMe processing rule and zone table
  3.     Dim SQL20 As String
  4.     Dim rs1 As New ADODB.Recordset
  5.     Dim rs2 As New ADODB.Recordset
  6.     Dim rs3 As New ADODB.Recordset
  7.     Dim rs4 As New ADODB.Recordset
  8.     Dim rs5 As New ADODB.Recordset
  9.     Dim rs6 As New ADODB.Recordset
  10.     Dim strConn As String ' stores the connection string
  11.     Dim srcZoneTbl As String
  12.     Dim srcProcessingTbl As String
  13.     Dim srcSystemNum As String
  14.     Dim srcZoneFields As String
  15.     Dim srcProcessingFields As String
  16.     Dim srcZones As String
  17.  
  18.     strConn = CurrentProject.Connection
  19.     ' set the srcZoneTbl variable to name of the table given
  20.     Set rs1 = New ADODB.Recordset
  21.         rs1.Open "SELECT source_zone_table_name FROM Ref_Data", strConn, adOpenDynamic
  22.  
  23.         If rs1.RecordCount <> 0 Then
  24.             rs1.MoveFirst
  25.                 srcZoneTbl = rs1("source_zone_table_name").Value
  26.             Else
  27.                 Debug.Print rs1("source_zone_table_name").Value
  28.         End If
  29.     Set rs2 = New ADODB.Recordset
  30.     ' set the srcProcesingTbl variable to the name of the table given
  31.         rs2.Open "SELECT source_processing_rule_name FROM Ref_Data", strConn, adOpenDynamic
  32.  
  33.         If rs2.RecordCount <> 0 Then
  34.             rs2.MoveFirst
  35.                 srcProcessingTbl = rs2("source_processing_rule_name").Value
  36.             Else
  37.                 Debug.Print rs2("source_processing_rule_name").Value
  38.         End If
  39.  
  40.     Set rs3 = New ADODB.Recordset
  41.     ' set the srcSystemNum variable to the join value being used between the two tables
  42.         rs3.Open "SELECT join_system_no FROM Ref_Data", strConn, adOpenDynamic
  43.  
  44.         If rs3.RecordCount <> 0 Then
  45.             rs3.MoveFirst
  46.                 srcSystemNum = rs3("join_system_no").Value
  47.             Else
  48.                 Debug.Print rs3("join_system_no").Value
  49.         End If
  50.  
  51.     Set rs4 = New ADODB.Recordset
  52.     ' set the array to the fields needing to be pulled from the table
  53.         rs4.Open "SELECT processing_fields FROM Ref_Fields", strConn, adOpenDynamic
  54.         Dim arrProcessingArray As Variant
  55.         arrProcessingArray = rs4.GetString(adClipString, , "; ", ", ")
  56.         arrProcessingArray = Left(arrProcessingArray, (Len(arrProcessingArray) - 2))
  57.  
  58.     Set rs5 = New ADODB.Recordset
  59.     ' set the array to the fields needing to be pulled from the table
  60.         rs5.Open "SELECT zone_fields FROM Ref_Fields WHERE ((zone_fields) Is Not Null)", strConn, adOpenDynamic
  61.         Dim arrZoneArray As String
  62.         arrZoneArray = rs5.GetString(adClipString, , "; ", ", ")
  63.         arrZoneArray = Left(arrZoneArray, (Len(arrZoneArray) - 2))
  64.  
  65.     Set rs6 = New ADODB.Recordset
  66.     ' set the srcZones variable to the name of the table being created. using a variable
  67.     ' as we will not alwyas get the previous two tables.
  68.         rs6.Open "SELECT Zone_table_name FROM Ref_Data", strConn, adOpenDynamic
  69.  
  70.         If rs6.RecordCount <> 0 Then
  71.             rs6.MoveFirst
  72.                 srcZones = rs6("Zone_table_name").Value
  73.             Else
  74.                 Debug.Print rs6("Zone_table_name").Value
  75.         End If
  76.  
  77.  
  78. Me.Message = "A query will open for you to use..."
  79. Me.Repaint
  80.     SQL20 = "SELECT " & arrZoneArray & ", " & arrProcessingArray & " INTO " & srcZones & " FROM " & srcZoneTbl & " INNER JOIN " & srcProcessingTbl & " ON (" & srcZoneTbl & "." & srcSystemNum & "=" & srcProcessingTbl & "." & srcSystemNum & ") AND (" & srcZoneTbl & ".zone_id=" & srcProcessingTbl & ".zone_id);"
  81.  
  82.     'DoCmd.OpenQuery ("qryMakeZonesTablefromMMMe"), acViewDesign, acEdit
  83. 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
  84. Me.Repaint
  85. rs1.Close
  86. rs2.Close
  87. rs3.Close
  88. rs4.Close
  89. rs5.Close
  90. rs6.Close
  91. Set rs1 = Nothing
  92. Set rs2 = Nothing
  93. Set rs3 = Nothing
  94. Set rs4 = Nothing
  95. Set rs5 = Nothing
  96. Set rs6 = Nothing
  97.  
  98. End Sub
  99.  
SQL Statement for SQL20

Expand|Select|Wrap|Line Numbers
  1. SELECT zone.system_no, 
  2. zone.zone_id, 
  3. zone.alarmgrp_no, 
  4. zone.comment, 
  5. zone.camera_zone_id, 
  6. processing_rule.zonestate_id, 
  7. processing_rule.event_id, 
  8. processing_rule.glsched_id, 
  9. processing_rule.sched_no, 
  10. processing_rule.gldisp_id, 
  11. processing_rule.dispage_no, 
  12. processing_rule.zone_to_restore, 
  13. processing_rule.alt_cs_no 
  14. INTO Zones 
  15. FROM zone 
  16. INNER JOIN processing_rule ON (zone.system_no=processing_rule.system_no) 
  17. AND (zone.zone_id=processing_rule.zone_id);
  18.  

Any ideas? Thanks so much in advance!!!
Jun 3 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.