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.

 

[jasmith4]

You have to open the company database exclusively. Close the database, but not Access, and do File/Open. On the dialog box you'll see an arrow on the right side of the Open button. Navigate to your database, click it just once to select it, then click that arrow and choose Open Exclusive.

 

[DAyotte]

flippin' sweet ray:

 

[boblarson]

That is a good reason why you use a split database whereas the backend has the tables and the frontend has everything else and a COPY of the frontend is on EACH USER'S MACHINE. Then you work on a master copy of the frontend and then deploy it when it is ready and you don't try inserting things into a used file. And if you don't want to deploy to a bunch of users you can use an auto updater like the free one that is available at my website. See my signature.

 

[DAyotte]

sounds complicated. lol.

I was just kind of thrown into all of this, with minor access knowledge to start. I'm learning as much as I can and in the process, fixing and updating the tools as necessary to try and get this department current with technology, and simplify all of our processes. While that idea sounds fantastic, I wouldn't even know where to begin doing something like that with multiple databases that are already in use, and used by scores of agents and analysts every day... (yet.)

 

[jasmith4]

Basically you'd make a database full of nothing but tables and their data -- that's the back end, and you'd put it up on a network so the path/file name is valid for everyone. Then you'd make a local database with no tables except for links to the first one's tables using the valid path/file name -- that's the front end, which you'd copy to all your users to install locally.

It's very efficient, because the only reason to get to the network database is to get the data -- all the temp files it creates as you process queries, open forms, print reports, etc., are local.

 

[DAyotte]

So what everyone would be using is just a database comprised of linked tables? How does this not cause the same issue with updating, etc.?

 

[jasmith4]

Your issue, I thought, was copying a form from one MDB to another -- you said that copying the form's source-data table went OK. If I'm correct, then you wouldn't need to copy forms, reports, or anything else between MDBs. You just make improvements to your copy of the local database (the one with the table links), e.g, by making changes to some form, and when it's ready for the users, just copy the whole MDB file to all of them, overwriting the MDB that each one has. Since there are no data in it, you're safe. You can even turn on the read-only attribute before copying it out to be sure the users don't make changes they shouldn't.

 

[boblarson]

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.

 

[DAyotte]

There are a bunch of reports and what-have you that are run with data stored in the original database. It's updated with information on a weekly basis depending what store needs sales adjustments, what store is getting audited, etc.

My table is pretty much an all inclusive, designed for a form made to give our agents quick access to pull up any store information they could need for resolving their issues.

I created my table and form separate, because my supervisor didn't want me editing the main database while it was being worked out of - totally understandable. Now that my form is complete, and I understand how to import my information, it should be great.

I do understand the benefits of that back/front end system, but I think that my situation is a bit different than what you're describing. They would need more than just my small database on their end to do the work required on a daily basis. Or are we suggesting that I link all the tables, and forms, etc to this database, and roll that out to the users?