473,320 Members | 2,193 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,320 software developers and data experts.

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

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
5 2095
FishVal
2,653 Expert 2GB
And ... what is the question?

Regards,
Fish.
Jun 3 '09 #2
Megalog
378 Expert 256MB
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
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
378 Expert 256MB
No problem, you were 99.999% of the way there =)
Jun 3 '09 #5
NeoPa
32,556 Expert Mod 16PB
We all have examples of those Timber. Don't worry.

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

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

Similar topics

82
by: Peter Diedrich | last post by:
The site design is pretty simple: ============================================ | Head | ============================================ | | ...
6
by: Bernd Koehler | last post by:
Hi: I am a EE prof currently teaching an IT course. One the assignments students recently completed was designing a small MS Access Database. I have two submissions that are remarkably...
21
by: Dan | last post by:
Hi, just ran into my first instance of a backend Access97 database not compacting. I'm getting the "MSACCESS.EXE has generated errors.." message on compact. I've narrowed it down to the largest...
52
by: mavic | last post by:
hi to everybody,i need help for my project....how to make a program that accept any integer and convert it binary,octal and hexadecimal.pls help me...thanks
6
by: scottyman | last post by:
I can't make this script work properly. I've gone as far as I can with it and the rest is out of my ability. I can do some html editing but I'm lost in the Java world. The script at the bottom of...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
2
by: Shelly | last post by:
I succeeded in putting up a datalist with thumbnails, four to a row. I put the height and width in the rows and invoked them in the aspx file with: <asp:Image ID="Image1" runat="server"...
15
by: Michael B Allen | last post by:
I like to use limit pointers as sentinels when working on buffers of data. Meaning I use ptr < lim where ptr and lim are both pointers rather than n 0 to determine if it is safe to proceed writing...
3
by: jonceramic | last post by:
Hi All, I need to know the best way to set up a datawarehouse/materialized view for doing statistics/graphs in Access. My crosstabs and unions are getting too complicated to crunch in real...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.