DROP TABLE vs DoCmd.DeleteObject acTable

[Dean]

My client has a db I am working that uses temp tables. During an
update procedure, I had the code If fTableExists(tempTblName) = True
Then DoCmd.DeleteObject acTable, tempTblName

Then I thought of using: If fTableExists(tempTblName) = True Then
CurrentDb.Execute "DROP TABLE " & tempTblName

Is there an advantage to either? What really be cool is DROP TABLE
deleted the table and I didn't have to compact the db so much.

[tina]

even cooler would be to use a temp db, with the tables linked into the data
db. that way you never add or delete table objects in the data db - instead
you just overwrite the temp db with a fresh copy as needed.

hth
"Dean" <no*****@coveyaccounting.comwrote in message
news:00**********************************@l64g2000 hse.googlegroups.com...

My client has a db I am working that uses temp tables. During an
update procedure, I had the code If fTableExists(tempTblName) = True
Then DoCmd.DeleteObject acTable, tempTblName

Then I thought of using: If fTableExists(tempTblName) = True Then
CurrentDb.Execute "DROP TABLE " & tempTblName

Is there an advantage to either? What really be cool is DROP TABLE
deleted the table and I didn't have to compact the db so much.

[Dean]

On Aug 21, 6:05*am, Dean <[e-mail]:

My client has a db I am working that uses temp tables. *During an
update procedure, I had the code If fTableExists(tempTblName) = True
Then DoCmd.DeleteObject acTable, tempTblName

Then I thought of using: If fTableExists(tempTblName) = True Then
CurrentDb.Execute "DROP TABLE " & tempTblName

Is there an advantage to either? *What really be cool is DROP TABLE
deleted the table and I didn't have to compact the db so much.

I did fund the followsin the the help:
"Deleting a table is faster when you use the SQL DROP statement or the
SQL DELETE statement without a predicate."

[lyle fairfield]

Yes. If it's a cloudy day the SQL DROP statement can trash a table in
1.6 one thousandths of a second (16/10000 seconds) faster than DoCmd
DELETE. Just think, after ten thousand deletions we could save 16
seconds! Well it's measurably faster only about 1/4 of the time, so I
guess that would be four seconds saved in 10000 deletions. WooHoo!
Ain't Access Help grand! Not only that but when we read something in
Access Help we can feel confident that there is a 50% chance that it's
correct (and a further 25% chance that it's complete)! That's pretty
good if you compare it with a Stevie Harper speech!

But I'm still trying to understand why you think DROP Table (as
opposed to DoCmd.Delete) might reduce the need to compact. I guess
I'll use my four seconds to think about that.

BTW, in my use of Access since the early nineties I do not recall ever
using a temporary table that was saved to disk. I like it that way.

On Aug 21, 5:37*pm, Dean <[e-mail]:

I did fund the followsin the the help:
"Deleting a table is faster when you use the SQL DROP statement or the
SQL DELETE statement without a predicate."

[Fred Zuckerman]

"lyle fairfield" <ly************@gmail.comwrote in message
news:83**********************************@l64g2000 hse.googlegroups.com...
Yes. If it's a cloudy day the SQL DROP statement can trash a table in
1.6 one thousandths of a second (16/10000 seconds) faster than DoCmd
DELETE. Just think, after ten thousand deletions we could save 16
seconds! Well it's measurably faster only about 1/4 of the time, so I
guess that would be four seconds saved in 10000 deletions. WooHoo!
Ain't Access Help grand! Not only that but when we read something in
Access Help we can feel confident that there is a 50% chance that it's
correct (and a further 25% chance that it's complete)! That's pretty
good if you compare it with a Stevie Harper speech!

But I'm still trying to understand why you think DROP Table (as
opposed to DoCmd.Delete) might reduce the need to compact. I guess
I'll use my four seconds to think about that.

BTW, in my use of Access since the early nineties I do not recall ever
using a temporary table that was saved to disk. I like it that way.

On Aug 21, 5:37 pm, Dean <[e-mail]:

I did fund the followsin the the help:
"Deleting a table is faster when you use the SQL DROP statement or the
SQL DELETE statement without a predicate."

To the OP,
When I use temp tables, I usually create them with a Make-Table Query. By
default, it automatically overwrites the existing table if it's there. When
done via code, I use the following if I want to make it seamless to the
user:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTable"
DoCmd.SetWarnings True

Fred Zuckerman

[Dean]

>
But I'm still trying to understand why you think DROP Table (as
opposed to DoCmd.Delete) might reduce the need to compact. I guess
I'll use my four seconds to think about that.

I have been using Access since 2.0. I didn't really think it would
reduce the need to compact a database...just thought it'd be cool
feature MS should look at. But the help documentation sheds little
light on the differences between the two commands. Just curios if
anyone knew.

[Dean]

On Aug 21, 11:07*pm, "Fred Zuckerman" <[e-mail]>
wrote:

"lyle fairfield" <[e-mail] in message

