Solved Tables from Backend File Getting Hidden on Compact & Repair (1 Viewer)

[Pac-Man]

Hello,

I have a backend of an access split database which is giving me this issue. When I use compact and repair on this file, a lot of (almost 80 - 90%) tables got disappeared. Initially I though that these are deleted but when I turned hidden and system items on, those are hidden with hidden check box grayed out means those cannot be unhidden from the checkbox. Also, owner of those tables is changed to "engine". I am unable to figure out the issue. I cannot used compact and repair database option on this file. Has anyone faced this issue and know the solution?

Searched the internet but couldn't find any solution either.

Regards,
Abdullah

 

[arnelgp]

did you change their attribute to dbhiddenObject?

 

[theDBguy]

Are you doing the C&R over the network?

 

[isladogs]

did you change their attribute to dbhiddenObject?

Just tested this and my suspicions were confirmed.
Using dbHiddenObject (deep hiding) would have no effect on the already linked table in the FE.
That is also the case if the linked table is just hidden in the BE nav pane.

In either case, it will still be visible in the FE and the Hidden checkbox in the nav pane will still be enabled.

My suspicion is that your C&R caused some corruption. Doing a C&R of the BE over a network is not a good idea.
Is there an MSysCompactError table in the BE? If so, check its contents.

Out of interest, please run this query on the BE tables that are now hidden in the FE

SELECT MSysObjects.Name, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

What is/are the Flags values for those tables? 2097152 (visible in the FE) or 2097160 (hidden in the FE) ... or something else?

 

[arnelgp]

Using dbHiddenObject (deep hiding) would have no effect on the already linked table in the FE.

i did not said it has effect on the fe. the op is in the BE doing the c&r.
read post #1.

 

[isladogs]

The OP didn't say whether he was in the BE compacting it or doing so externally.
He merely said he had compacted it ...but later said he cannot run C&R on it.
So it isn't clear what method he used.

Either way, using dbHiddenObject would completely hide those tables in the nav pane whether or not hidden objects were ticked.

 

[arnelgp]

When I use compact and repair on this file,

 

[isladogs]

As already stated, its irrelevant how the BE file was compacted.
Even if the OP had used dbHiddenObject, it wouldn't have the effect described in post #1 on the BE tables.
Nor would it have that outcome for the linked tables in the FE.

 

[Cotswold]

You aren't trying to repair and compact whilst the tables could possibly be open by one or more users are you?

 

[Pac-Man]

Thanks all for replying and sorry for delayed response to your queries. When I open the file (BE file with only tables, no code form or query) before C&R, it shows all the table and after C&R tables are hidden with no option to get unhide or even open the table.

did you change their attribute to dbhiddenObject?

No, I didn't use db hidden in my backend file. Tables are visible but got hidden on C&R.

Are you doing the C&R over the network?

I am doing C&R on local PC and NOT on LAN.

My suspicion is that your C&R caused some corruption. Doing a C&R of the BE over a network is not a good idea.
Is there an MSysCompactError table in the BE? If so, check its contents.

Out of interest, please run this query on the BE tables that are now hidden in the

Before C&R, there was a table MSYSCompactError but after C&R, there was none. I will run the query and report results here.

The OP didn't say whether he was in the BE compacting it or doing so externally.
He merely said he had compacted it ...but later said he cannot run C&R on it.
So it isn't clear what method he used.

I tried by just opening the BE and use Compact and Repair Database in file menu. I also tried vba code and both cause this issue.

You aren't trying to repair and compact whilst the tables could possibly be open by one or more users are you?

The file is on a local pc not connected to a network and only I am using it. I tried copy pasting the file on another location in the same pc and same happens.

Best Regards
Abdullah

 

[Pac-Man]

Just tested this and my suspicions were confirmed.
Using dbHiddenObject (deep hiding) would have no effect on the already linked table in the FE.
That is also the case if the linked table is just hidden in the BE nav pane.

In either case, it will still be visible in the FE and the Hidden checkbox in the nav pane will still be enabled.

My suspicion is that your C&R caused some corruption. Doing a C&R of the BE over a network is not a good idea.
Is there an MSysCompactError table in the BE? If so, check its contents.

Out of interest, please run this query on the BE tables that are now hidden in the FE

SELECT MSysObjects.Name, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

What is/are the Flags values for those tables? 2097152 (visible in the FE) or 2097160 (hidden in the FE) ... or something else?

I checked the query and there is no table with type =6. However hidden tables have flag value -2147483648 and there type is 1 (not 6). The tables that were not hidden by C&R have flag 0.

 

[isladogs]

Sorry. You would need to run that query from the FE ... not the BE. Type = 6 is for linked Access tables.

The Flags value -2147483648 is for certain read only system tables such as MSysObjects & MSysQueries.
Were those the tables you were referring to?
If any of your data tables have that Flags value, then your BE database has been corrupted.
I have seen that happen to a client once before and managed to fix it for him on that occasion.
See the testimonial by Doug Happ at Testimonials - Mendip Data Systems and the UA thread with the issue at UtterAccess.com

Your best solution may be to copy the data into new tables or a new blank database.
If that fails, you can try sending it to me to fix it if I can.

 

[Pac-Man]

Sorry. You would need to run that query from the FE ... not the BE. Type = 6 is for linked Access tables.

The Flags value -2147483648 is for certain read only system tables such as MSysObjects & MSysQueries.
Were those the tables you were referring to?
If any of your data tables have that Flags value, then your BE database has been corrupted.
I have seen that happen to a client once before and managed to fix it for him on that occasion.
See the testimonial by Doug Happ at Testimonials - Mendip Data Systems and the UA thread with the issue at UtterAccess.com

Your best solution may be to copy the data into new tables or a new blank database.
If that fails, you can try sending it to me to fix it if I can.

Thanks a lot for the suggestion and also for the offer to fix the db. Fortunately, this db was quite new and data was present only in 4 tables that were not affected by C&R (fortunately) and I exported these 4 tables into new BE with other tables in place already and now C&R do not affect this db. If tables with data were affected, then I have to have send you the db because table which are hidden couldn't be opened. Thanks again. What reasons could cause this corruption in future?

 

[isladogs]

Excellent. Glad you have a solution.
This time you were lucky. Next time, it may be more difficult to recover if you have a lot of data

Whilst a C&R usually works without issues, problems can occur and when they do an MSysCompactError table is normally created.
When that happens it should act as a red flag that something is wrong e.g. data corruption
At that point, you should have imported the tables into a new blank database rather than ignored it as you did.

The usual reasons leading to C&R problems are that it terminated early for some reason or was interrupted.
Typical causes might be when you compact:
a) over a network
b) using a WiFi connection
c) when someone else is using the BE
d) automatically on closing the file

Having said that, having data tables change to read only system tables is a very unusual problem.
You are only the 2nd person I've known to have experienced it in over 20 years working with Access.
Congratulations on achieving something unusual

 

[The_Doc_Man]

This is also why you ALWAYS make a simple copy of the file using the file interface of Windows itself before you do any major maintenance using Access. If the maintenance goes crazy on you, that copy is your backup of the "Before" status and you can restore to that copy. (And try again or try something else.)

 

[Pac-Man]

Thanks a lot every body for taking your time to reply and try to help. Thanks again.