Importing Issues

[DAyotte]

I'm getting a message when trying to import a form from my database to the company database. The table that it pulls from imported just fine, yet the form appears to lock every time I try.

The error message is: "Microsoft Office Access cannot save design changes or save a new database object because it is open by another user. To save this new object, you must have exclusive access to the file."

Mind you... I just saved it. It's closed on my computer, and no one else could possibly have it open. Just to be sure, I saved a copy of the database on my desktop, renamed it, and tried again to no avail.

 

[DAyotte]

ps... there is a comments section on the form, which is pretty much the only text box that isn't locked. Incase of a closing, or a special circumstance... the agent may need or input specific directions to that store. So I wouldn't want a read-only table.

 

[DAyotte]

Also, a split database will protect against corruption (which Access is good at when used in a multiuser environment) because if anyone is connected to the database and a network disruption occurs, no matter how slight, the ENTIRE database file could be rendered corrupt and irrecoverable. How would you like to lose all of your data and have to restore a backup and then enter in everything since that backup? Or if you have no backup (which you should always have) then you are really toast. Not to mention that if the entire thing goes down then EVERYONE can't work in it.

Whereas if you use a split database with a separate copy of the frontend and a user is using it and they suffer a network disruption (no matter how slight) then if anything gets corrupted it usually would be THEIR frontend. But everyone else can simply keep working. You just give the affected user a new copy of the frontend and then they are back in business. Sounds a lot better than the first scenario, don't you think?

So, a multiuser Access database shouild ALWAYS be split and users should not be running the frontend from the same location/file. They need a separate copy. In fact, I split ALL of my databases that store data even if they are single user. It is much easier to work on and replace the frontend than to have to try to make sure all data is transferred properly and also again it is protection from corruption.

That makes sense, but if one user is in charge of pulling the new information into the database by say... 9am monday morning so 3 other agents can work it... if they are all using their own copy, won't this make it impossible for the updates to be seen on everyone else's copies? or am I just that confused?

 

[jasmith4]

ALL the data -- and ONLY the data -- are in the big network database that everyone can see. No user's copy of the little local database would have any data at all. Everything in the local database would be based on table links to the big one's tables -- queries, forms, reports, everything.

 

[DAyotte]

excuse my...er...ignorance(?)

but how does this prevent anyone from screwing up the data files or database then?

 

[jasmith4]

Your risk of that is greatly minimized, but not altogether prevented. The only thing happening with the big database is data transfer -- all the other processing associated with the UI -- queries, forms, reports, etc. -- is local.

 

[DAyotte]

But then say that Agent Joe makes a query to perform some crazy task or whatever (super-specific, I know), that's then saved on his side... and Agent Mary, or his supervisor both won't have access (no pun intended) to it, right?

I'm sorry I'm being such a pain, I just want to make sure that I have a complete understanding.

 

[jasmith4]

How often do salespeople make their own queries? You need some control over that, and with all these local copies, you have it. Have Joe Email you the SQL for his new query, then you can decide to create a query in your copy and ship it out.

Do they make forms or reports too? If so, you're back to copying objects, but this time you're copying from a salesperson's local database to your local database, still not touching the big one with all data and only data.

 

[boblarson]

But then say that Agent Joe makes a query to perform some crazy task or whatever (super-specific, I know), that's then saved on his side... and Agent Mary, or his supervisor both won't have access (no pun intended) to it, right?

I'm sorry I'm being such a pain, I just want to make sure that I have a complete understanding.

Yes, that is true. And I think that the main problem with the conception here is that those of use who have been doing this a long time do NOT let users create objects in the database. If they want to create their own objects then they would need to do so in their own "side" database.

But regardless of whether the database is a single file or split into frontend/backend, the users would still have the same potential for screwing up data if you let them have carte blanche access to access objects (queries, tables, etc.).

 

[DAyotte]

that's true... I'm pretty sure the queries that they use are already on the db anyway. I can talk to my sup. about it.

 

[jasmith4]

Hence my suggestion re the read-only attribute! The potential for error grows exponentially.