ACCESS FEED: Re: [MS_AccessPros] Access SQL Syntax for a Query to Drop a Table

Jumat, 10 Agustus 2012

Re: [MS_AccessPros] Access SQL Syntax for a Query to Drop a Table

John

If Terry is using the code he posted that's probably why it's blowing up. But now that I look even more closely I see that there is not function or Sub name.

Terry

All routines need a name and an end point.
'***Start Code***
Public Sub DeleteTables()
Dim db As DAO.Database
On Error Resume Next

Set db = CurrentDb
db.TableDefs.Delete "tblTmpTblDailDevconImport"
db.TableDefs.Delete "tblTmpTblDailyCoachOnHoldImport"
db.TableDefs.Delete "tmptblDailyOBSCommCoachImport"
db.TableDefs.Delete "tblTmpTblDailyVehicleAssign"
db.TableDefs.Delete "tblTmpTblDailyINITArchives\\\\'"
db.TableDefs.Delete "[Sheet1$_ImportErrors]"
Set db = Nothing
End Sub
'***End Code***

Bill

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Well, Bill. If you looked at the original post, I obviously screwed up my
> copy/paste. It should be:
>
> db.TableDefs.Delete "tblTmpTblDailyINITArchives"
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Friday, August 10, 2012 5:26 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Access SQL Syntax for a Query to Drop a Table
>
> Terry
>
> What name?
>
> This looks fishy to me:
> "tblTmpTblDailyINITArchives\\\\'"
>
> Not only does it need square brackets but there is an apostrophe at the end.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Terry Olsen" <terryomsn@>
> wrote:
> >
> > Thanks, John. I created a module from your response -
> DeleteTmpTblDailyImport(Access 2010. It looks like this. I tried to execute it
> from a macro, using the runcode action. I got a message saying that it couldn't
> find the name.
> > Option Compare Database
> >
> > Dim db As DAO.Database
> > On Error Resume Next
> >
> > Set db = CurrentDb
> > db.TableDefs.Delete "tblTmpTblDailDevconImport"
> > db.TableDefs.Delete "tblTmpTblDailyCoachOnHoldImport"
> > db.TableDefs.Delete "tmptblDailyOBSCommCoachImport"
> > db.TableDefs.Delete "tblTmpTblDailyVehicleAssign"
> > db.TableDefs.Delete "tblTmpTblDailyINITArchives\\\\'"
> > db.TableDefs.Delete "[Sheet1$_ImportErrors]"
> > Set db = Nothing
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Terry-
> > >
> > > It's easier in VBA:
> > >
> > > Dim db As DAO.Database
> > > On Error Resume Next
> > >
> > > Set db = CurrentDb
> > > db.Tabledefs.Delete "tblTmpTblDailDevconImport"
> > > db.Tabledefs.Delete "tblTmpTblDailyCoachOnHoldImport"
> > > db.Tabledefs.Delete "tmptblDailyOBSCommCoachImport
> > > db.Tabledefs.Delete "tblTmpTblDailyVehicleAssign"
> > > db.Tabledefs.Delete "tblTmpTblDailyINITArchives\\\\'
> > > db.Tabledefs.Delete "[Sheet1$_ImportErrors]"
> > > Set db = Nothing
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terry Olsen
> > > Sent: Thursday, August 09, 2012 5:36 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Access SQL Syntax for a Query to Drop a Table
> > >
> > > I have a table that I would like to drop, if it exists.
> > > I have a query that drops several tables. All, but one exists whenever this
> > > query is called from a macro. The [Sheet1$_ImportErrors] may or may not be
> > > there.
> > >
> > > drop table tblTmpTblDailyDevconImport,
> > > tblTmpTblDailyCoachOnHoldImport,
> > > tmptblDailyOBSCommCoachImport,
> > > tblTmpTblDailyVehicleAssign,
> > > tblTmpTblDailyINITArchives,
> > > [Sheet1$_ImportErrors];
> > >
> > > Thanks for any suggestions
> > > Terryomsn
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>