Make-table query performance issues

[rednexgfx_k]

All,

Problem Summary: I've running about 30 make table queries via VBA in
Access 2000, and my database goes from 14,000k to over 2,000,000k. In
addition, the longer the procedure runs, the bigger the performance hit
VBA takes. I'm wondering how to prevent or reduce this.

Details: I have a database table of queries I want to run. This table
contains the query name, the SQL text of the query, the name of the
target table, and whether it's a create or an append query. The VBA
code I've written will go through each record of this table, read the
sqltext and execute it, copying the results to the target table. I do
this one field at a time, which is painful. Since the tables fields are
dynamic, I don't know how else to do this.

I used to have a Macro that would call these same Make Table and Append
queries, but this required the user to babysit the process of updating
them, constantly pressing yes. So I wanted to automate it through code.
While this works, it takes much longer and bloats the database. In
fact, the longer the process runs, the longer it takes to add a record
to the database. It slows down exponentially when it hits the big
tables, and stays slow.

Here is the main code logic. All database variables are declared as
DAO. I only include it here now so people don't ask later. All help is
appreciated, and I apologize if the formatting is off (the code does
run when formatted properly)!

<CODE>

Set db = CurrentDb()
Set rs = db.TableDefs(strQryName).OpenRecordset

'Select first record

rs.MoveFirst

While Not rs.EOF

'Execute Query For Later Use
Set qdf = db.CreateQueryDef("", rs.Fields("SQLText"))

Set rstable = qdf.OpenRecordset
'Main Logic/Loop
If rs.Fields("Type") = "Create" Then

'Create table, deleting it first if it already exists.

tableName = rs.Fields("TargetTable")

For Each tdf In db.TableDefs
If tdf.Name = tableName Then
db.TableDefs.Delete (tdf.Name)
End If
Next tdf

Set tdf = db.CreateTableDef(tableName)

'Build table fields here

For Each field In rstable.Fields
'Convert Oracle Field.Type 20s to Integers...
If field.Type = 20 Then
Set fld = tdf.CreateField(field.Name,dbInteger)
tdf.Fields.Append fld

Else
Set fld = tdf.CreateField(field.Name,
field.Type)
tdf.Fields.Append fld
End If
Next field
db.TableDefs.Append tdf

End If

'So we're not creating the table here, we're appending to it.
Even if we created it
'above, we're appending to it. Either way, we're here.
Set rstable2 = db.TableDefs(tableName).OpenRecordset

While Not rstable.EOF

'If i=True, then we AddNew. If it's false, then we
edit.

i = True

For Each field In rstable.Fields
If i = True Then
rstable2.AddNew
rstable2.Fields(field.Name) = field
rstable2.Update
i = False
Else
rstable2.MoveLast
rstable2.Edit
rstable2.Fields(field.Name) = field
rstable2.Update
End If
Next field
rstable.MoveNext

Wend

rstable2.Close

rs.MoveNext

Set rstable = Nothing
Set qdf = Nothing

Wend

rs.Close
</CODE>

[Salad]

re*********@hotmail.com wrote:

All,

Problem Summary: I've running about 30 make table queries via VBA in
Access 2000, and my database goes from 14,000k to over 2,000,000k. In
addition, the longer the procedure runs, the bigger the performance hit
VBA takes. I'm wondering how to prevent or reduce this.

Details: I have a database table of queries I want to run. This table
contains the query name, the SQL text of the query, the name of the
target table, and whether it's a create or an append query. The VBA
code I've written will go through each record of this table, read the
sqltext and execute it, copying the results to the target table. I do
this one field at a time, which is painful. Since the tables fields are
dynamic, I don't know how else to do this.

I used to have a Macro that would call these same Make Table and Append
queries, but this required the user to babysit the process of updating
them, constantly pressing yes. So I wanted to automate it through code.
While this works, it takes much longer and bloats the database. In
fact, the longer the process runs, the longer it takes to add a record
to the database. It slows down exponentially when it hits the big
tables, and stays slow.
You could have
SetWarnings
False
...execute query
SetWarnings
True
and bypassed the process of pressing Yes in a macro.

In code you could enter
Docmd.Setwarnings False
Docmd.OpenQuery "queryname"
Docmd.SetWarnings True

You could also do
Dim strSQL as String
'sql string to update/append/delete...not a simple Select.
strSQL = "Update ....rest of query statement"
Currentdb.Execute strSQL

Check these out in on-line help.
Here is the main code logic. All database variables are declared as
DAO. I only include it here now so people don't ask later. All help is
appreciated, and I apologize if the formatting is off (the code does
run when formatted properly)!

