Ms. Access database problem

[Orchid]

Hello,
I have a MS. Access database in XP. When I open the database in design
view, users will get an error message & cannot even open the database.
Is there any way to solve this problem?

Thanks for your help in advance.

Orchid

[pietlinden@hotmail.com]

Re: Ms. Access database problem

What error message? Did you try the standard compact/repair routine?

[Orchid]

Re: Ms. Access database problem

Thanks for your reply!
The error message is: "The database has been placed in a state by user
'Admin' on machine 'Name' that prevents it from being opened or
locked."

Yeah, I tried the standard compact/repair routine, (which is under
Tool, Database Utilities, Compact and Repair Database...)but still got
the same error message.

[khayman_v@yahoo.com]

Re: Ms. Access database problem

"Orchid" <yhtong86@yahoo .com> wrote in message news:<111219454 2.469115.105910 @g14g2000cwa.go oglegroups.com> ...[color=blue]
> Thanks for your reply!
> The error message is: "The database has been placed in a state by user
> 'Admin' on machine 'Name' that prevents it from being opened or
> locked."
>
> Yeah, I tried the standard compact/repair routine, (which is under
> Tool, Database Utilities, Compact and Repair Database...)but still got
> the same error message.[/color]

try deleting the *.ldb file on the machine that has it opened or locked.

[Orchid]

Re: Ms. Access database problem

I tried to delete the *.ldb file, but got error message: "It is being
used by another person or program. Close any programs that might be
using the file and try again."
The *.ldb is a file automatically created after opening a *mdb file in
a directory, once the *mdb file is closed, the *ldb file is disappear.

Is there any way that the users can open the database even I am on the
design view? Is it dealing with Security of the database?

Your help is appreciated!

Regards,
Orchid

[Tim Marshall]

Re: Ms. Access database problem

Orchid wrote:
[color=blue]
> Hello,
> I have a MS. Access database in XP. When I open the database in design
> view, users will get an error message & cannot even open the database.
> Is there any way to solve this problem?[/color]

No. Do you really want users to be getting into the database when you
are doing design work on it?

IS this a back end database or is it an application that everyone
accesses on a network?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Orchid]

Re: Ms. Access database problem

I want the users to be able to log into the database at any time even
when I am working on the design view. I want to eliminate the calls
from users saying they cannot open the database.

The database is on the network share drive. I have the database set up
that the users cannot view the design views,tables, and queries. When
they open the database, they will see a form & double click from a list
to open forms & reports. so is it consider to be an application? I
am not sure.

[Alan Webb]

Re: Ms. Access database problem

Orchid,
Look for JetComp on Microsoft's web site. It isn't a sure thing but there
are .mdb's that it will recover which cannot be recovered by Access. It's
free and it it works, very cool.

--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS

"Orchid" <yhtong86@yahoo .com> wrote in message
news:1112211469 .584818.118280@ l41g2000cwc.goo glegroups.com.. .[color=blue]
>I tried to delete the *.ldb file, but got error message: "It is being
> used by another person or program. Close any programs that might be
> using the file and try again."
> The *.ldb is a file automatically created after opening a *mdb file in
> a directory, once the *mdb file is closed, the *ldb file is disappear.
>
> Is there any way that the users can open the database even I am on the
> design view? Is it dealing with Security of the database?
>
> Your help is appreciated!
>
> Regards,
> Orchid
>[/color]

[Ronald W. Roberts]

Re: Ms. Access database problem

Orchid wrote:
[color=blue]
>I want the users to be able to log into the database at any time even
>when I am working on the design view. I want to eliminate the calls
>from users saying they cannot open the database.
>
>The database is on the network share drive. I have the database set up
>that the users cannot view the design views,tables, and queries. When
>they open the database, they will see a form & double click from a list
>to open forms & reports. so is it consider to be an application? I
>am not sure.
>
>
>[/color]
You need to split the database into a front-end and a back-end.
The FE will contain the Forms, Queries, Macros, Reports and Modules.
The BE will contrin only the tables.
Put the BE on the network and the FE on each users pc.
Link the FE to the BE.

This way you can make changes to your copy of the FE and then give each user
an new compy when you have completed making you changes.

Ron

