Corrupt Front End

[JimS]

I still have some debugging to do, but I'm hoping you guys have some
directional help for me.

I have a FE/BE database. The BE is quite large (1.1GB). The FE is modest
(470MB). No tables at all in the FE. All linked to BE. System's been working
fine for months. It's in production. Users all use the same network-stored FE
(many users, difficult to control each having current FE. I understand this
is not ideal, but anyway....)

One form, "frmCreateShadowTables" has been in use for some time. It deletes
all records in a table in the BE (linked), then repopulates it. It's a
flattened table whose creation takes a couple hours to run, so it's recreated
each night. Been working fine for months.

Suddenly, a few days ago (maybe first of the year...) the FE began to show
massive corruption. The db comes up with error messages e.g.: "ID is not an
index on that table", "vba modules appear to be corrupted" (Paraphrased),
etc. The BE is perfect...nothing wrong at all. When the FE is restored from a
backup, it performs flawlessly.

We isolated the problem to the next time the FE is opened after a shadow
table create run (the form automatically exits when the table creation is
done.) No evidence of errors from the form. I'll have to run it offline to
see if error messages happen that no one sees....

Any ideas?

 

[Dale Fye]

My first recommendation is to give everyone their own FE. Take a look at
Tony Toews Auto Frontend Updater: http://www.granite.ab.ca/access/autofe.htm

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[JimS]

Dale, thank you for your response. I know it would be better to do that, but
I have a limited amount of money to work with. I can deliver $50k worth of
useful apps and get a PO for another $50k, or I can make the existing sytem
$50k more stable and maybe not get another PO for $50k. I'm also using A2007,
and I don't think (though I haven't tried yet) autofe works for A2007. If
FMS's autostart mechanism worked for A2007, I might convince them to spring
for it since it can be supported by FMS if there's a problem and I'm not
available.

Any other ideas?

 

[Douglas J. Steele]

There was a thread about this in another newsgroup a couple of days ago, and
Tony said it works with Access 2007.

I don't understand what "a limited amount of money to work with" has to do
with splitting the application into a front-end and back-end. (or did I
misinterpret your comment?)

 

[JimS]

The app is split. Correctly. But there is only one copy of the Production FE,
on the server. To implement AutoFE, since it's written in VB6, I may need to
involve the company's IT group. That's not happening. My user pays me to
avoid them. I am an IT veteran, and I understand. I am scrupulous to follow
standards that would not offend an IT audit, but if the IT group got
involved, it would cost the user so much money in overhead they'd have to
cancel the project. I have asked for permission to spend the time (=money) to
implement "local" front ends (they'd actually be on a network server as well,
just one copy per user...) but the cost makes them live with the few
incidents of instability or inconvenience they've had so far. This problem is
new and inexplicable.

Any ideas on the problem at hand?

 

[Dale Fye]

Jim,

It is imperative that you don't have multiple users using the same
front-end, located on the server. See Tony's site and read some of his
explaination for splitting the database.

Create your own. I've got one that similar to Tony's that I created before
I knew his was available. If you're maintaining a frontend that is 470MB,
this should be a piece of cake.

All it really takes is:
1. a table in the frontend with a Version number (I use a decimal, so I can
track incremental changes between major release changes).
2. a table in the frontend that identifies the network path to the most
recent version of the application stored on the server.

I generally configure 1 copy of the Front End updater so that it contains
the fully qualified path to the application front-end. I then either install
that, or put it somewhere that the users can install it on their computer.

