Solved Back End Copies not readable ...

[Marshall Brooks]

This is really weird. Our database is a split format .accdb with the backend on a network share drive.

I use a program called compact-a-list to make nightly backups of the database. http://www.peterssoftware.com/cal.htm (with some modifications to use 7-zip and C&R locally, etc).

Last week, I noticed that I can only read the backups from last Monday's file - i.e. if I open Monday night's file by double-clicking it, I see all of my tables (19, but there are usually 7 that I am concerned with). If I double-click Tuesday through Thursday's files, the Tables field in the Navigation pane is blank.

More info:

• The Monday night backups are done inplant over Ethernet while Tuesday to Thursday are over VPN. Not sure if this is related. I can open the 4/27 backup which would have been over VPN. Issue appears to have just started last week. I'm thinking it is NOT VPN related. I'll be able to tell tomorrow after tonight's Ethernet backup.
• I have Access configured to show Hidden and Network files.
• A co-worker confirmed the same thing.
• It is not a Compact-A-List issue. I see the same thing if I go to restore previous versions of the backend folder. Files from 5/1 and prior work fine. Later files do not.
• I see the same issue if I copy the files to my desktop.
• I see the same issue if I view the files under Citrix (different OS, different Access program, different .zip file program.)
• We do a nightly export to Excel and that is working without issue.
• The working and non-working backup files appear to be roughly the same file size.
• The backups DO somewhat work. I.e. If I open a copy of the front-end and use Linked Table Manager to point to the extracted non-viewable backup file, the tables are visible in the front-end, so the data IS there, just not viewable now.
• https://answers.microsoft.com/en-us...back-end/071669bb-bbba-4294-a91b-8af387825a12 - Similar, but not identical issue from 10 years ago and not definitively resolved.

I haven't tried re-building the back-end into a blank database - I suspect that will be the next step, but wanted to see if anyone had a simpler suggestion first.

I DO see the same issue if I open the active back end file (not a previous backup of it). So I'm not sure how well copying to a blank database will work, unless the import program can read the tables, or unless I can copy them from the front-end file.

All help appreciated.

 

[Josef P.]

Do I understand this correctly: You don't see any tables in the backend?
What results of CurrentDb.Tabledefs.Count when you run it in the immediate window?
Maybe only the display in the navigation area is set incorrectly.

 

[The_Doc_Man]

Let's say this: IF it is VPN related, anything else that rides the VPN would show corresponding difficulties. As long as your Ethernet connection is up, if you don't see any errors during the transfers, the files should be OK. If done properly, a VPN carries traffic in the same way as a fiber or copper cable carries traffic, with respect to the protocols of that traffic. The VPN encapsulation is like an added layer that shields the inner traffic, but with respect to the traffic, it might as well be naked copper.

If there are log files you can examine, see if you had any errors during execution of your copy process on nights that exhibit the problem. If you have the means to do so, determine the date and time of transfers that exhibit this problem and try to search the system event logs for those dates and times. The event viewer is available as START >> WINDOWS ADMINISTRATIVE TOOLS >> EVENT VIEWER. You might find several different sets of logs that are populated. Access logs will be in the SYSTEM event logs or (rarely) in APPLICATION logs. Event logs are sorted by date/time so if you can establish a time for the transfers, it shouldn't take long to either find events or know that none occurred in the given time frame.

I followed the link to the app you use. It is apparently written in Access. See if IT keeps any logs. Again, you are looking for evidence that it tried but failed to do something with one of your problematic files. A question comes to mind as well. Since that is written in Access, if it is trying to use Access to do some of its backups, the question is whether the main file now has an extra table that lists errors incurred during a backup operation. It is possible that the log you are seeking is in the individually backed up DB files. I wonder if the problem with the backups is that the original file is somehow corrupted, since there are known problems with automated Compact & Repair operations.

 

[Marshall Brooks]

Correct:

Assuming I did it correctly:

 

[Josef P.]

Call with "?":
?Currentdb.tabledefs.Count

 

[Marshall Brooks]

With ? it returns 32. (Which would be about right including the MSys ones.)

@The_Doc_Man. I do not think it is VPN-related. The CAL program does do logging and it doesn't show there there were any errors.

I wasn't aware of known errors with automated C&R operations.

I think it's a corrupt backend.

More info:

The front end works with about 7 linked tables in the back-end. There are about 12 tables in the backend that it copies over and works with locally (for speed).

I released a new version of the front-end Monday (4-May). Shortly after I released it, it gave me/us an error with Invalid Argument on one of the tables that it imports (as opposed to linking). I wanted to C&R the backend, but I couldn't get one of the users to log out to clear the lock file, so I copied the file locally, C&R'd it and copied it back to the network (bad practice, I know). One of the records in the table had #### for every field, so I deleted that and I had to re-input two of the records. Later that day, we had an "unrecognizable state" error, so I got an admin to kick the other user out and did another C&R on the backend and it seemed to be working okay. (But both of those errors happened on the same day that the files were not readable. (It was Tuesday that we were having the invalid argument errors ...)

 

[Marshall Brooks]