--
Ronald W. Roberts
Roberts Communication
rwr@robcom.com

[khayman_v@yahoo.com]

Re: Ms. Access database problem

"Orchid" <yhtong86@yahoo .com> wrote in message news:<111222080 9.905390.4100@l 41g2000cwc.goog legroups.com>.. .[color=blue]
> I want the users to be able to log into the database at any time even
> when I am working on the design view. I want to eliminate the calls
> from users saying they cannot open the database.
>
> The database is on the network share drive. I have the database set up
> that the users cannot view the design views,tables, and queries. When
> they open the database, they will see a form & double click from a list
> to open forms & reports. so is it consider to be an application? I
> am not sure.[/color]

Has this worked before where you are in design view and others can
open the database without the error? If not maybe being in design
view locks the database.

[Orchid]

Re: Ms. Access database problem

I have more than 15 users & in different locations. It will be a
headache of doing the copies to each of them. Is there any other way?
Thanks for your help!

[Orchid]

Re: Ms. Access database problem

No, it always has problem to open the database if the design view is
open.

[dkintheuk]

Re: Ms. Access database problem

Orchid,

Are you saying that the users all have theie own versions of the
database at their own locations? If not, and you actually have all the
users going into the same database on a network location then there is
a recommended way of handling this.

You create the database as you have done but once complete (as it will
be at the moment) you take a copy of it and empty all data from it.

This copy has all its tables removed and you then LINK to the tables in
the full database.

Then you go back to the main database and remove all the forms and
other rubbish, leaving only the data (alternatively you can leave it
all here but it means maintaining the forms etc. in two places.)

You then do one of two things. You either instruct your users to copy
the front end (with the linked tables) to their own PCs and double
click on that file and NOT the one on the network, OR you create a
function to do this for them.

I would create a new function in the server end database (with the
actual data) that is run on startup, that copies the current server
version of the front end (with the linked tables) to their local C
drive and then closes the main database (unless it is you of course...
you can learn about that later)

You could then think about version control, you now have a database
that is being properly accessed by your users using a recommended and
safe method. You want users to be using the latest version of the front
end, so you could automate a version detection that would give you a
new copy of the front end if you are out of date. This has been done
before and i'm pretty sure that there is some info on
www.mvps.com...probably...

That is all for now as many others should follow on with their
recommendations ...

P.S. if you let more than about 10 people go into a server based
database you will probably kill it very quickly and need a lot of time
and energy to keep recovering it...

P.P.S. If your database is stuck now and there are definitley no users
in it... you need to get the server admin to delete the .ldb file and
then you can try to repair/compact things.

P.P.P.S. you will at some point need to kick users out to maintain the
database... ALL databases have downtime at some point... even if they
are monster server systems with mirroring etc...

Good luck!!!

[Orchid]

Re: Ms. Access database problem

Thanks for you recommendations !!!
I have no problem to create the front-end & back-end databases.
However, I am new on doing this security. How to create the function
to copy the database to their C drive & close the main database ? (is
the main database the Back-end database which with tables only)? Do
you have any examples that I can take a look? For the version control,
I will have 2 databases: 1) DB1 with the tables only for the back-end;
2) DB2 is linked all the tables from back-end database (DB1) and with
all the queries, forms, reports, Macros, etc. I will make changes on
DB2, so I don't need to worry about if the users are using latest
version or not. Is that correct?
I had Network problem when opened this www.mvps.com...probably... Any
other link?

Your help is greatly appreciated!!

[dkintheuk]

Re: Ms. Access database problem

Sorry the link got wierd on me... try...

http://www.mvps.com/access

Your description is good.

The function to copy your latest version of the FE (Front End) database
to their PC is going to be a function in the FE at any time.
The startup function of the FE database should check in the BE (Back
End) database for a value for the current version. If that is the same
version as the current one then all is okay to startup. If the version
is not the same then the FE needs to be shutdown and replaced with a
new version. The problem is in how to run code when you're shutting
down the FE database that contains the code.

I have to admit that i have only viewed other examples of this having
never needed to force this control over users. You should search around
this newsgroup and at mvps for examples but there are several out
there.

It might be better to invest in training your users to copy the FE
themselves.

Good luck getting it sorted...