<CODE>

Set db = CurrentDb()
Set rs = db.TableDefs(strQryName).OpenRecordset

'Select first record

rs.MoveFirst

While Not rs.EOF

'Execute Query For Later Use
Set qdf = db.CreateQueryDef("", rs.Fields("SQLText"))

Set rstable = qdf.OpenRecordset
'Main Logic/Loop
If rs.Fields("Type") = "Create" Then

'Create table, deleting it first if it already exists.

tableName = rs.Fields("TargetTable")

For Each tdf In db.TableDefs
If tdf.Name = tableName Then
db.TableDefs.Delete (tdf.Name)
End If
Next tdf

Set tdf = db.CreateTableDef(tableName)

'Build table fields here

For Each field In rstable.Fields
'Convert Oracle Field.Type 20s to Integers...
If field.Type = 20 Then
Set fld = tdf.CreateField(field.Name,dbInteger)
tdf.Fields.Append fld

Else
Set fld = tdf.CreateField(field.Name,
field.Type)
tdf.Fields.Append fld
End If
Next field
db.TableDefs.Append tdf

End If

'So we're not creating the table here, we're appending to it.
Even if we created it
'above, we're appending to it. Either way, we're here.
Set rstable2 = db.TableDefs(tableName).OpenRecordset

While Not rstable.EOF

'If i=True, then we AddNew. If it's false, then we
edit.

i = True

For Each field In rstable.Fields
If i = True Then
rstable2.AddNew
rstable2.Fields(field.Name) = field
rstable2.Update
i = False
Else
rstable2.MoveLast
rstable2.Edit
rstable2.Fields(field.Name) = field
rstable2.Update
End If
Next field
rstable.MoveNext

Wend

rstable2.Close

rs.MoveNext

Set rstable = Nothing
Set qdf = Nothing

Wend

rs.Close
</CODE>

[rednexgfx_k]

You could have
SetWarnings
False
...execute query
SetWarnings
True
and bypassed the process of pressing Yes in a macro.

I feel like an idiot. I could have saved a few days of work knowing
that.

Thank you very much for your help! I was able to implement your
suggestion about SetWarnings in just a few minutes.

[Salad]

re*********@hotmail.com wrote:

You could have
SetWarnings
False
...execute query
SetWarnings
True
and bypassed the process of pressing Yes in a macro.

I feel like an idiot. I could have saved a few days of work knowing
that.

Thank you very much for your help! I was able to implement your
suggestion about SetWarnings in just a few minutes.

In the long run you are a better programmer.

And if you get into a clutch in the future, post your problem sooner. :-)

[Salad]

re*********@hotmail.com wrote:

You could have
SetWarnings
False
...execute query
SetWarnings
True
and bypassed the process of pressing Yes in a macro.

I feel like an idiot. I could have saved a few days of work knowing
that.

Thank you very much for your help! I was able to implement your
suggestion about SetWarnings in just a few minutes.

BTW, you should always turn back the error messages back to true. If
you don't, you may execute something later on that doesn't prompt you
and you won't realize an error or action occured. So...set them off, do
the action, set them back on.

[kbmurphy]

Salad,

That's exactly what I did when I implemented the function in the Macro.
Here's the code (in case anyone else can learn from it):

<code>
Function DisableWarnings(bool As Boolean)

'Warnings are disabled so that macros can run Make Table and Append
Table queries
'without the user babysitting them.

If bool Then
DoCmd.SetWarnings False
Else
DoCmd.SetWarnings True
End If

End Function
</code>

The beginning of the Macro has a runcode that runs this as True, then
ends with runcode DisableWarnings as False.

Just for grins, I also tried your suggestion about using Make-Table
queries as my SQL Text so I didn't have to worry about appending fields
and such, and it worked perfectly. The only reason I didn't do that at
first was because I was convinced that those warnings would come up if
I did it that way.

It's amazing how easy it is to concentrate on getting around the
problem than it is just fixing the problem itself. Again, thanks for
the help.

[rednexgfx_k]

BTW,

Damn Google Groups and its cookies. I didn't want to share my gmail
address with the world. Thought I was posting under this ID above.
*sigh*...

[Tony Toews]

Salad <oi*@vinegar.com> wrote:

In code you could enter
Docmd.Setwarnings False
Docmd.OpenQuery "queryname"
Docmd.SetWarnings True
Don't forget to put the setwarnings in the error handling logic.
You could also do
Dim strSQL as String
'sql string to update/append/delete...not a simple Select.
strSQL = "Update ....rest of query statement"
Currentdb.Execute strSQL

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText

If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise weird things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm