Disappearing tables in Access 2002 split database

[pgruenke]

Several years ago I created a split database. The front end resides on each
individual's pc. The back-end resides on a server. Until the last several
weeks - all was well. If mistakes were made in data entry I could open the
back-end and make corrections. Then, one day, I opened the back-end and it
was completely empty - no tables visible. The size of the file indicated a
large amount of data. And when I went through the front end all the linked
tables were present and contained data. What do I need to do to be able to
access the data from the back-end. Its fine for now but I need it fixed
prior to being required to make some changes to the tables. Please help Pat

 

[Tom van Stiphout]

On Tue, 10 Mar 2009 14:26:07 -0700, pgruenke

Of course you are making frequent backups.
Repair & Compact the backend database.

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

First get everyone out of the database, both FE and BE.

Second make a complete copy of both the FE and BE.

Third open up the BE and make sure that it's just not something simple.
Press the F11 key to show the database window if you don't already see it. Go
to the tables tab on the database window and try moving the scroll bars back
and forth. If that doesn't work go up to View and chose Details or another
one of the views.

BTW if you can't see the database window, that another problem.

If that doesn't work, go up to Window on the menu bar and see if the work
Unhide is grayed out. If not some things, like your tables, may be hidden.

You can also go up to Tools, Options, View table and check the Hidden and
System boxes.

 

[pgruenke]

Jerry, the database window shows when the backend opens. The only option
available in Tables, Queries, Forms, etc. is to create - nothing else is
visible. Unhide is grayed out. Nothing changes when I choose Tools,
Options, View table and check the Hidden and System boxes - nothing changes.

And Tom, I made a copy of the back-end - all 314,336 Kb - and try to Compact
and Repair - I get the error message "could not create; no modify design
permission for table or query 'MSysAccessObjects'. I click ok and get that
message once more and the database window is gone. Now my file is listed as
112 Kb and when I try to open it I continue to get the same error message.

Please keep in mind that all the data is viewable from the linked tables in
the front end so I know its there. Thanks Pat

 

[pgruenke]

Some more insight - this database at one time was replicated. I stopped the
replication (maybe 3 years ago) but did not delete the replication fields
from the tables. The only way I can reconstruct the tables now appears to be
to create make-table queries in the front end without adding the replication
data fields. I would then delete the current table links to the broken BE,
rename the newly created tables to what they were named previously, and
resplit the database. Do you see any problems with doing that? Any idea why
the problem surfaced now?

 

[Jerry Whittle]

Try opening a new database file and importing all the tables into it.

 

[pgruenke]

I get the message "record is deleted" as soon as I click on the database to
import it. The window opens to where usually all the tables would be listed
and its blank. When I click on the Queries tab I get the same error message,
the window switches to the Query tab, and its empty. Pat

 

[Jerry Whittle]

Wow. Unfortunately Tom was probably correct and you have a severe corruption
issue. I like your idea of running make table queries in the FE to extract
the data.

Have you made a recent backup? That might be a better idea. I suggest that
you do make frequent backups to another hard drive or computer. Often the
issue that causes such corruption has something to do with the network or
hardware. The problem can happen yet again.