HOW TO "KILL" A QUERYDEF ARRAY!!!

[moroeh]

Hello:

I have a problem with some code I am working with. I have a table with collections of products, and other table with the products. What I have to do is: when somebody buys a collection the program must take all the products belonging to that collection and insert them in another table.

To do that I use an array of querydefs (because I don't know how many products are gonna be in each collection) and I make a querdef for each product. The code is working fine, but as I use the CreateQueryDef option, all the querys I create must be deleted after they are executed (in order to use this function some other time). The question is:

Is it possible to have an array of queries and execute them without been the queries created at the database (actually creating them in memory), and afterwards "kill" the array?

2. Otherwise, the only choice I have is to make a function and delete the queries I created one by one?

I put here the code. I hope it is useful for someone to help me!

Thanks in advance!


Set WSfroga = DBEngine.Workspaces(0)
WSfroga.BeginTrans
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Collection WHERE Collection.id = " & Me.id)
While Not rst.EOF
kontsulta = "INSERT INTO Warehouse(id,kostua, Data, Arrazoia, Kopurua) VALUES (" _
& rst!id & ",'" & kostua & "', '" & data & "', " & Arrazoia & "," & Kopurua & ""
Set qdfroga(i) = CurrentDb.CreateQueryDef(i, kontsulta)
qdfroga(i).Execute
i = i + 1
rst.MoveNext
Wend
If MsgBox("Are you sure you want to append this rows?", vbYesNo) = vbYes Then
WSfroga.CommitTrans
Else
WSfroga.Rollback
End If

 

[Rolliee]

Somewhere you have an array of qdfroga values. They will die when you close the app. To close them earlier, you must do a qdfroga(i).close, I believe. To get rid of the array, you can either 'erase' is or just let it die with it's loss of scope. The problem that you do not ask about is the first argument in the createquerydef and that is the name of the query. It appears to me that they will remain after you close the app and be there for the next one. I am not sure why you want to do that.

Rollie E

 

[JohnYingling]

"Kill" a dynamic array with
Erase gdFroga
OR
Redim gdFroga(0)

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[MichaelRed]

I am sure there are god an sufficient reasons for your approach -perhapd in hte code NOT shown. For the code which is showm, I can only comment on it as is:

The Transaction block appears to be nearly useless. The only abort rollbacl (or commit) criteria (or path) are the "if" block which is simply a "blind" (e.g. the user does not get to review the action) message box. A more common use of the begin [commit | rollback] construct would be to commit is all operations were sucessful and rollback otherwise, but this is not done here, so I find the construct not-useful/

There is no need to use a named (e.g. saved query) to accomplish the inserts at all, so there is no need for the array of querydefs, and no need to use the createquerydef instruction at all. You -in the venue presented- might just as well simply execute the SQL statement(s) and be satisfied with the results.

In hte broader sense, it MIGHT be wise (and it is certainly NORMAL) to at least check that all of the components of a collecttion are available before committing to the shippment (and potential billing for) thereof.

But that would lead us back to the transaction construct -which has already been abused.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[moroeh]

Hello again:

First of all, thanks to everybody for your answers.

Now Michael, actually you are right. This code shown is just some testing code I used to know how transactions work. The point was that I tried to do all the stuff with an String array (where the queries were) and then do a docmd.runSQL command; but doing that way, it doesn´t matter to do a commit or a rollback, because it will execute the command and afterwards there will not be choice, for instance, to do a rollback. Using the query array was the only way I could find to use transactions. Does anybody know how to use transactions or a thread were it is been talk about?

Thanks in advance!

Moroeh (Igor)