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

Run SQL Command Loop

Brilstern
100+
P: 208
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.

Share this Question
Share on Google+
3 Replies


Brilstern
100+
P: 208
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
Expert Mod 15k+
P: 31,768
Chosen as Best Answer by me and not the OP.
May 2 '14 #3

Brilstern
100+
P: 208
Thx Adrian. I am very close to finishing this project finally :)
May 4 '14 #4

Post your reply

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