VBA, Test for table existence

Wladimir Cesar Bianch
#1 / 8

Test for table existence

Hello all,

To find one table in database, find it in documents container.
This function return True if the table is in table container.
Ex:
If FindTable("Table Name") Then DoCmd DeleteObject A_TABLE, "Table Name"

This example is write in Access 97.

)Function FindTable(W_TableName As String) As Boolean
) Dim W_Doc As Document, W_DB As Database, W_Container As
Container
) Set W_DB = CurrentDb
) Set W_Container = W_DB.Containers!Tables
) FindTable = False
) For Each W_Doc In W_Container.Documents
) If W_Doc.Name = W_TableName Then FindTable = True
) Next
)End Function

ki>... ...

Quote:

>I need to test for the existence of a table before I attempt to delete
it.
>Because these table are used in conjunction with reports in preview mode,
I
>am unable to delete them as part of the button generating the report.

>I would like to use DeleteObject but cannot unless I can verify the
>existence of a table first. Some tables are not always generated in the
>report generation. A peer suggested the following that does not work:

>If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table Name'"))
Then
> DoCmd DeleteObject A_TABLE, "Table Name"
>End If

>Any takers?
>--
>Michael A. Michalski
>Crescendo Enterprises
>Consulting Services for Small Business
>419-524-1022

Fri, 08 Oct 1999 03:00:00 GMT

Wladimir Cesar Bianch
#2 / 8

Test for table existence

Hello all,

To find one table in database, find it in documents container.
This function return True if the table is in table container.
Ex:
If FindTable("Table Name") Then DoCmd DeleteObject A_TABLE, "Table Name"

This example is write in Access 97.

)Function FindTable(W_TableName As String) As Boolean
) Dim W_Doc As Document, W_DB As Database, W_Container As
Container
) Set W_DB = CurrentDb
) Set W_Container = W_DB.Containers!Tables
) FindTable = False
) For Each W_Doc In W_Container.Documents
) If W_Doc.Name = W_TableName Then FindTable = True
) Next
)End Function

ki>... ...

Quote:

>I need to test for the existence of a table before I attempt to delete
it.
>Because these table are used in conjunction with reports in preview mode,
I
>am unable to delete them as part of the button generating the report.

>I would like to use DeleteObject but cannot unless I can verify the
>existence of a table first. Some tables are not always generated in the
>report generation. A peer suggested the following that does not work:

>If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table Name'"))
Then
> DoCmd DeleteObject A_TABLE, "Table Name"
>End If

>Any takers?
>--
>Michael A. Michalski
>Crescendo Enterprises
>Consulting Services for Small Business
>419-524-1022

Fri, 08 Oct 1999 03:00:00 GMT

Michel Wals
#3 / 8

Test for table existence

This is a multi-part message in MIME format.

------=_NextPart_000_01BC4EF9.0F818160
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,

Sure, you can loop throught the collection, as suggested by Michael, but
you can also make error trapping to contribution and directly try a
CurrentDB( ).TableDefs("NameOfTable").Name, as example. If there is no
error (Err.Number =0), you can assume NameOfTable exist.

Hope it may help,
Vanderghast, Access MVP.

Quote:

> Hello all,

> To find one table in database, find it in documents container.
> This function return True if the table is in table container.
> Ex:
> If FindTable("Table Name") Then DoCmd DeleteObject A_TABLE, "Table Name"

> This example is write in Access 97.

> )Function FindTable(W_TableName As String) As Boolean
> ) Dim W_Doc As Document, W_DB As Database, W_Container As
> Container
> ) Set W_DB = CurrentDb
> ) Set W_Container = W_DB.Containers!Tables
> ) FindTable = False
> ) For Each W_Doc In W_Container.Documents
> ) If W_Doc.Name = W_TableName Then FindTable = True
> ) Next
> )End Function
> Michael A. Michalski escreveu no artigo

- Show quoted text -

Quote:

