X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Drop Table Automatically, Access 2013    
post Dec 23 2017, 11:41 PM

Posts: 5,231
Joined: 2-November 04
From: Downey, CA

I'm trying to totally automate a database - I'm exporting data from Sharepoint, and put it in an Access database ... it will let me specify the database I want to put it in, but for a novice like me, I don't know how to control what the table is named that will hold the exported table - it puts the exported data in a table named "invoice Approvals" - if there is already a table named "Invoice Approvals," it names the table with the newly exported data "Invoice Approvals1," so I need to programmatically drop the table, but I think because of the space (and perhaps my method), nothing's happening..

The method I'm using is to have a macro fire

Currentdb.execute "DROP TABLE [Invoice Approvals]"
Go to the top of the page
post Dec 23 2017, 11:44 PM

Access Wiki and Forums Moderator
Posts: 73,499
Joined: 19-June 07
From: SunnySandyEggo


Have you tried using something like?

DoCmd.DeleteObject acTable, "TableName"

Hope it helps...
Go to the top of the page
post Dec 24 2017, 07:32 AM

UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA

I think you can also try:

CurrentProject.Connection.Execute "DROP TABLE [tablaname];"

That's an ADO connection, whereas CurrentDb.Execute works off DAO. I don't recall offhand, but there's some DDL stuff that ADO can do that DAO won't. As a general rule, if you want to run DDL, CurrentProject.Connection is the preferred object to execute on.

Go to the top of the page
post Dec 24 2017, 09:13 PM

Posts: 5,231
Joined: 2-November 04
From: Downey, CA

Thanks, Jack! I'll give that a try! As for dbGuy's suggestion - I'm on a pc that doesn't have Access, but doesn't that just delete the records in the table? I want to get rid of the table completely.... thanks again!!

Go to the top of the page
post Dec 24 2017, 10:52 PM

UtterAccess VIP
Posts: 8,938
Joined: 25-October 10
From: Gulf South USA

Hi: PMFJI ...

...but doesn't that just delete the records in the table?

Actually, theDBguy's suggestion should delete the table itself, not just the records in it. It is just the Command approach. Either should work.

And a very Merry Christmas to you on this Christmas Eve! hat_tip.gif

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 02:40 PM