Can I edit a SQL table structure while people are using the database?

I have an Access front end and a SQL Express back end.  I sometimes need to add new fields to the tables.  I do this in the evening when there is nobody in the database.  Is it possible to add the new fields while people are using the database?  I know I will have to relink the tables for the changes to be seen but I don't want the users to get any errors once I have made the table changes but not refreshed the links yet.
egalawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
I generally don't make structural changes to the tables while users are connected. An ALTER TABLE statement creates a table lock during the duration of the operation. Depending on the size of the table it could take a little time. Read the REMARKS section on this MSDN page:

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

"ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end."
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdebaetsCommented:
On a side note, you can use our free Force Shut Down utility to shut down all copies of your Access application so that back-end or SQL schema changes can be made. then you won't have to wait until evening to do this. More info is at http://peterssoftware.com/fsd.htm
0
Jerry MillerCommented:
That's true, if you have a way to get your users out that is a great alternative. I have one web app where I can send a message and get them all out, but the other one is older and I have to wait until off hours for maintenance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.