Corrupted db

[ellenr]

I need expert opinion as to where to begin troubleshooting. Customer has both FE and BE on a server and runs FE via shortcut on her desktop. Today, out of the blue, it won't open, saying it cannot find the opening screen's form. I connect and look at the FE (by importing it into a new db since it won't open for me either). The tables (ie. links to BE) and queries are there but no forms, reports, etc. Mind you, the user isn't Einstein, I am not located in the same city and the server was upgraded a month ago. However, she had been using it daily without any problems being brought to my attention. Today, not only did the program not open, but she tells me that for some strange reason, she has noticed that there are two program backups on her desktop. Back to my question: is it likely that a corruption occurred due to a glitch on her computer, or in the communication between the computer and the server, or in the new server itself?

I would appreciate any comments any of you experts may have! Thanks

 

[The_Doc_Man]

First and most important question: With this FE/BE arrangement, is she sharing this with others? More specifically, if the BE is shared, is the FE ALSO shared?

If you have a shared FE, you have shared it incorrectly. If, on the other hand, your user is the only user thereof, then the problem has to be with the connection between the user's workstation and the hosting server.

The program backups are automatically generated when Access detects a case of corrupted data. DON'T DISCARD THE BACKUPS. You might be able to recover one of them and extract everything.

Two possible problems:

1. Improperly shared FE.

2. Flaky network between user workstation and server.

If you tell us which of the two (or both) apply, we can then suggest ways to avoid the issue. Without knowing which of the beasties we are facing, though, it becomes a much more difficult proposition.

You had a third option - a glitch in the server itself - but the odds do not favor that being the issue AND getting those backup copies. That is because making those copies takes time and if the server itself had gone cuckoo on you, Access would not have had the time to make those copies. Further, for a catastrophe at that level, your user would have seen something a lot more dramatic, I think. Like a total session disconnect or some type of blue screen message.

 

[Solo712]

I need expert opinion as to where to begin troubleshooting. Customer has both FE and BE on a server and runs FE via shortcut on her desktop. Today, out of the blue, it won't open, saying it cannot find the opening screen's form. I connect and look at the FE (by importing it into a new db since it won't open for me either). The tables (ie. links to BE) and queries are there but no forms, reports, etc. Mind you, the user isn't Einstein, I am not located in the same city and the server was upgraded a month ago. However, she had been using it daily without any problems being brought to my attention. Today, not only did the program not open, but she tells me that for some strange reason, she has noticed that there are two program backups on her desktop. Back to my question: is it likely that a corruption occurred due to a glitch on her computer, or in the communication between the computer and the server, or in the new server itself?

I would appreciate any comments any of you experts may have! Thanks

Hi Ellen,
has your user contacted whoever is in charge of that server? Can anyone check the path to the FE file? Is the file there physically ? If not, well, hope it is found or there is a backup. If the file is there, rename it and replace it with the FE backup in the path.

Best,
Jiri

 

[JHB]

.. I connect and look at the FE (by importing it into a new db since it won't open for me either). The tables (ie. links to BE) and queries are there but no forms, reports, etc. ...

When you imported it into a new database, did you've the opportunities to choose forms, reports and modules, (I mean were there some listed)?
Were there some difference in the size of the two backup she had notice?
Have you tried to open these two backup?

 

[ellenr]

The_Doc_Man I thank you. This morning when they opened I found out another user had begun using the db from another computer via a shortcut. Anyway, I have now put a copy of the FE on each computer. I (with your help) assume I have the problem solved.

 

[Mark_]

Next step is to set up a batch file that copies the FE from a central location to their computer, then opens the copy.

This lets you replace the FE if you make updates. It also replaces any FE they accidentally mess up.

 

[The_Doc_Man]

ellenr - separating the FE files goes a long way towards preventing that kind of interference. Not the only step, but a good first step.

Search this forum for the topic of "split databases" to see all of the things folks do. Then if you REALLY want to do yourself some good, look into ways to prevent users from opening that shared front end.

Mark's suggestion is one way - give them an icon to opens the database from a private copy of the FE

Since I'm a belt-and-suspenders type of guy, I also take the precaution of having the FE file have an "automagic" opening menu that, for its Form_Open routine, includes the test of where this FE file is located. And if it is on the same server for the BE file, you make that form execute an Application.Quit, perhaps with a snippy little message box that says "Don't do that, please." Or more appropriately worded as you choose.

Then there are things like assuring persistent connections, using UNC connection paths, and using queries rather than tables underneath forms. Don't ask me why, but queries somehow work better than direct BE table references even for the case of a single-table query. Crazy, right? But it works.

 

[Mark_]

Doc,

As I understand, the reason the query works better is because you are not recovering all records or all of each record, at least normally. If you try a query on Select * from table, does it give the same improvement?

On a more useful note for myself, can you share the code to check where the front end is opened at? I'd love to use that at work.

 

[The_Doc_Man]

Easier question first.

CurrentDB.Name is the device/path/name.type of the FE file.

If you have a back-end database linked in, it is Tables("table-name").Connect to find the device/path/name.type of the BE file.

Howerver, if you are using UNC mapping, you would have to parse out the computer name from the connect string, which will be in a sequence like:

//computer/more-path-data/database.type

In which case you might need to use Environ("ComputerName") to find out where the FE is hosted rather than the CurrentDB.Name method (because that would return a drive letter specification.)

As to why a query works better, when I was using linked tables for the first time, I noted that in some cases I needed to tell it where the tables were located even though I thought they were linked correctly. But if you have queries in the FE that point to the BE, it didn't ask. I THINK (and don't hold my feet to the fire on this) that since the table is in the BE, its TableDef is ALSO in the BE and the code in the FE has no freakin' clue as to where to look (because the table names are in the BE) - so it asks. BUT if the query in the FE references the table in the BE, it "remembers" better where to locate that stuff because if you look in the appropriate MSys tables, the table and its host computer are part of the definitions IN THE QUERY.

Your surmise that a query could return less data and thus be more efficient is true, but not always relevant. The cases where I noted the table location issue involved a query of the type SELECT X, Y, ..., Z FROM W ;. No WHERE clause involved. At MOST, an ORDER BY.

 

[isladogs]

As to why a query works better, when I was using linked tables for the first time, I noted that in some cases I needed to tell it where the tables were located even though I thought they were linked correctly. But if you have queries in the FE that point to the BE, it didn't ask. I THINK (and don't hold my feet to the fire on this) that since the table is in the BE, its TableDef is ALSO in the BE and the code in the FE has no freakin' clue as to where to look (because the table names are in the BE) - so it asks. BUT if the query in the FE references the table in the BE, it "remembers" better where to locate that stuff because if you look in the appropriate MSys tables, the table and its host computer are part of the definitions IN THE QUERY.

I'm not sure what you're saying here Doc.
The linked table info is in the Database field in MSysObjects
The query is also listed in MSysObjects and pulls the info it needs from other records in that table.

It should work perfectly using a linked table or a query based on that.
In fact you can also link to a query in another database and run that successfully for exactly the same reason

The MSysQueries table contains details of each field used in each query

 

[The_Doc_Man]

Minor correction: There is table info in the FE because there are TWO TableDefs - the "real" one in the BE and the linked one in the FE. (Too much Eggnog when I was posting. Hey - it only happens once a year - more or less.) The two TableDefs ARE different because the one in the BE is "local" whereas the one in the FE is not local. Internally, type 1 vs. type 6, if I recall that correctly. But that's immaterial.

I can only tell you that I saw what I saw.

The darned Form that was direct-linked to the BE table wanted a location (asked via an Input Box) the first time I used it in a session. Thereafter, it was OK until the next App Quit even if that form closed and was reopened in the same session.

BUT when I created the single-table query to the BE and used that FE query for the FE form, it was quiet as a mouse in his mouse-hole.

In fact you can also link to a query in another database and run that successfully for exactly the same reason

Yes. However, having a BE or external query invites issues since that means that in that context, the referenced QueryDef is being shared by multiple users (and thus lockable in a multi-user venue). The FE queries are private if the FE was distributed correctly. An FE query to a BE query, however, exposes you to other user locks.