Posted by Lawrence
Due to the fact that Access is a "file" based database, the file can be locked. Here's how to unlock it.
Due to the fact that Access is a "file" based database, the file can be locked and not allow you to load it or overwrite it. One way to over come this is to "break" the server connection with the file. You can do this by calling a bogus query that will throw an error. When the error is thrown, CF releases the file. To do this, create a CF file and name it "unlockdbcon.cfm" put the following code in the file..
<cfquery name="unlock"
datasource="YourDSNHere">
SELECT bogus
FROM bogustable
</cfquery>
Now upload this file to your server and then access the file in your browser. The problem is you have to be fast; If some one else opens a document that has a valid query in it, the lock will be reestablished.
I found the fastest way to do that is
1. Make a copy of the Access database file you wish to upload.
2. Rename it by adding a "tmp" to the name or something like that
3. Upload it to the same folder as your current access database file
4. Have your FTP client or DWMX open and ready to go
5. Now open your "BreakConnection" file in your browser
6. Immediately go to your FTP and delete the current file
7. Rename the new one to the exact name of the old one
There! You're back up and running.
If the database lock is occurring on your local test system do the following..
1. Open CF Administrator and go to "Data Sources"
2. Select the DSN for the Access database you wish to unlock and click on the edit icon
3. Click on "Show Advanced Settings"
4. Find and Uncheck "Limit Connections" and "Maintain Connections"
5. Click "Submit" to activate the new settings
Doing this will prevent the Access Database file from being locked from now on. If your lock is occurring on a remote server, you can also contact your host and ask them to change the settings on your DSN to stop the locking as well. If they won't, or are slow to respond, the "Break Connections" file is a good alternative.
