Shared DB Slow When Opening Exclusive

[Guest]

On a daily basis I pull data off a mainframe and import it into a shared DB
(2002). I open the DB exclusively to ensure no one else gets in it while I'm
working in it. Once I'm done, I send everyone a "all clear" message, letting
them know they can go in.

Inevitably (once or twice a week) while I'm working in the database, I get a
call or email from someone saying "I can't get into the database - it says
'file in use'." -- At least I know it's working.

On the days when no one has tried to get in, the DB runs smoothly. From
beginning to end it takes about 30 minutes. On days where someone has tried
to get in and receives the 'file in use' message, the database slows to a
crawl; taking over 90 minutes to complete the same task.

Why is this happening?

 

[Joseph Meehan]

On a daily basis I pull data off a mainframe and import it into a
shared DB (2002). I open the DB exclusively to ensure no one else
gets in it while I'm working in it. Once I'm done, I send everyone a
"all clear" message, letting them know they can go in.

Inevitably (once or twice a week) while I'm working in the database,
I get a call or email from someone saying "I can't get into the
database - it says 'file in use'." -- At least I know it's working.

On the days when no one has tried to get in, the DB runs smoothly.
From beginning to end it takes about 30 minutes. On days where
someone has tried to get in and receives the 'file in use' message,
the database slows to a crawl; taking over 90 minutes to complete the
same task.

Why is this happening?

Are you using a split system where each user has their own copy of a
front end that is linked to the back end (the file with the data) located
on the server?

 

[kingston via AccessMonster.com]

Instead of opening the database in exclusive mode (you can still do this if
you want), change the name of the database temporarily. This way, everyone
else's links won't work and you'll avoid whatever server/network issue is
causing the poor performance.

 

[Guest]

It is split and users have the FE on their desktops. When I update the
tables, I am going directly into the BE.

Side question: If I go in as exclusive through the FE, isn't only my FE on
my desktop marked exclusive - not the BE?

 

[Guest]

I thought of that, but was hoping for a more permanent solution. The act of
naming and renaming the file every morning doesn't seem very practical.

 

[kingston via AccessMonster.com]

You can create a macro or subroutine that does this for you:

OldName = "Path\FileName.mdb"
NewName = "Path\FileName1.mdb"
Name OldName As NewName

Do your thing in code and reverse the above.

I thought of that, but was hoping for a more permanent solution. The act of
naming and renaming the file every morning doesn't seem very practical.

Instead of opening the database in exclusive mode (you can still do this if
you want), change the name of the database temporarily. This way, everyone

[quoted text clipped - 16 lines]

 

[Guest]

Hmm... What I'm doing is already in code, so I just need to add this on top
and bottom. I like that.
Thanks!

But I'd still like to know why it happens

You can create a macro or subroutine that does this for you:

OldName = "Path\FileName.mdb"
NewName = "Path\FileName1.mdb"
Name OldName As NewName

Do your thing in code and reverse the above.

I thought of that, but was hoping for a more permanent solution. The act of
naming and renaming the file every morning doesn't seem very practical.

Instead of opening the database in exclusive mode (you can still do this if
you want), change the name of the database temporarily. This way, everyone

[quoted text clipped - 16 lines]

Why is this happening?

 

[Joseph Meehan]

Are you using a split system where each user has their own copy of
a front end that is linked to the back end (the file with the data)
located on the server?

To be honest with you, I have never tested it. I generally avoided
needing to go exclusive for a number of reasons when I was working
professionally.

 

[aaron.kempf]

yes; it isn't very practical

if you setup SQL Server / MSDE you could use DTS in order to copy data
on a scheduled basis; with a wizard.. without writing a single line of
code.

if you used SQL Server / MSDE you could setup an automated backup /
archive of this DATA.

As it is; MDB shouldn't be used for anything in the real world
Performance is DISMAL using MDB anywhere for anything.

 

[Albert D. Kallal]

Have you tried a persistent connection? (that is the first thing I would
try).

So, when you run your update code, simply open a table (any table), and then
run your code....

(keep that table open during the process).

Try the above, there is a good chance that the above will remove the
delays...

 

[aaron.kempf]

ROFL

i dont need to do that with Access Data Projects.. is there a list of
all the workarounds you kids have to make because you use a half-rate
engine?

ROFL

-Aaron