> ki>... ...
> >I need to test for the existence of a table before I attempt to delete
> it.
> >Because these table are used in conjunction with reports in preview
mode,
> I
> >am unable to delete them as part of the button generating the report.

> >I would like to use DeleteObject but cannot unless I can verify the
> >existence of a table first. Some tables are not always generated in the
> >report generation. A peer suggested the following that does not work:

> >If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table Name'"))
> Then
> > DoCmd DeleteObject A_TABLE, "Table Name"
> >End If

> >Any takers?
> >--
> >Michael A. Michalski
> >Crescendo Enterprises
> >Consulting Services for Small Business
> >419-524-1022

------=_NextPart_000_01BC4EF9.0F818160
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFE8">Hi, Sure, you can loop throught =
the collection, as suggested by Michael, but you can also make error =
trapping to contribution and directly try a CurrentDB( =
).TableDefs("NameOfTable").Name, as example. If there is =
no error (Err.Number =3D0), you can assume NameOfTable =
exist. Hope it may help, Vanderghast, Access =
MVP. Wladimir Cesar Bianchi <> wrote in article <>... > Hello all, > > To find =
one table in database, find it in documents container. > This =
function return True if the table is in table container. > Ex: =
 > If FindTable("Table Name") Then DoCmd DeleteObject =
A_TABLE, "Table Name" > > This example is write =
in Access 97. > > )Function FindTable(W_TableName As =
String) As Boolean > =
        )  Dim W_Doc As =
Document, W_DB As Database, W_Container As > Container > =
  )  Set W_DB =3D CurrentDb >    ) =
 Set W_Container =3D W_DB.Containers!Tables > =
   )  FindTable =3D False >   ) =
 For Each W_Doc In W_Container.Documents > =
      )    If W_Doc.Name =
=3D W_TableName Then FindTable =3D True >   ) =
 Next > )End Function >  Michael A. Michalski =