To the OP,
When I use temp tables, I usually create them with a Make-Table Query. By
default, it automatically overwrites the existing table if it's there. When
done via code, I use the following if I want to make it seamless to the
user:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTable"
DoCmd.SetWarnings True

Fred Zuckerman

These temp tables (22 in all) my client makes is from an ODBC
connection to their ERP system. Not sure why he went this route
though. He deletes and imports with the database transfer command.
This method works, but bloats the db each time import which is at
least daily. So nothing like a qryMakeTable will fit here.

[Fred Zuckerman]

"Dean" <no*****@coveyaccounting.comwrote in message
news:f7**********************************@x41g2000 hsb.googlegroups.com...
On Aug 21, 11:07 pm, "Fred Zuckerman" <[e-mail]>
wrote:

>"lyle fairfield" <[e-mail] in message

>To the OP,
When I use temp tables, I usually create them with a Make-Table Query. By
default, it automatically overwrites the existing table if it's there.
When
done via code, I use the following if I want to make it seamless to the
user:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTable"
DoCmd.SetWarnings True

Fred Zuckerman

These temp tables (22 in all) my client makes is from an ODBC
connection to their ERP system. Not sure why he went this route
though. He deletes and imports with the database transfer command.
This method works, but bloats the db each time import which is at
least daily. So nothing like a qryMakeTable will fit here.

---------------------------------------------------------------------------------

What about a delete query (delete all records) followed by an append query?
That saves having to remove and add objects.
I don't know if that minimizes bloat.....
Fred Zuckerman

[Dean]

On Aug 21, 11:50*pm, "Fred Zuckerman" <[e-mail]>
wrote:

"Dean" <[e-mail] in message

news:f7**********************************@x41g2000 hsb.googlegroups.com...
On Aug 21, 11:07 pm, "Fred Zuckerman" <[e-mail]>
wrote:

"lyle fairfield" <[e-mail] in message
To the OP,
When I use temp tables, I usually create them with a Make-Table Query.By
default, it automatically overwrites the existing table if it's there.
When
done via code, I use the following if I want to make it seamless to the
user:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTable"
DoCmd.SetWarnings True

Fred Zuckerman

These temp tables (22 in all) my client makes is from an ODBC
connection to their ERP system. *Not sure why he *went this route
though. *He deletes and imports with the database transfer command.
This method works, but bloats the db each time import which is at
least daily. *So nothing like a qryMakeTable will fit here.

---------------------------------------------------------------------------*------

What about a delete query (delete all records) followed by an append query?
That saves having to remove and add objects.
I don't know if that minimizes bloat.....
Fred Zuckerman- Hide quoted text -

- Show quoted text -

The data is imported database transfer command. So yes, you could
link the odbc tables then write your own delete/append but you have to
do this 22 times. Also, each o connection consumes a license on their
ERP software so a live connection should be avoided on a multi-user
db. For the purpose of the Access db they created, there is no need
for it to be live anyway.
The list of the ODBC tables needed are stored in a table and it loops
through the list:
If fTableExists(tempTblName) = True Then
CurrentDb.Execute "DROP TABLE " & tempTblName
docmd.transferdatabase tempTblName, "ODBC Connection"
etc.

If a new table is needed, just add it to the list of tables. Run the
procedure. So the code is pretty efficient and easy to maintain.

None of this answers the real question I asked:

DoCmd.DeleteObject acTable, tempTblName
or
CurrentDb.Execute "DROP TABLE " & tempTblName

Is there an advantage to either other than the little blurb I found in
help?
It seems I gave too much detail.

[Dean]

---------------------------------------------------------------------------*------

What about a delete query (delete all records) followed by an append query?
That saves having to remove and add objects.
I don't know if that minimizes bloat.....
Fred Zuckerman- Hide quoted text -

- Show quoted text -

No way that minimizes any bloat. A deleted record or object is still
there until you compact.

[CDMAPoster]

On Aug 22, 6:28*am, Dean <[e-mail]:

None of this answers the real question I asked:

DoCmd.DeleteObject acTable, tempTblName
or
CurrentDb.Execute "DROP TABLE " & tempTblName

Is there an advantage to either other than the little blurb I found in
help?

My guess is that either the 'DoCmd.DeleteObject acTable, tempTblName'
is implemented by using a 'DROP TABLE ...' query, or the 'DROP
TABLE...' query is implemented by changing the Object Model. The hint
that the 'DROP TABLE...' query is slightly faster makes me lean toward
the former. Either implementation would have to deal with the table
object eventually without the expense of having to look at the rest of
the Object Model. An example of an Access Object Model:

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

But all this is just guesswork at what Access does under the hood.
Sometimes I am amazed at how simple the implementation is to many
things that Microsoft tried so hard to make look like it took a lot of
effort. Other times they surprise me by getting the implementation
right for some truly difficult things. I suppose I would implement
the 'DROP TABLE' SQL by having it modify the Object Model and having
the DoCmd.DeleteObject do likewise. Therefore my best guess is that
Microsoft implements both with 'DROP TABLE' :-).

James A. Fortune
CD********@FortuneJames.com