So that whenever they run it, it checks to see whether they have a copy of
the front end on their system (I used to put the application in their
Window\Temp folder, but now I generally put it in their App Data folder. You
can find the path to this folder using the SpecialFolders API
(http://www.mvps.org/access/api/api0054.htm).

If not, I copy it from the network path. If they already have a copy, I
query the Version_Num field in that database, then query the Version_Num
field in the front-end that is on the server. If the one on the server is
larger, then I delete the front-end that is sitting on the users computer,
and copy the newer version to that same location. Finally, I use the
application.Followhyperlink method to open the front-end that is located on
the users computer, followed immediately by a docmd.Quit (to close the
front-end updater).

I have not really taken a look at Tony's code, but cannot imagine that it is
much more complicated than this.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[JimS]

OK, I'll do it. But you're all missing the point. Although the FE is
network-located, when this process is running, it's single-user (everyone
else has gone home is all), so it can't be the "multiple people using one
front end" issue. My user starts this process at 5 pm and it doesn't finish
until after 6 (creates two tables). Everyone goes home at 5. The last run was
on a Sunday evening, so no one was there.

My only guess is that the IT folks have implemented some new process that
runs periodically and can't deal with an open .mdb and corrupts it somehow.
They do have a feature that "saves" "versions" of network served files. This
feature has made my life easier since it seems to do its saves intelligently
and frequently. Still, I wonder if it doesn't cause problems for open .mdb's?
Of course, you can't know, and I am not allowed to ask, but whatever the
issue, it's clearly not related to the multiuser environment within A2007.

 

[Douglas J. Steele]

Dale's done an admirable job listing alternatives.

 

[Dale Fye]

Your IT guys may have done what ours started doing. Somehow, they are able
to shutdown our computers from their systems. They do this at 6:30 PM, but
preface it with some sort of warning message. If we are still working, and
click the button that says not to close down, we can keep on working, but if
we fail to click that button, our computer gets shut down. It doesn't make
sense that they would be doing this with a server though, so they may be
doing some other maintenance that is crashing the application.

Also, one of the purposes of putting the front-end on the local users
machine, rather than on a network server is to reduce network traffic and
speed up the application. If the application has to pass all of the form or
report rendering information across the network, it increases load and slows
down the whole process.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[JimS]

I'll put the FE on the local machine if it doesn't violate any IT policies. I
respect those guys and want to follow their rules where I know them.

I don't see how network traffic is an issue, though. The vast majority of
the FE is memory-resident, and what isn't is small. Again, there are NO local
tables in the FE.

If the FE is executed from a client whose "home" drive is a network server
path, it would seem (for these puposes, anyway) to be very close to having
the FE on the client's "C" drive.

The BE will always be on a network share, of course.

 

[David W. Fenton]

Users all use the same network-stored FE
(many users, difficult to control each having current FE. I
understand this is not ideal, but anyway....)

I, for one, quit reading when I see this kind of comment. If you
can't get something this basic right, then there's no point in
looking for workarounds.

 

[David W. Fenton]

The BE will always be on a network share, of course.

There's one thing that's not clear to me: where is the work table
that is being processed? Is it in the front end? The back end?
Either of those is THE WRONG PLACE for a work table. A table in
which all the records are deleted and replaced belongs in a separate
back end, precisely because it will otherwise lead to huge, huge
bloat.

As to the suggestion that perhaps your IT folks are shutting down
everyone's workstation, you could get around that by running the
process in a Remote Desktop session on the server. If the RDP
session is running on the file server, this would also make it
substantially more efficient, as all the data would be local and
none of it would be pulled across the LAN.

 

[JimS]

Dave,
I appreciate your responding to my post. I know it's frustrating to see
others appear to disregard what you see as a fundamental design requirement.
I have been developing this project for almost two years now, and I think
it's fundamentally well designed, pretty much fully normalized, and pretty
robust for a single-programmer project given the constraints I've had.

The project has evolved from three separate db's, all "temporary", and
"localized", to a fairly massive multi-user system with multiple classes of
users from developers and administrators to a number of end-user project
managers and trades supervisors. Just managing security has been a challenge.

It's especially hard because I have no control or access to IT tools. I
cannot install any unauthorized software on my company-owned computer, and I
access the entire environment through Citrix and Remote Desktop.

But enough whining. I will implement Tony's front-end manager as soon as
practical.

 

[Tony Toews [MVP]]

The app is split. Correctly. But there is only one copy of the Production FE,
on the server. To implement AutoFE, since it's written in VB6, I may need to
involve the company's IT group.

I'm not sure why the IT group is required. Copy the StartMDB.exe to
the file server and setup the INI file. The VB6 runtime has been
included in the Windows 2000 OS and newer and is even present in the
Windows 7 beta.

My user pays me to avoid [the IT group]/

Most understandable.

I have asked for permission to spend the time (=money) to
implement "local" front ends (they'd actually be on a network server as well,
just one copy per user...) but the cost makes them live with the few
incidents of instability or inconvenience they've had so far. This problem is
new and inexplicable.

The cost to implement the AutoFEUpdater should be an hour or so of
your time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I have a FE/BE database. The BE is quite large (1.1GB). The FE is modest
(470MB).

470 Mb? That's huge. Do you have embedded graphics on forms, logos
on reports?

One form, "frmCreateShadowTables" has been in use for some time. It deletes
all records in a table in the BE (linked), then repopulates it. It's a
flattened table whose creation takes a couple hours to run, so it's recreated
each night. Been working fine for months.

I'd certainly put that in it's own BE so it's easier to manage just
that file. I'd be real tempted to either compact that MDB after the
record delete and before the record creation is performed or to create
the MDB and table layout fresh every. See the TempTables.MDB page at
my website which illustrates how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Suddenly, a few days ago (maybe first of the year...) the FE began to show
massive corruption. The db comes up with error messages e.g.: "ID is not an
index on that table", "vba modules appear to be corrupted" (Paraphrased),
etc. The BE is perfect...nothing wrong at all. When the FE is restored from a
backup, it performs flawlessly.

The first message is a standard sign of corruption. Second message
sounds like i too.

We isolated the problem to the next time the FE is opened after a shadow
table create run (the form automatically exits when the table creation is
done.) No evidence of errors from the form. I'll have to run it offline to
see if error messages happen that no one sees....

Any ideas?

No. What you're describing doesn't make a lot of sense. I'm
wondering if deleting and recreating the linked table in the FE after
the table has been created would help.

Nevertheless, as others have stated, give each user their own copy of
the FE. That may solve this problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I don't think (though I haven't tried yet) autofe works for A2007.

