Re: Record locking

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Brett Thompson (brettthompson1_at_optushome.com.au)
Date: 06/05/04 

Date: Sun, 6 Jun 2004 00:48:13 +1000

OK. I have fixed the problem, but if someone could explain to me why it
worked I would be greatly appreciative. The solution arrived by serendipity
in that the same thing started happening with another table, and I
remembered what I had done with it. The original backend database used to
be in Access - when I upsized it various fields were added in the process by
the upsizing wizard. I never used these fields, never referred to them. So
I had started removing them, thinking they just were filling the database
with useless data. The one I discovered that was important was of datatype
"timestamp". When it was removed the described problem happened, when it
was replaced the described behaviour stopped and the behaviour of the
records returned back to normal full access.
Any suggestions from anyone? Maybe this is the wrong forum to ask.
Regards,
Brett
"Brett Thompson" <brettthompson1@optushome.com.au> wrote in message
news:%23cAs8XMREHA.556@tk2msftngp13.phx.gbl...
> Hi all.
> I have a puzzling situation that I assume is due to an error in record
> locking. I hope I can explain it with any degree of clarity.
>
> My set up is Win 2K, SQL Server 2000 backend and Access 2000 front end
with
> attached tables. I have a small network at work (though 2 servers at each
> end of a wireless connection each holding copies of the front end), a but
> write on a Win2K laptop at home which has it's own copy of SQL Server on
it.
>
> The problem has occurred in only one table (of many). At some time during
> its life on my laptop something has altered this table. It should be
> read/write. Now, on my laptop, while in Access, it is possible to add a
new
> record, but once added it can't be modified or deleted. The message
> received is that someone else has modified the record at the same time and
> that the changes can't be saved. This is at table level as well as in
forms
> based on this table directly or via queries. In SQL Server it is possible
> to add, modify and delete on this table both on my laptop and on my work
> server. Its permissions are the same as all other tables in the database.
> The SQL database on my laptop is a restored up to date backup of my work
> database- so it is consequently always changing.
>
> I had done some work on my front end, and installed it on one of the
servers
> thinking that the problem I have just described was a problem peculiar to
> the laptop setup, not to the Access database. Prior to this the
> aforementioned table at work was behaving normally (ie full read/write
> access). After installation of the new front end (which is located on the
> server, not on the workstations) it started to behave as it does on my
> laptop. So I went back to using the older version of the front end and
now
> the problem occurred there as well. I created a new Access database on
the
> same machine and attached the table in it, and the same problem existed.
>
> From a computer that was pointed at the other server for its front-end
> database I then opened the old version of the front end that was on it,
> without installing the new version. On this computer the table behaved
> normally, allowing full editing capability. Remember, it is linked to the
> same SQL Server database as the first computer.
>
> This behaviour puzzles me, and is also quite destructive of my systems
> capabilities as the table can no longer be modified on half my computers.
I
> assume it is a locking issue, but I have no idea where I can find the
> locking settings for an individual table. The general setting is "No
locks"
> and this has been and remains the case in all other tables.
>
> Has anyone any suggestions? Is there some info I have missed that might
> clarify something?
>
> Many thanks in advance.
> Brett
>
>
>

Relevant Pages