Problem opening mdb database after editing db

[Yblitzka]

Here's the situation:
We have an SQL server back end db with a MDB front end.
Each computer has it's own copy of the front end. We update this front end
almost on a daily basis.

Our distribution method for each computer is a batch file - everyone runs
the batch file and an updated version of the front end is copied down to the
computers.

Our problem comes in when the users try to run the database app after this
"update". The front end db takes forever to open (on an older win2K machine,
upwards of 10 minutes!). The strange part being, once the db has opened
successfully, we can close it and reopen it in the exact same method (with
the icon on the desktop) and it opens up in about 10 seconds or so. And it
will contuinue to open up lickety-split every time until we update again.

I think that part of thre problem is the size of the DB. The wait time on
that first open has been getting longer as we add more to the db (we compact
the updated version on a regular basis). We were getting errors regarding
"cannot find file or one of itrs components" errors on that first open, which
I have traced to DDE settings in access. I've fixed that, but the problem
with that first open taking forever still persists.

We have a wide selection of different setups.

We have Windows 2000 and Windows XP machines.
Each has either Office 2003 (professional ed) or Office XP (Professional ed)
In some cases, the app is running with either a /runtime switch ion the
command line on the shortcut or it is set to open with Access 2002 runtime in
the command line. (on both '03 and XP office installations).
In other cases the app is running in access with no runtime switch on.

I've seen this problem in all of the above settings, and I've had it not
occur in multiple scenarios (most often with the Office XP installs). Can
anybody shed some light on why this db keeps behaving like this?

PS - opening the item from Access itself doesn't make the problem go away -
it still takes forever.

TIA,

~Yb

 

[David F Cox]

virus scan?

 

[Bill Mosca, MS Access MVP]

Lickity-split! That was one of my grandmother's favorite expressions. Thansk
for bringing that wonderful person to my mind!

It sounds like the front ends might be doing some relinking. Is there any
code in the startup that is causing the bog-down?

 

[dbahooker]

you probably got some dipshit that is refreshing and relinking tables..
and importing querydefs; etc

I would reccomend throwing away your current solution and rewriting it
as an Access Data Project


you don't have ANY deployment nightmares with ADP

-Aaron

 

[Armen Stein]

Lickity-split! That was one of my grandmother's favorite expressions. Thansk
for bringing that wonderful person to my mind!

It sounds like the front ends might be doing some relinking. Is there any
code in the startup that is causing the bog-down?

A couple of other things to check:

Make sure Name Autocorrect is turned OFF. (Tools|Options)

Distribute an MDE instead of an MDE to be sure that the database isn't
becoming decompiled, then recompiling when it is first opened (though
that shouldn't take 10 minutes!)

 

[Yblitzka]

To reply to all the suggestions thus far:

dbahooker: Rewriting is not an option. This was not an app we built - it was
built for us, as a software product. We do have the rights to modify it as we
please, but we can't ask them to make a completely different program, and we
don't have the time or the manpower to completely rewrite the app - everyone
uses it all day everyday at 30 to 50 workstations. A nice thought, but a pipe
dream, not an option.

David F Cox: Hmmmm... I was going to rule that out, but maybe, because the
item is being copied down from the network, it's being rescanned. I'll have
to check that. That just might be it...

Armen: I will double check the settings, but when we set up the app to run
on the computers, that's one of the things we set, along with killing
personalized menus, setting up a System ODBC connection, and changing the
date format (this app is picky on the short date format). I'll check, but I'm
betting that the Autocorrect setting is not causing it. And the MDE vs. MDB
debate has crossed our minds, though we're concerned with comaptibility
issues. I know there are some headaches across Access versions with dbs made
in different versions, for different versions. That may be an option as the
thing keeps growing.

Bill: You're welcome!
And as for startup code, why would it run only the first time after we've
updated the db? Why not every time? It isn't some wacky temp file or caching
issue. The issue doesn't occur because we've rebooted, just when the db has
been updated since the last time that user opened it. We don't have any
startup parameters (other than the /runtime switch on some machines.)

I'll post what results I get with my checking...
Thanks!

~yb

 

[Bill Mosca, MS Access MVP]

yb

A conditional relinking might be going on. I have apps where I store a value
for comparison to determine if the tables should be relinked or not. With
some of the apps, a broken link triggers it. With a backend in SQL server,
if I make a change to a table, the relink must be forced or the app will not
see the change. Weird, but true. In this case, the app checks if I set that
flag to true or false.

It just seems that if the delay happens sporatically, there might be some
sort of check being made.

Just something to think about.

 

[dbahooker]

it is an option.

you run around all day putting out fires and yet you think that you
don't have time to solve the problem?

the mort IMPORTANT your app is-- the more CRITICAL it is to move to a
decent platform.

MDB is a piece of shit ass platform and anyone that uses it for
anything should be fired immediately.

I mean seriously here.

It's not stable; it's not reliable.

You should SUE the person that wrote it for being a SCAM.