0

I have a multiuser database (POS/billing system), split into front and backend. Each user have copies of both files on their tablets and a master copy is maintained in the office. Each user sends daily data exports which are imported into the master database, and once a week, a copy of the master database is made available for all users to import to their tablets. The new database file has the current date as prefix. (e.g. 2015-04-10_BE_MYDATABASE.mdb) All of this is done through VBA and it all works fine. However, my problem is that I end up with multiple files on the tablets and sometimes the users connect the fronted to the wrong (older) backend.

What I would like to do is that when a user updates to the database, delete the current backend file they are connected to. I have created the code to save a backup copy of the current database in a different folder, and then connect all my tables to the newly imported database. But, I am not able to delete/kill the current database because of the lock file.

Please help.

Inderpal S
  • 1
  • So I'm guessing the Excel files are connecting to the local copy of the backend database through an Excel connection and not through VBA code using ADO? – Tmdean Apr 14 '15 at 02:31
  • Also it would be helpful to know where the user's pending changes are being written to. Is it to a table in their local copy of the backend database, the Excel file, a separate database? – Tmdean Apr 14 '15 at 02:33
  • Tmdean, the application is in Access 2007. All users have the same setup and files, the only difference being that only the database copy in the office is used to amalgamate all data. – Inderpal S Apr 14 '15 at 02:37

1 Answers1

0

I'm not reproducing the error with a simple reproduction of your situation.

This code will switch Table1 in Frontend.accdb from Backend1.accdb (which is currently linked) to Backend2.accdb. After switching to Backend2.accdb, Backend1.accdb is not locked and can be moved or deleted.

Sub RelinkDb()
    Dim db As DAO.Database
    Dim td As DAO.TableDef

    Set db = CurrentDb
    Set td = db.TableDefs("Table1")
    td.Connect = ";DATABASE=C:\Users\...\Documents\Backend2.accdb"
    td.RefreshLink
End Sub

Try creating a test environment, reduce your databases as much as possible (eliminate queries, tables, and other database objects) while still reproducing the problem. Once you can get the problem occurring in a simple environment, usually the solution becomes obvious.

Tmdean
  • 9,108
  • 43
  • 51
  • 1
    Hello TmDean, I was able to resolve the issue by closing all open forms, which in turn deleted the lock file, and then reopening the login form after deleting the old database file. Thank you for your help. – Inderpal S Apr 14 '15 at 14:00