The reason I don't think it is VPN related is I see the same issues with the network backup files from any date (not the CAL files) - both of them show the same issue.

 

[Josef P.]

Maybe someone read Colin's recommendations and hid the table.

 

[The_Doc_Man]

I wasn't aware of known errors with automated C&R operations.

On this forum we have seen many times that an automated (= unattended) C&R will fail when corruption is involved. The step-by-step copy process won't copy a corrupted object correctly. Either it stops the copy in the middle of the object if that object is multi-membered, like a table, or it refuses to copy the object at all. In either case, things that would have been copied later are not copied. But there should be some indication somewhere of the error - unless notifications are disabled during the process.

 

[Marshall Brooks]

@ Josef P. - I have the navigation pane set to show hidden and system tables - unless MS Access somehow automated the process to make them deep hidden ...

@ The_Doc_Man - C-A-L does report if the C&R process fails (typically if someone else has the database open). I'm sure it is also possible that the database has some sort of corruption and the C&R doesn't repair the corruption and so the errors copy over with the process.

@all - I did an experiment with one of the backup copies of the database (that was not displaying tables) and I opened a blank database and copied the tables into it, and saved it and the tables are displayed and readable and look okay. I think I just need to do that with the backend and then it will probably resolve the issue. (But I'm open to other suggestions)...

 

[Marshall Brooks]

Marking this as solved.

I got exclusive access to the backend, copied it to a local directory, imported the tables into a blank database and saved that, and put that file back on the network.

Last night's backup looks fine.

That was the hard-wired backup. I'll know tomorrow whether the VPN backup works, but I expect it to do so.

I'll report the results.

Thanks to all for the assistance.

 

[isladogs]

Maybe someone read Colin's recommendations and hid the table.

@Marshall Brooks
I know Josef's comment was intended to be facetious but you could try the free version of my utility:

Table Analyzer & Viewer

This utility allows you to quickly analyze the tables in any database. For each table, the table type, number of fields and records are displayed. You can also determine the size of each table, view its data and the table design. All field types can be handled including complex and calculated fields

www.isladogs.co.uk

Does that find the tables?

 

[Marshall Brooks]

@isladogs

Actually, it does not. It shows this:

Also - I forgot to mention that when I imported the tables into the blank database with the test/backup file yesterday, I got a similar message when I tried to select all tables.

I ASSuME'd that the message just meant that the MSys tables could not be imported, so I only selected the tables which I had created.

Based on your question and the result, I'm now thinking that perhaps the MSysObjects table was corrupt and that was why I didn't see the tables in the navigation pane, but they worked fine when linked through the front-end or importing them into a blank database.

 

[isladogs]

Hi
My utility works by linking to MSysObjects in the selected database which clearly wasn't possible for some reason in your case
So either the MSysObjects table is corrupted as you suggested or someone really has deep hidden that system table (which I think is very unlikely)

When you import tables into a blank database, new system tables are created automatically

EDIT:
As a test, I set MSysObjects as deep hidden in a test database then ran the utility on that db.
The code still worked fine. So there is definitely another reason why yours failed

 

[KitaYama]

I'm now thinking that perhaps the MSysObjects table was corrupt

I don't think so. I was receiving the same error in following thread and @Josef P. solved the puzzle

Solved - Getting the count of tables in an external database

How can I get the count of user defined tables in an external database? Dim dbs As DAO.Database Dim rs As DAO.Recordset Dim sql as string Set dbs = OpenDatabase(PathToMyTarget) Debug.Print dbs.Name Debug.Print dbs.QueryDefs.Count Debug.Print dbs.TableDefs.Count...

www.access-programmers.co.uk

 

[Marshall Brooks]

So either the MSysObjects table is corrupted as you suggested or someone really has deep hidden that system table (which I think is very unlikely)

To clarify: I got the error with your utility on MSysObjects and when I tried to import MSysObjects, but I couldn't see ANY of the tables in the Navigation pane.

So someone would have had to have deep-hidden all 32 tables, and the only one with write access to the backend and close to enough knowledge to do that would be either me or the system somehow did it automatically, and I don't see either one being likely.

Either way, it seems to be fixed now.

 

[isladogs]

Did you see my recent edit above?
The system won't ever deep hide tables automatically.

I use code similar to the working version referenced by @KitaYama so that doesn't explain the error either.
As you now have a working database, its probably not worth spending additional time on this

 

[Marshall Brooks]

Table Analyzer and Viewer worked fine on last night's backup of the backend (after importing into a blank database.)

One other thing, but as you suggested, I'm not going to spend additional time on this ...
?Currentdb.tabledefs.Count in the immediate window of the non-readable database showed 32 tables.

The same command and Table Analyzer and Viewer shows 31 tables in last nights backup - so there might have been an additional table in the non-working database that was corrupt. (There also MIGHT have been a table of "Paste Errors" or similar that I didn't care about and didn't copy over.)

 

[Marshall Brooks]

Last nights backup over VPN was readable today also. I'm considering this solved (until the next issue)!!! Thanks again for all the assistance!

 

[isladogs]