alternate to CurrentDb.Execute for make-table query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tina - 29 Jul 2005 20:37 GMT

hi folks.

i have a process where multiple action queries are run sequentially to
generate data for a report, including some make-table queries. this process
is used repetitively, so the tables that are created by the make-table
queries already exist after the first "run".

when a make-table query is run manually, then of course a msgbox pops up
warning that the pre-existing "new" table will be deleted and asking if you
want to continue. however, when you automate the "run queries" process by
using the Currentdb.Execute method in a procedure and looping through a list
of action queries, the procedure simply errs out at the make-table query, as

Error 3010: Table 'NewTableName' already exists.

i got around this by trapping the error, and using DoCmd.OpenQuery, with
warnings turned off (and back on), and then moving back to the Loop code
with Resume Next.

all of the above works without error, but i'm wondering if there is a
downside or potential problem to using the OpenQuery solution, that i'm not
aware of. or perhaps a better way to handle the issue, altogether. one note:
i can't alter the basic process of using action queries to generate report
data; the scope of my task is to automate the "run queries" process.

i'd appreciate any comments, suggestions, or critique y'all would care to
offer.

thx
tina :)

Reply to this Message

Klatuu - 29 Jul 2005 21:46 GMT

Use the DeleteObject method to delete the tables before you execute the make
table query:

DoCmd.DeleteObject ........
CurrentDb.Execute(.......

> hi folks.
>
[quoted text clipped - 26 lines]
> thx
> tina :)

Reply to this Message

tina - 30 Jul 2005 00:20 GMT

thanks for taking the time to respond, Klatuu. that solution would work if
the action queries were all hard-coded into the procedure, because i could
also hard-code the table objects to be deleted at the appropriate point in
the process.

the procedure i wrote is a generic handling procedure that loops through a
list of action queries, as

Do
strName = Rst("QueryName")
CurrentDb.Execute strName, dbFailOnError
Rst.MoveNext
Loop Until Rst.EOF

i have no way of knowing which, if any, of the action queries in a given
list will be make-table queries - so a DeleteObject command is not always
appropriate in a given cycle of the loop.

the user defines the sequential list of specific action queries from a
"master" list of action queries. i could require the user to manually
designate which of the chosen queries is a make-table query; in fact, that
was my original solution (before i wrote the DoCmd.OpenQuery) solution. but
it's a clunky solution, prone to user error, and i prefer to handle the
issue entirely programmatically.

another possible avenue: the "master" list of queries is created by
appending the names of all action queries into a table using the following
code, as

For Each varQry In CurrentDb.QueryDefs
If Left(varQry.Name, 1) = "~" Or _
Left(varQry.Name, 1) = "r" Then
' skip this query because it's a combobox sql
' querydef or an "r" report recordsource
Else
CurrentDb.Execute "INSERT INTO tblQueries " _
& "( QueryName ) SELECT '" _
& varQry.Name & "'", dbFailOnError
End If
Next

is there any way to examine a query object programmatically and 1) determine
whether or not it is a make-table query, and 2) if it is, determine what
name will be assigned to the "new" table? if so, i could capture that data
and append it into the master list, also.

as before, i appreciate all comments, suggestions, critiques that anyone
cares to offer.

thx,
tina :)

> Use the DeleteObject method to delete the tables before you execute the make
> table query:
[quoted text clipped - 32 lines]
> > thx
> > tina :)

Reply to this Message

David C. Holley - 30 Jul 2005 00:54 GMT

I would encapsulate* the make-table SQL Statement in an IF...THEN that
checks for the existance of the table by querying the MSysObjects table.
A DCount() should do the trick.

If DCount([parameters]) = 0 then
[Code Here]
end if

*My new favorite word

> hi folks.
>
[quoted text clipped - 26 lines]
> thx
> tina :)

Reply to this Message

tina - 30 Jul 2005 01:59 GMT

thanks for taking the time to respond, David. i'm working on an approach
that utilizes the SQL property of the querydef, at this moment. i'll post
back on how it comes out.

in the meantime, comments, suggestions, and critiques are still requested
and appreciated, as before.

thx
tina :)

> I would encapsulate* the make-table SQL Statement in an IF...THEN that
> checks for the existance of the table by querying the MSysObjects table.
[quoted text clipped - 35 lines]
> > thx
> > tina :)

Reply to this Message

tina - 30 Jul 2005 10:40 GMT

well, i was able to parse the SQL to identify what type of action query each
query is, and then get the name of the "new table" from the make-table
query's SQL. it's pretty clunky code, but it does get the job done.

your remark about querying the MSysObjects table put my mind on a whole new
track, though, so now i'm pursuing that avenue...and the learning never
ends. <g>

thanks again for your time, everyone.

tina :)

> thanks for taking the time to respond, David. i'm working on an approach
> that utilizes the SQL property of the querydef, at this moment. i'll post
[quoted text clipped - 54 lines]
> > > thx
> > > tina :)

Reply to this Message

Bas Cost Budde - 30 Jul 2005 14:54 GMT

You might just get away with
- trap error 3010
- scan the err.description string for the table name
- delete that table
- Resume.

That way you don't have to interpret every action query you're about to run.

Or, use MSysQueries. The row with attribute=1 has the result table name
in Name1:

SELECT MSysObjects.Name, MSysQueries.Name1
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Type)=5) AND ((MSysQueries.Attribute)=1))
ORDER BY MSysObjects.Name;

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Reply to this Message

tina - 30 Jul 2005 22:39 GMT

thanks for taking the time to respond, Bas. i did end up referencing the
system tables (very carefully) in various places in my code, and it worked
very well for my needs. thanks for the good suggestion. :)

> You might just get away with
> - trap error 3010
[quoted text clipped - 12 lines]
> WHERE (((MSysObjects.Type)=5) AND ((MSysQueries.Attribute)=1))
> ORDER BY MSysObjects.Name;

Reply to this Message