473,320 Members | 1,876 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.

Run SQL Command Loop

Brilstern
208 100+
This is more for OOP purposes but is there a way to loop through strings of SQL in VBA?

Example:

I have a database that tracks personnel for a boys and girls club. When the personnel manager wants to delete a member I have set it up to archive instead of deleting the record. #1 so that they may restore a member & #2 so that the historical data can be reported on.

Below are the tables and replica tables that the SQL appends from them deletes from.
Expand|Select|Wrap|Line Numbers
  1. tbl_Members/tbl_A_Members
  2. tbl_Contacts/tbl_A_Contacts
  3. tbl_Medical/tbl_A_Medical
  4. tbl_ReportCards/tbl_A_ReportCards
  5. tbl_ProgramAttendance/tbl_A_ProgramAttendance
  6. tbl_EventAttendance/tbl_A_EventAttendance
tbl_Member is the primary table and the primary key is [Member ID]. Each of the other tables has [Member ID] as a foreign key and has enforced referential integrity.

The process to "move" a member is simply an append statement and delete statement for each table to a replicated table that serves as the archive, obviously the member being last because of the referential integrity.

So my VBA ends being lengthy because there is a String, SQL statement, and DoCmd.RunSQL for each table. (twice, once for append and once for deletion).

Example (sudo code)
Expand|Select|Wrap|Line Numbers
  1. Dim SQL1 AS String
  2. Dim SQL2 AS String
  3.  
  4. strSQL1 = "SELECT.."
  5. strSQL2 = "DELETE.."
  6.  
  7. DoCmd.RunSQL strSQL1
  8. DoCmd.RunSQL strSQL2
Except of course that is one of 6 tables so 12 strings total.

Is their any way to simplfy/loop this to save face? this is my first command and I will have to do this another 15 times (7 more entities to move, and 8 restore features)

This method works PERFECT, just the code is long...

Any help or guidance would be much obliged.
Apr 30 '14 #1

✓ answered by Brilstern

Ok, so I did some more digging (rephrasing my question in Google) and I have found an answer. I'll add it here to help others as well as simplify what I found.

First you begin by adding each statement to a collection:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdArchive_Click()
  2.  
  3.       'collection string
  4.       Dim colSQL As New Collection
  5.  
  6.       'each SQL statement as added (no ; except on last statement) 
  7.       colSQL.Add "INSERT ..."
  8.       colSQL.Add "DELETE ..."
  9.       colSQL.Add "INSERT ..."
  10.       colSQL.Add "DELETE ...;"
  11.  
  12.       'calls a public function that runs the collection as a transaction
  13.       bResult = ExecuteTransaction(colSQL)
  14.  
  15. Exit Sub
Now the Public Function; this function will run as a transaction. This means that if one of the statements fails it will roll back the whole transaction and restore your tables to their "pre-SQL" state. You can easily remove this feature if you want it to run regardless of errors.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function ExecuteTransaction(ByRef colSQL As Collection) As Boolean
  3.  
  4.     Dim intItems As Integer
  5.     Dim bError As Boolean
  6.  
  7.     'start a transaction
  8.     DBEngine.BeginTrans
  9.     DoCmd.SetWarnings False
  10.     DoCmd.Hourglass True
  11.  
  12.     For intItems = 1 To colSQL.Count
  13.  
  14.         On Error Resume Next
  15.         DoCmd.RunSQL colSQL(intItems)
  16.         'error handling
  17.  
  18.         If Err() <> 0 Then
  19.             bError = True
  20.  
  21.             Exit For
  22.  
  23.         End If
  24.  
  25.         On Error GoTo 0
  26.  
  27.     Next intItems
  28.  
  29.     'check if an error occured and if so, rollback the transaction
  30.  
  31.     If bError Then
  32.  
  33.         DBEngine.Rollback
  34.         MsgBox "there was an error"
  35.  
  36.     Else
  37.  
  38.         DBEngine.CommitTrans
  39.  
  40.     End If
  41.  
  42.     DoCmd.SetWarnings True
  43.     DoCmd.Hourglass False
  44.     ExecuteTransaction = Not bError
  45.  
  46. End Function
Hope this helps anybody looking for the same capability.

NOTE: To remove error handling delete the following lines: 5, 16-25, 29 - 36, 40, & 44.

3 2058
Brilstern
208 100+
Ok, so I did some more digging (rephrasing my question in Google) and I have found an answer. I'll add it here to help others as well as simplify what I found.

