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

Bill-

He says he's calling it from a macro, so it needs to be a Function.

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 7:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>

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

Yahoo! Groups Links