rednexgfx_k@hotmail.com wrote:
[color=blue]
> 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.[/color]
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.
[color=blue]
> 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>
>[/color]
