Form Dying

[M_Tag]

Hello all - found your BB with a little search on Access DB's looks very useful, and I have book marked.

I have a large DB with complicated table structure and relationships. We are using it at our facility as a mini MRP until corporate can cough up for a more robust implementation (oracle, SAP, what ever)

Recently we have had more users on the DB and it has become unstable requireing a restore at least once per week, where as it was quite stable before. When ever it reaches about 45 Meg in size it requires some maintenance to make it healthy again.

One problem is a series of forms that have subforms that just plain will not open. IT is impossible to view the design either.

I have tried to go back to the queries and tables that drive the forms but they appear to be normal. I have compared to a copy on my desktop that was made prior to the most recent instability and aside from the non operating forms, its not any different.

Because I didn't author the DB, i am at a bit of a loss as to where to look next. I am only an intermediate user.

Does some on have a suggestion.

Many thanks.

 

[ghudson]

A few thoughts...

The db must be split if more than one user is opening the db concurrently.

Each user must have a copy of the front end on their hard drive which is linked to the back end on the server. That will eliminate a lot of your problems.

The back end should be repaired and compacted on a regular schedule and of course backed up. Access 2000 and newer has a feature to automatically compact the db when closed.

Since Access 2000... you can not be in the design view of an object when another user might also have that object open.

 

[M_Tag]

I had been trying to keep it all together as one file. Understanding your suggestion, I should sever at the Table/query level and link to the forms used at each user station?

You are correct, I am in 2000, but the design view was attempted when i was the only user (I work PM shift at the factory, and all the office staff shut down by 4)

A good suggestion but will take some buy in from my colleagues I think.

 

[KenHigg]

ghudson,

'The db must be split if more than one user is opening the db concurrently.'

Shouldn't that be:

'The db should be split if more than one user is opening the db concurrently.'

++++++++++++++

'Each user must have a copy of the front end on their hard drive which is linked to the back end on the server.'

Shouldn't that be:

Each user should have a copy of the front end on their hard drive which is linked to the back end on the server.

ken

 

[ghudson]

Grammatically speaking... yes, I agree that the db should be split.

 

[monkeytunes]

"Should"? No, I say we go with the Stalin approach to database design, and declare MUST absolutes.

M_Tag - You'll find an option under Tools to split the database for you. It will put all the tables in a Backend (which Access will helpfully rename "YourDatabaseName_be.mdb"), linked to your Front End (which keeps the same name).

Now, you upload the Front End on everyone's desktop, and things will go mighty speedy. This also allows you to take your own copy of the Front End, modify forms and queries and whatnot if you need to, and then redistribute it to your users at your leisure (after everyone's left for the day, or early in the morning before they arrive works best).

If you don't split it, and more than one person is using the DB and you try to modify a form, you get a warning that you don't have exclusive access to the database at the moment. Even if all the users are out, sometimes there will be a rogue ".ldb" file (the Access 'locked' file) floating around, usually because a user closed the database incorrectly. The database will be marked as 'locked' until this file is deleted.

 

[M_Tag]

Very Good, thank you all - we will Split the database as suggested.

 

[M_Tag]

Just some follow up. I was not able to use the database splitter because of some reason or another, so I went to the MS knowledge base which had some very good help.

After splitting manually my forms and switchboard did not work. Again, back to the MS knowledge base.

All features are working well - Many thanks, with out the direction of this BB I would likely not have been successful tonight.