Determine presence of a Table in the Navigation Pane.

[casinc815]

I have been working with SQL and Access and Oracle for a few years and have used


sysobjects to determine the presence of an object when needed. To this point, I am
able to determine if a table was created and if it exists in the db.

I would like to do the same in Access v2007.

I have searched for a process to determine the presence of a table in the Navigation Pane
and have gotten to the point where I see the sysobjects table in the Navigation Pane.
Unfortunately, this system table is greyed out and is inaccessible for querying.

Does anyone have a snippet of code that detemines the presence of an object, preferable,
a table in the Navigation Pane? Does one use the sysobjects table? Can this technique be used
for other opjects such as forms, reports, modules etc?

Please advise!

Thanks for your consideration!

Jim

[ItsMe]

You mean like this...

Dim obj As TableDef, dbs As Object
Set dbs = CurrentDb
For Each obj In dbs.TableDefs
Debug.Print obj.Name
Next obj

[casinc815]

You mean like this...

Dim obj As TableDef, dbs As Object
Set dbs = CurrentDb
For Each obj In dbs.TableDefs
Debug.Print obj.Name
Next obj

I cut and paste your code into a button and wanted to see if it would
work 'as is.' I will get back to you with the result. Thanks for the quick response.

Jim

[casinc815]

I cut and paste your code into a button and wanted to see if it would
work 'as is.' I will get back to you with the result. Thanks for the quick response.

Jim

WE are back and unfortuneately, the code did not work. Also, all tables were made with the wizard, if that helps.

Thanks for trying!

Jim

[casinc815]

Please find the code that wre modified from your start. Many thanks!
The Debug.print did not print to screen so we used Msgbox. A list of
objects comes to the screen. In this list you will see "MSys" tables and
there is one called MSysAccessObjects which we believe is the equivalent
to the SQL sysobjects table in SqlServer.

Your suggestion appears to work and will be using this procedure to test
for tabledefs. We are exploring the technique for other objects. AS you can
imagine, this is a quality control issue that we wish to balance the number of
objects in a database version of the application.

Many thanks, ItsMe!

Jim

[ItsMe]

You can display the Immedite Window using the keyboard Shortcut - Ctrl+G

You can get more info on the TableDefs Collection here.
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Here is info on QueryDefs
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

I believe objects such as forms, modules, and macros are available in the scope of CurrentProject
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[casinc815]

WE are back and unfortuneately, the code did not work. Also, all tables were made with the wizard, if that helps.

Thanks for trying!

Jim

Back again!

We replaced the Debug.print command with Msgbox and were able to scroll
through the tabledefs. Here is the code.

Dim dbs As Database
'Dim obj As TableDef
Dim tdfLoop As TableDef


Set dbs = CurrentDb()
For Each tdfLoop In dbs.TableDefs
MsgBox " " & tdfLoop.Name
Next tdfLoop

Many thanks, for pointing us in the right direction! If you look at the list that is scrolled through
you will see M or MSys tables. We believe MsysObjects corresponds to sysobjects in SQLServer.

Thanks again!

Jim

[ItsMe]

Yes, those are the system tables.