I've got 2 issues I cannot seem to find an answer to anywhere on the web. Hoping someone here knows a bit more than I do about this.
1. I am running Windows 7 and MS Office 2010. When opening a .accdb file Windows 7 does not give me the security error of untrusted publisher. However when opening any of our old .mdb databases I get the security error. Yes I know the best is just to upgrade them all to .accdb formats. Right now we have hundreds and I just don't have the time for that yet. So I was wondering is there a setting somewhere that will allow me to trust those? I've already set my network locations as trusted within MS Access, but I'm not sure how to trust a publisher that should already be trusted.
2. That brings me to the next issue. If the new file format is .accdb, why does MS Access when doing a compact and repair use the .mdb format? We keep having problems because our IT will set someone up on a folder without write or delete permissions. When the database tries to compact sometimes it will delete the .accdb file, but then does not convert the .mdb back to the .accdb. The compacted database stays named in the folder as Database.mdb. I then have to go back in and fix the database back so it has the correct name and file extension. Why does this happen, and is it actually storing it as an old .mdb file even though it is an accdb database? Do I need to re-convert that file again back to an accdb and rename it, or can I just rename that one to the original name with the accdb extension?
1) I would not trust all .mdb files but you should be able to add trusted publishers to your A2010 when you get the popup that a pupblishers signature is available. Other wise you have to Enable All Macros in trust center (highly recommend against that). This is a per user machine/login so changing it on one does not change it on another. Make sure you do not have a checkmark "Disable All Trust Location..."
2) When Access is compacting your database it uses a temporary .mdb file to store certain layout information and the data within the tables. This is what pops up temporarily and deletes itself when all is functioning properly. It is very important to realize two things with your problem; A) Your IT department has an issue setting peoples accounts correctly and you need to check with them before letting your users do anything to create this problem. B) there is a misuse of Compact going on here. Really look at why you are having to do this. If a db is built correctly it is very rare to need to Compact. If you are refering to the FE (Front End), then really a Compact should only need to be done during manufacturing or changing of objects. If you are creating temp tables in the FE a better way to go would be to create a temp db and destroy it when complete. If you are talking about compacting the BE (Back End), then why are you getting so much boat? are you entering a lot of data and then deleting it? Here again a temp db can save you all that trouble by only entering real data you want held in the bucket.
Make sure you keep a backup to restore from. I think there are some modeling issues you have.
Thank you for the reply. I appreciate your answer to my questions. I have some follow up to ask.
To question one. This only happens when the databases are on the network server not if I pull a copy to my desktop. It is not an Access issue blocking macros or code (I think). The message states Open File - Security Warning. It appears to be the OS not liking the file because it has an unknown publisher. I don't understand why it would be different based on it being on the network vs the desktop. You can get this warning message and still open the database and get another if you have not set it as a trusted document. Most of them are really queries and are unpublished so I would like to know how I can make them trusted without needing to publish or digitally sign them. While I still want to keep anything outside the network untrusted. I also should mention that if I take the same mdb that has a problem and convert it to an accdb then put it back on the network I don't get the error anymore. Why is the mdb different from accdb in that regard?
To question two I know this is poor design causing the problems, but I have no control over everyone else and how they design. I simply am tasked with keeping other peoples messy design working. So what I really needed to know is if I simply take the database.mdb (that was created) and rename it back to the OriginalName.accdb what problems can that cause. Should I just always restore a backup copy instead of renaming what was created by the compact and repair? We've renamed them in the past and they usually work just fine.
- Edited by Ben AIS Friday, March 22, 2013 9:08 PM