Two users using same table PROBLEM

[Alireza355]

Dear friends,

I have a big database, that I have splitted into two different versions (front office and back office).

One is used by sales department users, to input some data and see some reports, some edits and so on... , and the other one (main one) is used by the accounting department, to do all the rest of more important actions...

In one of my forms, I have used some VBA code, to rename a table by clicking a button.

The problem is that when a front office user is somehow "connected" to that specific table, the back-office users encounter difficulties renaming tables...

How can I know if another user is somehow "connected" to a specific table in the main database, and issue a warning to the back-office user before clicking that button?

Thanx a loooooooooooooo oot......

[ADezii]

1. Via a Public Function accepting two Arguments, try to Rename your Table to itself, Trapping any Errors which may occur.
   1. If Error 3211 occurs, the Table is in use by another process: perhaps it is the Record Source for an Open Form, or an Open Form is based on a Query using this Table, etc. Notify the User of this and make no further attempt to Rename the Table.
   2. Should any other Error occur, display a Warning Dialog to the User indicating the nature of the Error, and again make no further attempt to Rename the Table.
2. If no Error occurs when you Rename the Table to itself, allow the code to fall through and Rename the Table to the New Name as passed to the Function.
3. Function Definition:
   

   Public Function fRenameTable(strOldTableName As String, strNewTableName As String)
   On Error GoTo Err_fRenameTable
   
   'Try to Rename the table to itself, if Error 3211 is generated it is in
   'use by another Process
   DoCmd.Rename strOldTableName, acTable, strOldTableName
   
   'If you get here, you should be able to successfully Rename the Table
   DoCmd.Rename strNewTableName, acTable, strOldTableName
   
   Exit_fRenameTable:
     Exit Function
   
   Err_fRenameTable:
     If Err.Number = 3211 Then     'in use by another Process
       MsgBox strOldTableName & " is in use by another process and cannot be Renamed", _
              vbExclamation, "Error in Renaming Table"
     Else      'Table does not exist, etc.
       MsgBox Err.Description, vbExclamation, "Error in Renaming Table"
     End If
       Resume Exit_fRenameTable
   End Function

4. Sample Call to Function:
   

   Call fRenameTable("Employees", "New Employees Table")

[Alireza355]

interesting...

Dear ADezii,

Thank you so much for your kind help.

I will give it a try, and let you know shortly.

Thanx a lot.

[Alireza355]

Thanx a lot

Yesssssssssssss ssssssssssssss

It worksssssssssss sssssssssssssss ss

Thank you so much...