Access - SQL Server Locking Problem

[Andy]

Hi guys - Hope you can help me out with what is problem a simple fix to an
important application at work.

I have a small application in Access 2003 with linked tables on a network
SQL Server 2000 instance (around 3000 rows type size)

All has been fine for several months until today when, stupidly, I was
adding a couple of fields to one table in SS Enterprise Manager (Design table
GUI rather than T SQL) and didn't realise that one User, actually in the same
room as me, was using the application at the same time in edit mode.

We both got locked out and had to abandon jobs (Access and SQL Server) - my
colleague even had to reboot her lap top to recover.

Now, if I try to edit a record in the Access application, I get the pop up
message (every time) about the "data having been changed by another User - do
you want to copy to Clipboard or Undo changes" etc.

I tried re-creating the offending table (with the same name) and inserting
the data from old into new and re-linking but that gave same error even with
the new table.

I guess that there is some kind of lock in one of the System tables that I
need to clear but need some help where to look. I can't reboot the Server or
start and stop SQL Server because other databases (not belonging to me)
co-exist.

Any help/ideas much appreciated - sorry that it is so late.

Kind Regards,
Andy.

[Sylvain Lafontaine]

I don't think that you have a locking problem here. By closing the
connection, any lock would have been automatically removed by SQL-Server.

Did you try to refresh the ODBC Link or better, remove it and then recreate
it? Each time you change the design of a backend table, you should
recreate - or at least refresh - the ODBC Linked Table.

Seeing this message is also normal under some conditions; for example if you
are using some incompatible types for the new columns; for example a
Nullable Bit field. Probably that by removing the extra field, you'll go
back to the previous situation.

You should take a look with the SQL-Server Profiler to see what's going here
exactly between Access and SQL-Server.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Andy
Hi guys - Hope you can help me out with what is problem a simple fix to an
important application at work.
I have a small application in Access 2003 with linked tables on a network
SQL Server 2000 instance (around 3000 rows type size)
All has been fine for several months until today when, stupidly, I was
adding a couple of fields to one table in SS Enterprise Manager (Design table
GUI rather than T SQL) and didn't realise that one User, actually in the same
room as me, was using the application at the same time in edit mode.
We both got locked out and had to abandon jobs (Access and SQL Server) - my
colleague even had to reboot her lap top to recover.
Now, if I try to edit a record in the Access application, I get the pop up
message (every time) about the "data having been changed by another User - do
you want to copy to Clipboard or Undo changes" etc.
I tried re-creating the offending table (with the same name) and inserting
the data from old into new and re-linking but that gave same error even with
the new table.
I guess that there is some kind of lock in one of the System tables that I
need to clear but need some help where to look. I can't reboot the Server or
start and stop SQL Server because other databases (not belonging to me)
co-exist.
Any help/ideas much appreciated - sorry that it is so late.
Kind Regards,
Andy.

[Andy]

Thank you Sylvain - I think you are on the right track here because I also
read last night that if you add a binary single bit field to an existing
table of data (which is what I did by the way) and don't initialise the data
to zero (i.e. leave it as Null) it can cause this type of error.

I will check at work today and report back if this fixed my problem.

I haven't used the the SQL-Server Profiler. I'll try and find some
information about how I can use this going forward unless you have any
suggested reading.

Andy.

Post by Sylvain Lafontaine
I don't think that you have a locking problem here. By closing the
connection, any lock would have been automatically removed by SQL-Server.
Did you try to refresh the ODBC Link or better, remove it and then recreate
it? Each time you change the design of a backend table, you should
recreate - or at least refresh - the ODBC Linked Table.
Seeing this message is also normal under some conditions; for example if you
are using some incompatible types for the new columns; for example a
Nullable Bit field. Probably that by removing the extra field, you'll go
back to the previous situation.
You should take a look with the SQL-Server Profiler to see what's going here
exactly between Access and SQL-Server.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Andy
Hi guys - Hope you can help me out with what is problem a simple fix to an
important application at work.
I have a small application in Access 2003 with linked tables on a network
SQL Server 2000 instance (around 3000 rows type size)
All has been fine for several months until today when, stupidly, I was
adding a couple of fields to one table in SS Enterprise Manager (Design table
GUI rather than T SQL) and didn't realise that one User, actually in the same
room as me, was using the application at the same time in edit mode.
We both got locked out and had to abandon jobs (Access and SQL Server) - my
colleague even had to reboot her lap top to recover.
Now, if I try to edit a record in the Access application, I get the pop up
message (every time) about the "data having been changed by another User - do
you want to copy to Clipboard or Undo changes" etc.
I tried re-creating the offending table (with the same name) and inserting
the data from old into new and re-linking but that gave same error even with
the new table.
I guess that there is some kind of lock in one of the System tables that I
need to clear but need some help where to look. I can't reboot the Server or
start and stop SQL Server because other databases (not belonging to me)
co-exist.
Any help/ideas much appreciated - sorry that it is so late.
Kind Regards,
Andy.

.

[Andy]

Yes - That did it. I initialised the 1 bit field to zero for all records and
that solved the problem.

Thanks
Andy.

Post by Sylvain Lafontaine
I don't think that you have a locking problem here. By closing the
connection, any lock would have been automatically removed by SQL-Server.
Did you try to refresh the ODBC Link or better, remove it and then recreate
it? Each time you change the design of a backend table, you should
recreate - or at least refresh - the ODBC Linked Table.
Seeing this message is also normal under some conditions; for example if you
are using some incompatible types for the new columns; for example a
Nullable Bit field. Probably that by removing the extra field, you'll go
back to the previous situation.
You should take a look with the SQL-Server Profiler to see what's going here
exactly between Access and SQL-Server.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Andy
Hi guys - Hope you can help me out with what is problem a simple fix to an
important application at work.
I have a small application in Access 2003 with linked tables on a network
SQL Server 2000 instance (around 3000 rows type size)
All has been fine for several months until today when, stupidly, I was
adding a couple of fields to one table in SS Enterprise Manager (Design table
GUI rather than T SQL) and didn't realise that one User, actually in the same
room as me, was using the application at the same time in edit mode.
We both got locked out and had to abandon jobs (Access and SQL Server) - my
colleague even had to reboot her lap top to recover.
Now, if I try to edit a record in the Access application, I get the pop up
message (every time) about the "data having been changed by another User - do
you want to copy to Clipboard or Undo changes" etc.
I tried re-creating the offending table (with the same name) and inserting
the data from old into new and re-linking but that gave same error even with
the new table.
I guess that there is some kind of lock in one of the System tables that I
need to clear but need some help where to look. I can't reboot the Server or
start and stop SQL Server because other databases (not belonging to me)
co-exist.
Any help/ideas much appreciated - sorry that it is so late.
Kind Regards,
Andy.

.