I was trying to copy the backend MDB from the front end but hadn’t closed a database variable pointing to the backend MDB. I used the presence and absence of the backend LDB file in Windows Explorer to track down the problem.
The client wanted to email PDF copies of daily field tickets in his pickup truck while on site to his clients using the cell phone system data network. Given that his laptop would be out in the field and subject to hazards including theft I decided to also give him the ability to email a zipped copy of the backend MDB to the office. As this is a single user app I wasn’t concerned about other folks also access the backend MDB. So my code closed all open forms and copied the backend database to the temp folder while giving it a name that ended in the yyyy-mm-dd format.
After I installed some updates on their systems it starting giving them the permission denied message. Well, they were running on Windows Vista and I was on Windows XP. So, despite it having previously worked on their system, I decided that it was a weird Windows Vista problem and said I’d figure it out later. I did not test it myself on my system at that time. Cue ominous music.
Later, just for grins and to avoid the time spent in copying the 9 Gb Virtual PC hard disk file to make a test install, I decided to test it myself. Oops, I got the same error. Ah hah, not Windows Vista. Cue dumb *ss assumptions music with Goofy hyuk hyuk laughter.
Turns out I had added some code to update the backend table, field, indexes and relationships when I gave them a new version. So I opened a database object against the backend MDB to do these updates but I forgot to close the database object.
Now I always use a GlobalOptionsHidden form to store those Global Options variables and reference through out my application. These are things such as Goods and Service Taxes percentages, account numbers, default settings, email address, email server name and so forth. But when I started the app, got to the main menu and I closed the GlobalOptionsHidden form I saw that the LDB lock file was still present for the backend MDB in Windows Explorer.
As the main menu did not have a record source or combo box referencing tables I knew it couldn’t be the problem. Although I could’ve double checked this by closing that form and looking to see if the backend MDB’s LDB file was still present.
It took a bit of debugging to figure out where the problem was. But I could track it by running Windows Explorer looking for when the LDB file was created in the code which started when the MDB was opened. Once I saw the OpenDatabase I went looking for the close database in the same subroutine and discovered I had forgotten it.
I emailed the client the new copy of the front end MDB and phoned them to tell them I was a dumb *ss. They just chuckled and were happy that it was working.
They figure this app will save the company owner about an hour a day in hand writing. Figuring his time is worth $100 an hour his system will pay for itself in less than two months or so. Including the cost of the laptop.
Here’s an interesting and much more obscure variation of that error – Debugging Access “Permission Denied” Errors