How to update tables in the backend of a linked database (1 Viewer)

Jason1

Registered User.
Local time
Today, 06:41
Joined
Mar 12, 2010
Messages
63
Hello,
My database has been split, and the BE is on a server. I have about 15 user terminals using the database front end through out our facility. I need to add a column to one of the tables in the BE, but it It keeps telling me it is read only because the database is in use. Is there any way to make these changes without making sure all of the terminals have microsoft access shut down?

Thanks,
Jason
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:41
Joined
Oct 29, 2018
Messages
21,358
Hi Jason. If you can't lock the table to make design changes, you'll have to either wait until all users are out of it or you need to kick them out.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 19, 2002
Messages
42,981
NEVER, EVER attempt to do maintenance on a BE that is in use by other people. Always do these changes during off-peak times OR have a procedure where you can notify people that you need to do maintenance and you'll need a way to kick them out of the database so you can do your think. Once the BE is quiet, I rename it so that anyone trying to open the app gets an error. I back up the BE, make the change, compact the db. rename it back to the original name and create another backup. Then I send an email to everyone that I'm done.
 

Jason1

Registered User.
Local time
Today, 06:41
Joined
Mar 12, 2010
Messages
63
okay, so no short cuts haha. How do you go about kicking a user out of the database. I haven't done that in the past.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 19, 2002
Messages
42,981
You really need to avoid making these changes during normal work hours. Having to kick out the users should be for emergencies only, not for your convenience because you don't want to stay late or come in early or work on the weekend. Get your employer to buy you software that allows you to connect from home to your work PC so that you don't actually have to drive to the office.

Your testing should always be done with test copies of BOTH the FE and the BE so not being able to make a change immediately should not impact your development schedule.
 

Jason1

Registered User.
Local time
Today, 06:41
Joined
Mar 12, 2010
Messages
63
Pat,

I plan on making the changes to database after hours, but I also know that asking everyone to log out of the database before they leave will result in about a 60% success rate, and I'm going to need to be able to get them logged out when they are not here.
 

Isaac

Lifelong Learner
Local time
Today, 03:41
Joined
Mar 14, 2017
Messages
8,738
@Jason1 ... This is in that category of things where I usually strongly recommend that people come up with an approach to solve, BEFORE splitting the database and deploying it as such. (Of course I understand there's nothing you can do about that now, just mentioning for emphasis).

1 - Have an auto-versioning/auto-distribution system completely intact (so users automatically get a copy of the FE every time you update your master/server copy)
2 - Have a way to kick them out of the back end database.

Of course, you'll have some established process for gently nudging them out, whether that's an advertised downtime or manager communication or a database-driven message, etc. But none of that is substitute for having a way to force them out....it's gotta be in place, because some day you'll probably have to.
For that "brute-force" kick-out, in many cases I've used something extremely simple, such as a text file on the network somewhere with a 0 or a 1 in it, along with a form timer that checks for that.
On some projects, I've never had to use it. On other projects, agreed-upon maintenance hours were, say, after 5 PM--and there were users who would go home every day and literally never close their databases, so they got kicked out every time. "Don't deploy without it".
 

Users who are viewing this thread

Top Bottom