1

As the above states really; I have a .accdb database that is literally just the BE dataset, that users can link into via Excel, and refresh the data via VBA.

Recently, the BE password was potentially compromised, so we needed to change this. We have over 100 users who daily access the Excel file, which refreshes the data. Even when the day is finished, some users are locked to the file where the link doesn't break, or they stay logged in overnight etc. Unfortunately now, I can't decrypt the BE because it won't allow me to open the database exclusively as it's locked by (whatever machine is linked in first).

Is this a limitation of Access & Excel, that there is no "live" link to the database without there being a lock put on it? My string does contain:

Mode=Read;

But this unfortunately, still locks the file. I am happy to look into changing the connection process for the database, if there's a more efficient way to do so.

eisbehr
  • 12,243
  • 7
  • 38
  • 63
RazorKillBen
  • 561
  • 2
  • 20

1 Answers1

1

This indeed is a limitation of Access, though it's a usual limitation.

Exclusive access to a database denies people reading it, so it's expected that when people are reading from it, it can't be locked.

You can make a copy from the database without an exclusive lock using the technique I described in this answer. Then you can adapt the front-ends, which need to be changed anyway to use the new password.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks @Erik von Asmuth - do you mean create a copy and link the users Excel file to the BE copy? Will this be "live" data if I add a record to the original, or would the VBA copy the latest data each time? Or, do you setup a link between the two databases by doing so? – RazorKillBen Aug 01 '18 at 08:25
  • Obviously, you can't re-encrypt a database without breaking the live connection. Ideally, you would disable access to the original as soon as you've made the copy, to force users to use the new copy with the new password. If you still leave the original with the old password around and use VBA to copy changes, you've fixed nothing by creating a copy since the data is still available using the old password. – Erik A Aug 01 '18 at 08:28
  • Apologies, I mis-understood the technique - I realise you're referring to re-pointing the database to change the password, which will work perfectly. Really appreciate the help, thanks! – RazorKillBen Aug 01 '18 at 08:32