Read Only Table

[Jgr4ng3]

Hello

I have a database with its backend on a network location, and every different user has a front end copied to their desktop. Whenever any user is in the front end, a table (AgentList) is read-only. Its quite a pain as I often want to make changes to the table structure and users are in the FE from 8am-10pm (our entire working shifts) as every employee uses it.

Does anyone know what is causing this? I read somewhere an object might have the table open, and to have the recordsource as a query rather than the table, but I cant find anything that has the table as its recordsource that would generally be open.

If thats the only diagnosis, I will delve deeper, but I feel this may not be the case with my db..

Your help would be appreciated!

Cheers.

 

[spikepl]

Read only DATA, so you are unable to update the table? Or unable to make structural changes? When you do not have the DB exclusively to yourself, then yes, you cannot change the structure of objects such as tables.

If the db being opened all day prevents you from applying frequent structural changes then that is not necessarily a bad thing. Frequent changes imply lack of change management, and that, sooner or later, tends to bite both users and developers in the derrière

 

[Jgr4ng3]

Hello

Thank you for your reply - data changes are possible, structural changes are not. Maybe I was exagerating with the word 'frequent' its just whenever I do want to, I cant!

Is there no way around this

 

[Cronk]

You don't need to open the database exclusively but you cannot make changes to a table structure while any use has the table open or any other object such as a form bound to that table, open.

I've used three methods in your situation.
1. Get to work outside of normal working hours eg weekend
2. Email users to ask them to close out, or add functionality to the the FE where you can set a flag in the BE that closes out users when they try to open the database (and get to work as early as you can).
3. Use Data Definition Language in the FE so that the first person opening the database the next day, causes the changes - hopefully all users shut down overnight. For DDL, search for "ALTER TABLE"

 

[Jgr4ng3]

You don't need to open the database exclusively but you cannot make changes to a table structure while any use has the table open or any other object such as a form bound to that table, open.

I've used three methods in your situation.
1. Get to work outside of normal working hours eg weekend
2. Email users to ask them to close out, or add functionality to the the FE where you can set a flag in the BE that closes out users when they try to open the database (and get to work as early as you can).
3. Use Data Definition Language in the FE so that the first person opening the database the next day, causes the changes - hopefully all users shut down overnight. For DDL, search for "ALTER TABLE"

Hi Cronk,

Thank you - I have checked all of my forms and none of them have the table bound to them, only queries which run off of that table - would that be the cause? I was under the impression that if it was via a query I would be okay.

I am confident in the ALTER TABLE function, however how would one set this to run when the next person opens it? An autoexec macro could obviously do this, but would then do it for every subsequent user logging in also.

Sorry if I'm being dim, I'm not an expert...

 

[Cronk]

Firstly, a table structure can't be open if someone else has the table open, or a query based on the table is open or a form.

When the db opens, it will run a macro AutoExec if it exists. My systems have a one line macro that executes RunCode | fnAutoexec. This is where I put any required initialisation.

Obviously a table can't have two fields with the same name. So after the first user opens the db and the change is made, this will fail for second and subsequent openings by that or any other user.

When you've tested your ALTER statement(s) is working, add the line
On Error Resume Next

So an example would be

Dim db As Database
On Error Resume Next
Set db = CurrentDb
db.Execute "ALTER Table MyTable ADD COLUMN MyField TEXT (10)"
   
Set db = Nothing

 

[Jgr4ng3]

Firstly, a table structure can't be open if someone else has the table open, or a query based on the table is open or a form.

When the db opens, it will run a macro AutoExec if it exists. My systems have a one line macro that executes RunCode | fnAutoexec. This is where I put any required initialisation.

Obviously a table can't have two fields with the same name. So after the first user opens the db and the change is made, this will fail for second and subsequent openings by that or any other user.

When you've tested your ALTER statement(s) is working, add the line
On Error Resume Next

So an example would be

Dim db As Database
On Error Resume Next
Set db = CurrentDb
db.Execute "ALTER Table MyTable ADD COLUMN MyField TEXT (10)"
 
Set db = Nothing

Hi Cronk

At the risk of sounding dim - my FE & BE are seperate so the query will not run on the linked table. I used an auto exec to run the query to alter table.

Cheers.

 

[Cronk]

No, it's me overlooking a split database system.

So instead of opening db as Currentdb, we need to open db as the backend. You can look up help or Google the Opendatabase method and instead of

set db = currentdb

use

set db = dbengine(0).opendatabase("C\dbFolder\YourBE.accdb", true)