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

make table with VBA, no errors, but doesn't work either

P: 39
I posted this in the VB forum but I think its in the wrong place. So I thought I would try here.

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


FishVal
Expert 2.5K+
P: 2,653
And ... what is the question?

Regards,
Fish.
Jun 3 '09 #2

Megalog
Expert 100+
P: 378
Ok so on line 85 you have:
Expand|Select|Wrap|Line Numbers
  1. 'DoCmd.OpenQuery ("qryMakeZonesTablefromMMMe"), acViewDesign, acEdit
Is this still relevant somehow, or is it old commented code?

I take it you have tried using this on line 86?:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL SQL20
As far as I can see, there's nothing executing your sql string in this code.
Jun 3 '09 #3

P: 39
OK!! WOW! I'm special today! I thinking I might need to take a break now.

Ok, So it does help to have the DoCmd.RunSQL SQL20 after the actual SQL statement.

My question was why didn't it do anything when I clicked the button, but after the question about the commented line of code for an old saved query being ignored it clicked.

Sorry all for making such a wondeful error.

thanks for the second pair of eyes.
Jun 3 '09 #4

Megalog
Expert 100+
P: 378
No problem, you were 99.999% of the way there =)
Jun 3 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
We all have examples of those Timber. Don't worry.

BTW Welcome to full memberhood (Posts also match age - 8-) ).
Jun 4 '09 #6

Post your reply

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