First you begin by adding each statement to a collection:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdArchive_Click()
  2.  
  3.       'collection string
  4.       Dim colSQL As New Collection
  5.  
  6.       'each SQL statement as added (no ; except on last statement) 
  7.       colSQL.Add "INSERT ..."
  8.       colSQL.Add "DELETE ..."
  9.       colSQL.Add "INSERT ..."
  10.       colSQL.Add "DELETE ...;"
  11.  
  12.       'calls a public function that runs the collection as a transaction
  13.       bResult = ExecuteTransaction(colSQL)
  14.  
  15. Exit Sub
Now the Public Function; this function will run as a transaction. This means that if one of the statements fails it will roll back the whole transaction and restore your tables to their "pre-SQL" state. You can easily remove this feature if you want it to run regardless of errors.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function ExecuteTransaction(ByRef colSQL As Collection) As Boolean
  3.  
  4.     Dim intItems As Integer
  5.     Dim bError As Boolean
  6.  
  7.     'start a transaction
  8.     DBEngine.BeginTrans
  9.     DoCmd.SetWarnings False
  10.     DoCmd.Hourglass True
  11.  
  12.     For intItems = 1 To colSQL.Count
  13.  
  14.         On Error Resume Next
  15.         DoCmd.RunSQL colSQL(intItems)
  16.         'error handling
  17.  
  18.         If Err() <> 0 Then
  19.             bError = True
  20.  
  21.             Exit For
  22.  
  23.         End If
  24.  
  25.         On Error GoTo 0
  26.  
  27.     Next intItems
  28.  
  29.     'check if an error occured and if so, rollback the transaction
  30.  
  31.     If bError Then
  32.  
  33.         DBEngine.Rollback
  34.         MsgBox "there was an error"
  35.  
  36.     Else
  37.  
  38.         DBEngine.CommitTrans
  39.  
  40.     End If
  41.  
  42.     DoCmd.SetWarnings True
  43.     DoCmd.Hourglass False
  44.     ExecuteTransaction = Not bError
  45.  
  46. End Function
Hope this helps anybody looking for the same capability.

NOTE: To remove error handling delete the following lines: 5, 16-25, 29 - 36, 40, & 44.
Apr 30 '14 #2
NeoPa
32,556 Expert Mod 16PB
Chosen as Best Answer by me and not the OP.
May 2 '14 #3
Brilstern
208 100+
Thx Adrian. I am very close to finishing this project finally :)
May 4 '14 #4

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

Similar topics

1
by: Ben Floyd | last post by:
It goes like this: Im converting a perl script that executes a command on each result of a directory listing. In perl, all is well. In Python, using approximately the same logic creates a...
7
by: Mark Harrison | last post by:
I'm writing some event-driven programs, and I would like to do the equivalent of the Tcl 'after' command, e.g.: after 1000 {puts "one second has elapsed"} 1. What's the most canonical way of...
8
by: bearophileHUGS | last post by:
Hello, I have four things to ask or to suggest, sorry if they seem basic or already discussed. ------------------- I am still ignorant about Tkinter. This little program, after pressing the...
34
by: Roman Mashak | last post by:
Hello, All! I'm implementing simple CLI (flat model, no tree-style menu etc.). Command line looks like this: <command> <param1> <param2> ... <paramN> (where N=1..4) And idea is pretty simple: ...
32
by: cj | last post by:
When I'm inside a do while loop sometimes it's necessary to jump out of the loop using exit do. I'm also used to being able to jump back and begin the loop again. Not sure which language my...
4
by: Byte | last post by:
The following code will not work for me: x = 1 while x == 1: print 'hello' x = input('What is x now?: ') while x == 2: print 'hello again'
0
by: rjain7 | last post by:
Hello, I am a newbie; looking for an easy equivalent of TCL 'unknown' command in python. My basic aim is to be able to pass any command which python interpreter does not understand to my own C++...
2
by: apec | last post by:
Dear All, I'm having trouble getting my c++ code to work (below). Any suggestions as to what could be the problem? I think it has to do with the while loop, since the program ends before command...
2
by: fniles | last post by:
I am using .Net 2003 and querying a SQL Server 2000 database. I read the database in a loop, but on every iteration I set the SQLCommand to new, set it, execute it, dispose and set it to nothing....
10
by: cricket | last post by:
Hello, I am trying to create a loop in an MS Access application that will read in a list of iSeries system names from a file in my Access database, and then submit a command to each of the listed...
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...
1
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...
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: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.