I know, I know, I know....

[dotolee]

So... I assumed, being a novice that it was ok to just build a db with forms ... and allow multiple users - only up to 2 at a time... to use this database.
It's now sitting on a shared drive. But, they're getting error messages when they try to open the database simultaneously. The odd thing is that this only happens on one of the MS Access databases I've built for them. The other one, in the same directory, opened by the same users... is fine.
I've read that this architecture is bad design - i really should only put the back end tables on the network and move all the forms to a local database for each user ... and then link to the back end.
Unfortunately, my due date is tomorrow and I'm still building some other crazy forms they've requested.

My questions are:
1) how do i prevent this lock from being created so that both users can access the database off the network.
2) what is the real impact of this type of design? I will revisit this issue once the monkey is off my back... but I'd like to know what I'm dealing with here.

Thanks guys. I apologize if I'm asking some pretty basic questions.

 

[TheAceMan1]

How are ya dotolee . . .

Nothing you can do until you split the Db and distribute the frontend!

Be aware: the time it takes to split a Db is in minutes. Even less to link the backend to the distributed front.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dotolee]

Oh. So no reason why the other database allows multiple users without any problems?
Sorry for being thick but I don't get your "Be aware" comment. Is this a heads up that it's not as easy as it seems to split the database, or it's so easy that i should get off my lazy rear end and do it?
Thanks!

 

[TheAceMan1]

dotolee . . .

The latter! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dotolee]

heh heh heh. I'll get on it.
Thanks.
I'd be interested in getting your comments on the following article about splitting databases... being an MS Access novice, I'm not sure how much weight to give to these comments - especially the ones about poor performance.

http://www.granite.ab.ca/access/splitapp/index.htm

 

[TheAceMan1]

dotolee . . .

Realize . . . in the days of 97, processors were alot slower that today (typical 200Mhz and let alone where up to 2007). Yes there was a slight hit in speed, but no longer with the speed deamons of today.

Admittedly there's a bit more than meets the eye, however concurency of users demands splitting. Be sure to backup before you get started.

There is one problem you'll come across if your using autonumber for your primary keys. See Why AutoNumber shouldn't be used in MultiUser databases about this.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

There's absolutely no downside to splitting up the database. It's a no brainer decision. Using the wizard it should take you 30 seconds.

 

[dotolee]

Thanks for all the comments guys. Here's the thing. I tried (not using the wizard) creating a front end database by:
copying the existing database from the network share to my desktop. Then I deleted all tables and linked via unc paths back to the network database for the same tables.
It works ... but it is very slow.
Will using the wizard make a difference? I'm going to give it a try anyways. I'm running Access 2003.
Any comments? It is always possible that I messed something up... but I tried my best to follow a how to article step by step.
Cheers.

 

[JoeAtWork]

The wizard does the same thing as linking. You are limited by the speed of your network.

Adding indexes to your tables may help.

If you open a linked table directly is that slow, or are you seeing the slowness mainly in forms and queries?

 

[dotolee]

Hi JoeAtWork.
I'm not at work right now... so I can't tell you exactly but I only tried working through the forms, mimicking what my end users would be doing.
I'll try adding some indexing to see if that helps.

 

[JoeAtWork]

Try opening one of the larger linked tables directly. If you are able to scroll through and edit records without much trouble, then it is more likely the efficiency of the queries that your forms are bound to.
Post the query for one of the forms that is working slowly.

 

[dotolee]

Ok, Thanks JoeAtWork.
I'll post the query tomorrow morning.
Thanks again.