fields.append method (vba) when Table is linked and in use

[Rx_]

fields.append method - can code add fields while table records are linked or in use by users?

Will this code add a field while the Table is in use (bound) by the users?
The database has a Customers table - Every user has a link to it when the database is open. As a front-end/ back end split database, there are a half dozen users constantlly linked and engaging this table.

Try and open the table in Design Mode - produces a warning:
"Either an object bound to table ... " Yes for Read Only
Can't use the Access interface tool to open in Design Mode for a read-only view.

There are half a dozen users connected to this table. Will this code allow me to modify the table and add a new field while the table is in use?
There does not appear to be any error trapping code for the examples out there.

Function ModifyTableDAO()
    'Purpose:   How to add  fields to existing tables.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field    
    'Initialize
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblDaoContractor") ' table already exist   
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
    Debug.Print "Field added."    
    'Destroy object variables
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

 

[Rx_]

Well, answerd my own question at least for Access 2010
Created a module in the shared back-end database while it was in use by others.
The code would run, but the code could not be saved because the databae was not opened exclusivelly.
The code failed at tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
with an error. The message was along the line of "the table was in use by others.... can't add fields, try later"

Since this table is a primary foundation for the database, it is unlikely that it will be available in a multi-user environment.

None of the MS Access examples had error trapping code or any mention about this. It makes sence that fields can't be appended during record locking. But, finding documentation about this was difficult.

 

[gemma-the-husky]

you cannot modify a table that is in use

you can modify a not-in-use table, in a databaser that is in use.

note that you cannot modify the tabledef of a "linked" table, in the database you are using, although you can open the backend directly, and change it in there.

 

[Pat Hartman]

It is very poor practice to attempt to update a database that is in use by others even if sometimes you can get away with it. If you must add columns to a table or make other back end changes during normal working hours, you need to create a procedure that requests all users to exit the application for some amount of time. You then would BACK UP the database, make the changes. TEST the changes. And finally notify the users that the application is available again. Front end changes are handled differently. You make the change to your own copy of the FE. After it is tested, you would replace the distribution copy of the FE on the network share. You would then notify the users that a new copy is available and they would shut down the app and when they reopen it, a fresh copy from the distribution folder would be copied to their PC. You can use a batch file that always copies a new version of the FE whenever it runs or you can write your own code that checks versions and does the copy for you. Or you can purchase Tony Toews auto-updater. There may be other products available.