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(te mpTblName) = True
Then DoCmd.DeleteObj ect acTable, tempTblName

Then I thought of using: If fTableExists(te mpTblName) = True Then
CurrentDb.Execu te "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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

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" <noreply@coveya ccounting.comwr ote in message
news:00ec330b-c315-44b9-adb7-f6c6f35464ce@l6 4g2000hse.googl egroups.com...

My client has a db I am working that uses temp tables. During an
update procedure, I had the code If fTableExists(te mpTblName) = True
Then DoCmd.DeleteObj ect acTable, tempTblName
>
Then I thought of using: If fTableExists(te mpTblName) = True Then
CurrentDb.Execu te "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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

On Aug 21, 6:05 am, Dean <nore...@coveya ccounting.comwr ote:

My client has a db I am working that uses temp tables.  During an
update procedure, I had the code If fTableExists(te mpTblName) = True
Then DoCmd.DeleteObj ect acTable, tempTblName
>
Then I thought of using: If fTableExists(te mpTblName) = True Then
CurrentDb.Execu te "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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

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 <nore...@coveya ccounting.comwr ote:

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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:83e43c8f-b992-40d2-b5ae-84ae4a5be700@l6 4g2000hse.googl egroups.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 <nore...@coveya ccounting.comwr ote:

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.SetWarnin gs False
DoCmd.OpenQuery "qryMakeTab le"
DoCmd.SetWarnin gs True

Fred Zuckerman

[Dean]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

>
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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

On Aug 21, 11:07 pm, "Fred Zuckerman" <Zuckerm...@sbc global.net>
wrote:

"lyle fairfield" <lyle.fairfi... @gmail.comwrote 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.SetWarnin gs False
DoCmd.OpenQuery "qryMakeTab le"
DoCmd.SetWarnin gs 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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable


"Dean" <noreply@coveya ccounting.comwr ote in message
news:f735fb2e-5e9b-49c0-98cb-b67bc9d04ce8@x4 1g2000hsb.googl egroups.com...
On Aug 21, 11:07 pm, "Fred Zuckerman" <Zuckerm...@sbc global.net>
wrote:

>"lyle fairfield" <lyle.fairfi... @gmail.comwrote 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.SetWarni ngs False
>DoCmd.OpenQuer y "qryMakeTab le"
>DoCmd.SetWarni ngs 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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

On Aug 21, 11:50 pm, "Fred Zuckerman" <Zuckerm...@sbc global.net>
wrote:

"Dean" <nore...@coveya ccounting.comwr ote in message
>
news:f735fb2e-5e9b-49c0-98cb-b67bc9d04ce8@x4 1g2000hsb.googl egroups.com...
On Aug 21, 11:07 pm, "Fred Zuckerman" <Zuckerm...@sbc global.net>
wrote:
>
>
>
>
>

"lyle fairfield" <lyle.fairfi... @gmail.comwrote 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.SetWarnin gs False
DoCmd.OpenQuery "qryMakeTab le"
DoCmd.SetWarnin gs 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(te mpTblName) = True Then
CurrentDb.Execu te "DROP TABLE " & tempTblName
docmd.transferd atabase 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.DeleteObj ect acTable, tempTblName
or
CurrentDb.Execu te "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]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

---------------------------------------------------------------------------------
>
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@fortunejames.com]

Re: DROP TABLE vs DoCmd.DeleteObj ect acTable

On Aug 22, 6:28 am, Dean <nore...@coveya ccounting.comwr ote:

None of this answers the real question I asked:
>
DoCmd.DeleteObj ect acTable, tempTblName
or
CurrentDb.Execu te "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.DeleteOb ject 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/library/aa663065(office.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.DeleteObj ect do likewise. Therefore my best guess is that
Microsoft implements both with 'DROP TABLE' :-).

James A. Fortune
CDMAPoster@Fort uneJames.com