By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

Two users using same table PROBLEM

P: 86
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 loooooooooooooooot......
Mar 29 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,702
  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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRenameTable(strOldTableName As String, strNewTableName As String)
    2. On Error GoTo Err_fRenameTable
    4. 'Try to Rename the table to itself, if Error 3211 is generated it is in
    5. 'use by another Process
    6. DoCmd.Rename strOldTableName, acTable, strOldTableName
    8. 'If you get here, you should be able to successfully Rename the Table
    9. DoCmd.Rename strNewTableName, acTable, strOldTableName
    11. Exit_fRenameTable:
    12.   Exit Function
    14. Err_fRenameTable:
    15.   If Err.Number = 3211 Then     'in use by another Process
    16.     MsgBox strOldTableName & " is in use by another process and cannot be Renamed", _
    17.            vbExclamation, "Error in Renaming Table"
    18.   Else      'Table does not exist, etc.
    19.     MsgBox Err.Description, vbExclamation, "Error in Renaming Table"
    20.   End If
    21.     Resume Exit_fRenameTable
    22. End Function
  4. Sample Call to Function:
    Expand|Select|Wrap|Line Numbers
    1. Call fRenameTable("Employees", "New Employees Table")
Mar 29 '10 #2

P: 86
Dear ADezii,

Thank you so much for your kind help.

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

Thanx a lot.
Mar 30 '10 #3

P: 86

It workssssssssssssssssssssssssssss

Thank you so much...
Mar 31 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.