I create temporary tables and use them for reports, etc.  Some of more 
inquisitive testers delete those tables.  I would like to test for the 
existance of those tables and then act on that information (IE rebuild or 
just pull a report).  Is there an easy way to check in VBA with an IF 
statement to see if those tables exist.

Thanks

See MS Access help on TableDef
 Mike Marcus wrote:I create temporary tables and use them for reports, etc. Some of more 
inquisitive testers delete those tables. I would like to test for the 
existance of those tables and then act on that information (IE rebuild or 
just pull a report). Is there an easy way to check in VBA with an IF 
statement to see if those tables exist.

Thanks

Lonnie Johnson 
ProDev, Builders of MS Access Databases
Check me out ==> http://www.galaxymall.com/software/PRODEV 




---------------------------------
Do You Yahoo!?
Yahoo! Health - Feel better, live better

Mike,
The database has a tables collection you can loop through. It is a 0
based collection so use For i= 0 to Tables.Count-1. then just check for
the name of the table in your If statement.

Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

*****ORIGINAL MESSAGE*****

I create temporary tables and use them for reports, etc.  Some of more 
inquisitive testers delete those tables.  I would like to test for the 
existance of those tables and then act on that information (IE rebuild
or 
just pull a report).  Is there an easy way to check in VBA with an IF 
statement to see if those tables exist.

Mike,
Sorry, it is a TableDefs collection, not a Tables collection.
Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

Sub test()
   If TableExists("YourTableName") Then
      'do something here
   Else
      'do something else here
   End If
End Sub

Private Function TableExists(ByVal TableName As String) As Boolean
   Dim db As DAO.Database
   Dim tbl As DAO.TableDef

   Set db = CurrentDb
   On Error Resume Next
   tbl = db.TableDefs(TableName)
   TableExists = Not tbl Is Nothing
   Set tbl = Nothing
   Set db = Nothing
End Function

You don't need to loop through the tabledefs collection just ignore the
error and see if the object is still nothing.

|-----Original Message-----
|From: Mike Marcus [mailto:mmarcus@n...]
|Sent: Thursday, July 25, 2002 8:19 PM
|To: Access
|Subject: [access] Check for Tables Existance
|
|
|I create temporary tables and use them for reports, etc.  Some of more
|inquisitive testers delete those tables.  I would like to test for the
|existance of those tables and then act on that information (IE rebuild or
|just pull a report).  Is there an easy way to check in VBA with an IF
|statement to see if those tables exist.
|
|Thanks
|

Hi

For my curiosity

All the temp tables are fine and finding out the existence and just fire
the report from the existing temp file.
My concern is, if that temp table is too old, then what is the result of
the output? could be same as old reports.

My idea here is, the temp table are not too big, then just overwrite always
when the report generated.
Another case, it is too big and need more processing time to create, then
we need to check the date and time of
temp file generated, will have to judge whether to refresh the data or just
generate the report.

Could be the ideal way of reporting from temp files.

Best Regards



                                                                                                                                    
                      leoscott@c...                                                                                              
                                               To:       access@p...                                                        
                      07/26/2002 06:44         cc:                                                                                  
                      AM                       Subject:  [access] RE: Check for Tables Existance                                    
                      Please respond to                                                                                             
                      access                                                                                                        
                                                                                                                                    
                                                                                                                                    




Sub test()
   If TableExists("YourTableName") Then
      'do something here
   Else
      'do something else here
   End If
End Sub

Private Function TableExists(ByVal TableName As String) As Boolean
   Dim db As DAO.Database
   Dim tbl As DAO.TableDef

   Set db = CurrentDb
   On Error Resume Next
   tbl = db.TableDefs(TableName)
   TableExists = Not tbl Is Nothing
   Set tbl = Nothing
   Set db = Nothing
End Function

You don't need to loop through the tabledefs collection just ignore the
error and see if the object is still nothing.

|-----Original Message-----
|From: Mike Marcus [mailto:mmarcus@n...]
|Sent: Thursday, July 25, 2002 8:19 PM
|To: Access
|Subject: [access] Check for Tables Existance
|
|
|I create temporary tables and use them for reports, etc.  Some of more
|inquisitive testers delete those tables.  I would like to test for the
|existance of those tables and then act on that information (IE rebuild or
|just pull a report).  Is there an easy way to check in VBA with an IF
|statement to see if those tables exist.
|
|Thanks
|