escreveu no artigo < > ki>... ... > >I need to test for =
the existence of a table before I attempt to delete > it. > =
>Because these table are used in conjunction with reports in preview =
mode, > I > >am unable to delete them as part of the =
button generating the report. > > > >I would like to =
use DeleteObject but cannot unless I can verify the > =
>existence of a table first.  Some tables are not always =
generated in the > >report generation.  A peer suggested =
the following that does not work: > > > >If Not IsNull =
(DLookup("[Name]","MSysObjects","[Name} =
=3D'Table Name'")) > Then > > DoCmd DeleteObject =
A_TABLE, "Table Name" > >End If > > > =
>Any takers? > >-- > >Michael A. Michalski > =
>Crescendo Enterprises > >Consulting Services for Small =
Business > >419-524-1022 > > > 
</body></html>
------=_NextPart_000_01BC4EF9.0F818160--

Sat, 09 Oct 1999 03:00:00 GMT

Ken Ge
#4 / 8

Test for table existence

says...

Quote:

> I would like to use DeleteObject but cannot unless I can verify the
> existence of a table first. Some tables are not always generated in the
> report generation. A peer suggested the following that does not work:

Why do you need to test? Just use code that turns off error handling,
then deletes the table:

On Error Resume Next
' Delete the table here
' Reset error handling here

If your only goal is to delete the table, and you just want to continue
unnotified if the table doesn't exist, I can't imagine why this solution
wouldn't be the right one. -- Ken

Sat, 09 Oct 1999 03:00:00 GMT

Laura Wilso
#5 / 8

Test for table existence

Michael,

Look in the Microsoft Knowlege Base (at www.microsoft.com) for a function
called ObjectExists(). I copied the code and it works fine.

HIH,

Laura Wilson

Quote:

> I need to test for the existence of a table before I attempt to delete it.
> Because these table are used in conjunction with reports in preview mode, I
> am unable to delete them as part of the button generating the report.

> I would like to use DeleteObject but cannot unless I can verify the
> existence of a table first. Some tables are not always generated in the
> report generation. A peer suggested the following that does not work:

> If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table Name'")) Then
> DoCmd DeleteObject A_TABLE, "Table Name"
> End If

> Any takers?
> --
> Michael A. Michalski
> Crescendo Enterprises
> Consulting Services for Small Business
> 419-524-1022

> ---------------------------------------------------------------

> I need to test for the existence of a table before I attempt to delete
> it. Because these table are used in conjunction with reports in
> preview mode, I am unable to delete them as part of the button
> generating the report.

> I would like to use DeleteObject but cannot unless I can verify the
> existence of a table first. Some tables are not always generated in
> the report generation. A peer suggested the following that does not
> work:

> If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table Name'"))
> Then
> DoCmd DeleteObject A_TABLE, "Table Name"
> End If

> Any takers?
> --
> Michael A. Michalski
> Crescendo Enterprises
> Consulting Services for Small Business
> 419-524-1022

Sat, 09 Oct 1999 03:00:00 GMT

Gerry Hickma
#6 / 8

Test for table existence

Yeah, what version does that work in? I wasn't sure what version he was
using.
--
Gerry Hickman (London UK)
http://www.users.dircon.co.uk/~gerry1/

Sun, 10 Oct 1999 03:00:00 GMT

Darcy Fo
#7 / 8

Test for table existence

I think the above mentioned function works in all versions. I am using it
in Access 97, no problem.

Quote:

> Yeah, what version does that work in? I wasn't sure what version he was
> using.
> --
> Gerry Hickman (London UK)
> http://www.users.dircon.co.uk/~gerry1/

Mon, 11 Oct 1999 03:00:00 GMT

Matthew L. Schuet
#8 / 8

Test for table existence

You guys all made this thing way too hard. He gets an error when the
table doesn't exist during code to DELETE the table. If the table
doesn't exist it's already DELETED. TASK ACCOMPLISHED! Trap the error,
RESUME NEXT. Now let us Resume next. Please.

matt

Quote:

> Hi,

> Sure, you can loop throught the collection, as suggested by Michael,
> but you can also make error trapping to contribution and directly try
> a CurrentDB( ).TableDefs("NameOfTable").Name, as example. If there is
> no error (Err.Number =0), you can assume NameOfTable exist.

> Hope it may help,
> Vanderghast, Access MVP.

> > Hello all,

> > To find one table in database, find it in documents container.
> > This function return True if the table is in table container.
> > Ex:
> > If FindTable("Table Name") Then DoCmd DeleteObject A_TABLE, "Table
> Name"

> > This example is write in Access 97.

> > )Function FindTable(W_TableName As String) As Boolean
> > ) Dim W_Doc As Document, W_DB As Database, W_Container As
> > Container
> > ) Set W_DB = CurrentDb
> > ) Set W_Container = W_DB.Containers!Tables
> > ) FindTable = False
> > ) For Each W_Doc In W_Container.Documents
> > ) If W_Doc.Name = W_TableName Then FindTable = True
> > ) Next
> > )End Function
> > Michael A. Michalski escreveu no artigo

> > ki>... ...
> > >I need to test for the existence of a table before I attempt to
> delete
> > it.
> > >Because these table are used in conjunction with reports in preview
> mode,
> > I
> > >am unable to delete them as part of the button generating the
> report.

> > >I would like to use DeleteObject but cannot unless I can verify the
> > >existence of a table first. Some tables are not always generated
> in the
> > >report generation. A peer suggested the following that does not
> work:

> > >If Not IsNull (DLookup("[Name]","MSysObjects","[Name} ='Table
> Name'"))
> > Then
> > > DoCmd DeleteObject A_TABLE, "Table Name"
> > >End If

> > >Any takers?
> > >--
> > >Michael A. Michalski
> > >Crescendo Enterprises
> > >Consulting Services for Small Business
> > >419-524-1022

Sun, 17 Oct 1999 03:00:00 GMT