473,233 Members | 1,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

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

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
0 1735

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

Similar topics

10
by: Douglas Buchanan | last post by:
I am using the following code instead of a very lengthly select case statement. (I have a lot of lookup tables in a settings form that are selected from a ListBox. The data adapters are given a...
3
by: markydev | last post by:
Hi, I'm using sql server 2000 sp4. I've 2 databases linked, an instance and my local. I'm getting two different errors when trying to update the remote table (local server) from the instance....
2
by: Kavitha Rao | last post by:
Hi, I am getting the following errors while trying to run this snippet in Microsoft Visual C++.Can't seem to print the crc value stored. /* +++Date last modified: 05-Jul-1997 */ /* Crc - 32...
0
by: AndyAFCW | last post by:
I am developing my first .NET application that connects to a SQL Server 2000 database and I am having a total nightmare :x :evil: I am running Windows 2000 with Visual Studio .NET version...
2
by: PHP_Paul | last post by:
Ok, I'm trying to poineer into the wonderful area of PHP/MySQL programming, but I'm having some difficulties. http://www.paulhq.com/php/freepage.html should register, but when anyone fills something...
20
by: ridergroov | last post by:
I had this message previously in another group and I was told to try here. Hopefully someone can help out. Site was created in Dreamwaver MX 04 and I cna't figure out why there are so many errors...
4
ak1dnar
by: ak1dnar | last post by:
Hi, I have created a PHP page that write down form data to mySQL table. Before i submit data I want to check the Input field, whether it has filled up or Not. the form should validate from the PHP...
9
by: Keith G Hicks | last post by:
I'm having a lot of trouble with "file in use" errors in my "folder watcher" project. Starting and stopping the watcher and reading my XML file work fine. Once the watcher is started, I'm reading...
5
by: timber910 | last post by:
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...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.