Using Access to change SQL tables. Table randomly becomes read-only.

Question

I have a database that the client needs to update. They like to you access. Some tables randomly become read-only for them. Any ideas why?

They are using Access 2007 and MS SQL 2005.

SQL Table:

CREATE TABLE [dbo].[Users](
    [SyncGroup] [varchar](20) NULL,
    [UserID] [varchar](20) NOT NULL,
    [Password] [varchar](20)  NOT NULL,
    [Restriction] [text] NULL DEFAULT (' '),
    [SiteCode] [varchar](20) NULL,
    [Group] [varchar](20)  NULL,
    [EmpId] [varchar](20)  NULL,
    [TimeZoneOffset] [int] NULL,
    [UseDaylightSavings] [bit] NULL,
    PRIMARY KEY ([UserID]) )

Answer 1

are users accessing the database while you're trying to do stuff iwth sql? if so, then you will get an error message stating that the database is in use and is read only. no one can be in the database when you are doing things with it though sql.

Answer 2

Access really likes having a TimeStamp aka RowVersion field on every table. I don't know if this will fix your problem though.

"On servers that support them (such as Microsoft SQL Server), timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time the record is updated. If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an attached table, re-attach the table in order to inform Microsoft Access of the new field."

http://technet.microsoft.com/en-us/library/cc917601.aspx

Answer 3

Sounds like a permissions problem. Are you keeping careful track of who is altering the schema? You may have users who aren't permitted to use changes made by certain other users.