The Auto FE Updater works for A2007 and Windows Vista.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[JimS]

Tony,
I came back to the forum today without knowing you'd responded to my post.
I really appreciate the time you took to respond. I also am very embarassed
that I hadn't looked at your autofe package more carefully. Frankly, it's
brilliant (as they say in the UK....)

I was able to implement it in a test environment in under an hour. It
seems to work flawlessly, and I will put it in production as soon as
practical. I am leaving town for 4 weeks coming up, so I'll likely wait until
I return.

In case no one discovered this before, in A2007, you must identify the
locations of databases you "trust" in order to have the vba code work. One
issue is that the new location on the local drive is not yet "trusted" for my
users, so I'll have to ask my client to visit each user and set up security
for him/her.

I wonder if some really smart programmer could find a way to at least
partially
automate this?

Thanks again, Tony.

Jim

 

[Douglas J. Steele]

You should be able to simply run the .reg file Jeff Conrad has at
http://accessjunkie.com/faq_33.aspx

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Tony Toews [MVP]]

I came back to the forum today without knowing you'd responded to my post.
I really appreciate the time you took to respond. I also am very embarassed
that I hadn't looked at your autofe package more carefully. Frankly, it's
brilliant (as they say in the UK....)

Not a problem.

I was able to implement it in a test environment in under an hour. It
seems to work flawlessly, and I will put it in production as soon as
practical. I am leaving town for 4 weeks coming up, so I'll likely wait until
I return.

Glad to hear it was relatively easy to setup. And waiting is most
understandable.

In case no one discovered this before, in A2007, you must identify the
locations of databases you "trust" in order to have the vba code work. One
issue is that the new location on the local drive is not yet "trusted" for my
users, so I'll have to ask my client to visit each user and set up security
for him/her.

BTW I'm now suggesting the %APPDATA%\Your app Name folder be the
folder to which you copy the Access FE. I need to create a page one
of these days on that topic.

I wonder if some really smart programmer could find a way to at least
partially automate this?

I could probably put this logic in the Auto FE Updater. Indeed there
is some registry logic in there but it's been so long since I looked
at it that it may not work. It also almost certainly doesn't handle
the %APPDATA% logic that would be required if you were to use that as
the FE folder.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

You should be able to simply run the .reg file Jeff Conrad has at
http://accessjunkie.com/faq_33.aspx

Note that that page is slightly out of date. You can use any key
below
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted
Locations to store your location.

For example rather than using Location0 as in Jeff's example you could
use a GUID or "Your App Name".

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/