Test for table existence 
Author Message
 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  
 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  
 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"><p><font size=3D2 =
color=3D"#000000" face=3D"Arial">Hi,<br><br>Sure, you can loop throught =
the collection, as suggested by Michael, but you can also make error =
trapping to contribution and directly try a <b>CurrentDB( =
).TableDefs(&quot;NameOfTable&quot;).Name</b>, as example. If there is =
no error (Err.Number =3D0), you can assume NameOfTable =
exist.<br><br>Hope it may help,<br>Vanderghast, Access =
MVP.<br><br>Wladimir Cesar Bianchi &lt;<font =

color=3D"#000000">&gt; wrote in article &lt;<font =

nt color=3D"#000000">&gt;...<br>&gt; Hello all,<br>&gt; <br>&gt; To find =
one table in database, find it in documents container. <br>&gt; This =
function return True if the table is in table container.<br>&gt; Ex: =
<br>&gt; If FindTable(&quot;Table Name&quot;) Then DoCmd DeleteObject =
A_TABLE, &quot;Table Name&quot;<br>&gt; <br>&gt; This example is write =
in Access 97.<br>&gt; <br>&gt; )Function FindTable(W_TableName As =
String) As Boolean<br>&gt; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;Dim W_Doc As =
Document, W_DB As Database, W_Container As<br>&gt; Container<br>&gt; =
&nbsp;&nbsp;) &nbsp;Set W_DB =3D CurrentDb<br>&gt; &nbsp;&nbsp;&nbsp;) =
&nbsp;Set W_Container =3D W_DB.Containers!Tables<br>&gt; =
&nbsp;&nbsp;&nbsp;) &nbsp;FindTable =3D False<br>&gt; &nbsp;&nbsp;) =
&nbsp;For Each W_Doc In W_Container.Documents<br>&gt; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;If W_Doc.Name =
=3D W_TableName Then FindTable =3D True<br>&gt; &nbsp;&nbsp;) =
&nbsp;Next<br>&gt; )End Function<br>&gt; &nbsp;Michael A. Michalski =
escreveu no artigo &lt;<font =

color=3D"#000000"><br>&gt; ki&gt;... ...<br>&gt; &gt;I need to test for =
the existence of a table before I attempt to delete<br>&gt; it.<br>&gt; =
&gt;Because these table are used in conjunction with reports in preview =
mode,<br>&gt; I<br>&gt; &gt;am unable to delete them as part of the =
button generating the report.<br>&gt; &gt;<br>&gt; &gt;I would like to =
use DeleteObject but cannot unless I can verify the<br>&gt; =
&gt;existence of a table first. &nbsp;Some tables are not always =
generated in the<br>&gt; &gt;report generation. &nbsp;A peer suggested =
the following that does not work:<br>&gt; &gt;<br>&gt; &gt;If Not IsNull =
(DLookup(&quot;[Name]&quot;,&quot;MSysObjects&quot;,&quot;[Name} =
=3D'Table Name'&quot;))<br>&gt; Then<br>&gt; &gt; DoCmd DeleteObject =
A_TABLE, &quot;Table Name&quot;<br>&gt; &gt;End If<br>&gt; &gt;<br>&gt; =
&gt;Any takers?<br>&gt; &gt;--<br>&gt; &gt;Michael A. Michalski<br>&gt; =
&gt;Crescendo Enterprises<br>&gt; &gt;Consulting Services for Small =
Business<br>&gt; &gt;419-524-1022 <br>&gt; <br>&gt; <br>&gt; </p>
</font></font></font></font></font></font></font></body></html>
------=_NextPart_000_01BC4EF9.0F818160--



Sat, 09 Oct 1999 03:00:00 GMT  
 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  
 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  
 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  
 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  
 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  
 
 [ 8 post ] 

 Relevant Pages 

1. Testing for table existence & listing available tables

2. Testing for table existence

3. Testing for table existence

4. Testing for table existence

5. Testing for table existence

6. Testing for Existence of Table

7. Test for the existence of a table

8. Testing the existence of a table

9. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

10. Testing for existence of a query

11. Testing for a record already in existence

12. test for existence of a field

 

 
Powered by phpBB® Forum Software