mSysObjects and Missing Table

[CA_IT]

I have a piece of code that first tests for the existance of a table, then
drops it, and recreates it. It has been working fine for about 6 months.
Suddenly, I am getting a 'table already exists' error. When looping through
the code, I find it is never found when looping through the tabledefs.
Further investigation revealed that I have a table that is listed in the
mSysObjects table, but not the database window. Below is the relevent code
snippets

'test and drop if present
For Each tdfOld In db.TableDefs
If tdfOld.Name = "temptblCommitteeLabels_New" Then
db.TableDefs.Delete "temptblCommitteeLabels_New"

'create new table
Set tdfNew = db.CreateTableDef("temptblCommitteeLabels")

With tdfNew
.Fields.Append .CreateField("strCompany", dbText)
.Fields.Append .CreateField("firstname", dbText)
.Fields.Append .CreateField("lastname", dbText)
.Fields.Append .CreateField("strbusinessaddress", dbText)
.Fields.Append .CreateField("strcity", dbText)
.Fields.Append .CreateField("strstate", dbText)
.Fields.Append .CreateField("strzip", dbText)
.Fields.Append .CreateField("TermBegan", dbText)
.Fields.Append .CreateField("TermEnded", dbText)
.Fields.Append .CreateField("TermsServed", dbText)
.Fields.Append .CreateField("CurrentStatus", dbText)
.Fields.Append .CreateField("CommitteeName", dbText)
.Fields.Append .CreateField("PositionName", dbText)
.Fields.Append .CreateField("sortpos", dbText)

db.TableDefs.Append tdfNew

As I stated, the code has been working fine for several months. So far I
have done the compact and repair routine about 10 times, and the importing
objects into a new database twice. It should be noted, when doing the import
objects, that this ghost table is not in the list, but, does import to the
new database.

I have done a work around of using a new table name, however, I would like
to get this ghost out of my DB!!

Thanks in advance!

 

[Roger Carlson]

Perhaps this is too simplistic, but is the table marked as Hidden?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CA_IT]

please note

the table names being different in here is a typo/pasting error. They are
identical in the real code. Sorry for the confusion!

 

[CA_IT]

When I view hidden objects it is still not there.

Never be afraid to ask the obvious! More of my mistakes are 'oh DUH!' than
real issues! ;-0

 

[Roger Carlson]

Ok. You've tried compacting an importing. What about decompiling?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jerry Whittle]

Run the following SQL. It should return a Type of 1 for Table or 6 for Linked
Table. If it returns anything else or nothing at all, that may explain
things. Make sure that I put in the correct table name.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name)="temptblCommitteeLabels_New"));

 

[CA_IT]

It returns a type 2

Run the following SQL. It should return a Type of 1 for Table or 6 for Linked
Table. If it returns anything else or nothing at all, that may explain
things. Make sure that I put in the correct table name.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name)="temptblCommitteeLabels_New"));

 

[CA_IT]

I

1) decompiled
2) compiled the modules
3) saved
4) closed the db
5) opened it normally
6) compacted and repaired.

still, no go....

 

[Jerry Whittle]

I've only seen one Type 2 and it was for the MSysDb. I found this about
MSysDB: Due to some internal details, the permissions for the whole database
are set by modifying the single document found in the Database container
called "MSysDb." I do know that is often pops up in error messages when the
database is corrupt.

I'm thinking that your table is being create not quite right. As a Type 2,
it's not showing up in the database window and might cause problems. I see
from another post that doing a Select * from MSysDB shuts down Access. You
ghost table might cause problems also.

 

[Tom Coffinger]

Anyone have any fresh ideas on this?

 

[Joan Wild]

Why are you dropping the table, and then recreating it?

Wouldn't it be simpler to run two queries - delete query to empty it, and an
append query to add to it.

 

[Tom Coffinger]

Joan,
The actual fields in the table are dynamic, so, it is created on the fly.

the issue is, this has worked for months. Now suddenly I have one ghost
table, and cannot get rid of it. So, how do i delete a db object, that is
in the MSysObjects table and no where else?!

 

[Joan Wild]

That sounds like an odd arrangement, but when you create the new mdb and
import, be sure not to import the MSys objects.

After creating the table in code, do you refresh the collection?
db.TableDefs.Refresh

 

[CA_IT]

I cannot find the MSysObjects any where in the lists when I do an import.
So i am GUESSING they are selected by default. How would I exclude them?

 

[Joan Wild]

I didn't mean MSysObjects, I meant any object that begins with 'MSys'.

Just ensure that they are hidden i.e. in the old database: Tools, Options,
uncheck the show system objects.

 

[CA_IT]

yeah

they already were. So, not sure why it is still importing.

 

[Joan Wild]

As I asked before, do you refresh the collection in your code?
db.TableDefs.Refresh
Also, you could try
Application.RefreshDatabaseWindow