Update the backend while it is open

[Djblois]

I am in the process of creating a database that I broke into a front end and a back end. Currently, I know that I can update the UI or the Front end per se and have my users exit the Database and then update the UI. I told my boss that I can do that but if I ever make changes to the database structure then I would have to work nights or weekends to correct it. However, he told me there should be a way to update it while it is open or update the structure and then save it on top of the other one.

I know updating it while it is open will cause problems but is there a way to save just a new structure without editing the underlying data? I don't think it is possible but my boss says you can?

 

[boblarson]

You really can't update anything (although you can perhaps change tables that aren't being used by anyone at the time) in the backend while someone has the frontend open. If you need to make changes to tables that are normally used, then you will need to have everyone out of it when you make your changes.

So, essentially, your boss is technically incorrect.

 

[gemma-the-husky]

if its just tweaking a key, or adding fields, try and get a brief time when the users arent in

you can massage the data while its in use though

 

[ACMAIN_CHM]

if you want to alter the table structure, you must lock the table while there is no other user is accessing it. so the job has to be done at either mid-night or weekend.

but you also can schedule a job which execute a script to alter the table structure. for example, you can schedule a VBA scripts with ADO to run some DDL at mid-night, of course, you need first test it on the test environment