Help SQL Server / Access locking problem

[Shaun]

Ok here is the situation…
Have an access 20002 application that I'm converting to have a SQL
Server backend (2000), the application has been in use with an access
backend for years, no real problem, just getting large so needed to
migrate to SQL.
I've converted the tables in SQL tables (I've used SQL quite a lot)
and link the tables when the login screen for the application pops up
using the following code

Dim CurrTableTDF As TableDef
Dim ODBCTablesRS As Recordset
Dim dbs As Database
Dim strConnect As String

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

strConnect = "ODBC;DRIVER=SQL Server;Server=" & strDSN &
";UID=abc;PWD=xyz;DATABASE=" & strDatabase & ";Network=DBMSSOCN;"

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set ODBCTablesRS = dbs.OpenRecordset("ODBC Tables (Local)")

Do While Not ODBCTablesRS.EOF
Set CurrTableTDF = CurrentDb.CreateTableDef(ODBCTablesRS![Table
Name], dbAttachSavePWD)
CurrTableTDF.Connect = strConnect
CurrTableTDF.SourceTableName = ODBCTablesRS![Table Name]
CurrentDb.TableDefs.Append CurrTableTDF
ODBCTablesRS.MoveNext
Loop

Exit_LinkSQLServerTables:

SysCmd acSysCmdClearStatus
Exit Sub

This seems to work fine
Now the problem, its to do with record locking on the main screen,
that screen is based on two tables, one is the main case table and for
each case, multiple logs can be raised. So there is a form and a sub
form with the sub form showing the logs for the main case. So far so
good.
If you double click a log, it pops up a modal form allowing the log to
be viewed / changed, if you do this, that modal form updates a date on
the main case screen to show that the case has been updated any by
who. The modal pop-up is then closed and if you try to change anything
on the main case screen you get the message "The data has been
changed". Conversely if you change something on the main screen then
go to a log for that case, you get the message "You Can't Assign A
Value to this object". The code stops and if you resume it it works
fine which indicates it's a lock as well (The code halts on the line
that updates the operator Id).
The update on the main form is done simply by setting the field on the
screen that is bound to the underlying record.
Anyone any ideas? This was fine under Access to Access (or at least it
didn't mind or error it)
I've also noticed that upon login, SQL Server shows 2 processes
assigned to the application, not sure why that is, most of the last
commands for these processes seem to be sp_executesql, etc.
One other thing, I am only testing this, there are no other users in
the system, the record lock options in access are all set to the
lowest possible (the same as they were pre conversion)
You can mail to sh***@abcparadisiixyz.co.uk removing the obvious bits
first!!

[Edward]

sh********@msn.com (Shaun) wrote in message news:<4a**************************@posting.google. com>...

Ok here is the situation?
Have an access 20002 application that I'm converting to have a SQL
Server backend (2000), the application has been in use with an access
backend for years, no real problem, just getting large so needed to
migrate to SQL.
I've converted the tables in SQL tables (I've used SQL quite a lot)
and link the tables when the login screen for the application pops up
using the following code

Dim CurrTableTDF As TableDef
Dim ODBCTablesRS As Recordset
Dim dbs As Database
Dim strConnect As String

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

strConnect = "ODBC;DRIVER=SQL Server;Server=" & strDSN &
";UID=abc;PWD=xyz;DATABASE=" & strDatabase & ";Network=DBMSSOCN;"

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set ODBCTablesRS = dbs.OpenRecordset("ODBC Tables (Local)")

Do While Not ODBCTablesRS.EOF
Set CurrTableTDF = CurrentDb.CreateTableDef(ODBCTablesRS![Table
Name], dbAttachSavePWD)
CurrTableTDF.Connect = strConnect
CurrTableTDF.SourceTableName = ODBCTablesRS![Table Name]
CurrentDb.TableDefs.Append CurrTableTDF
ODBCTablesRS.MoveNext
Loop

Exit_LinkSQLServerTables:

SysCmd acSysCmdClearStatus
Exit Sub

This seems to work fine
Now the problem, its to do with record locking on the main screen,
that screen is based on two tables, one is the main case table and for
each case, multiple logs can be raised. So there is a form and a sub
form with the sub form showing the logs for the main case. So far so
good.
If you double click a log, it pops up a modal form allowing the log to
be viewed / changed, if you do this, that modal form updates a date on
the main case screen to show that the case has been updated any by
who. The modal pop-up is then closed and if you try to change anything
on the main case screen you get the message "The data has been
changed". Conversely if you change something on the main screen then
go to a log for that case, you get the message "You Can't Assign A
Value to this object". The code stops and if you resume it it works
fine which indicates it's a lock as well (The code halts on the line
that updates the operator Id).
The update on the main form is done simply by setting the field on the
screen that is bound to the underlying record.
Anyone any ideas? This was fine under Access to Access (or at least it
didn't mind or error it)

[...]

Most likely the problem is that when Access retrieves the date field
from SQL Server, it truncates it. When you then try to update the
record, Access compares the two records and determines that its local
copy differs from the SQL Server copy. To overcome this, add a
Timestamp field to the SQL Server table. Access will then ignore all
other fields but this, and you should be able to proceed.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Shaun]

Hi
Cheers for the tip, I seem to have fixed it by using the Refresh
command after each field update to force the update back to the
server. I tried the Dirty property but that didn't work. I'll chekc
the point re field sizes as well just in case during the conversion
some were changed
Cheers all
Shaun

te********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

sh********@msn.com (Shaun) wrote in message news:<4a**************************@posting.google. com>...

Ok here is the situation?
Have an access 20002 application that I'm converting to have a SQL
Server backend (2000), the application has been in use with an access
backend for years, no real problem, just getting large so needed to
migrate to SQL.
I've converted the tables in SQL tables (I've used SQL quite a lot)
and link the tables when the login screen for the application pops up
using the following code

Dim CurrTableTDF As TableDef
Dim ODBCTablesRS As Recordset
Dim dbs As Database
Dim strConnect As String

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

strConnect = "ODBC;DRIVER=SQL Server;Server=" & strDSN &
";UID=abc;PWD=xyz;DATABASE=" & strDatabase & ";Network=DBMSSOCN;"

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set ODBCTablesRS = dbs.OpenRecordset("ODBC Tables (Local)")

Do While Not ODBCTablesRS.EOF
Set CurrTableTDF = CurrentDb.CreateTableDef(ODBCTablesRS![Table
Name], dbAttachSavePWD)
CurrTableTDF.Connect = strConnect
CurrTableTDF.SourceTableName = ODBCTablesRS![Table Name]
CurrentDb.TableDefs.Append CurrTableTDF
ODBCTablesRS.MoveNext
Loop

Exit_LinkSQLServerTables:

SysCmd acSysCmdClearStatus
Exit Sub

This seems to work fine
Now the problem, its to do with record locking on the main screen,
that screen is based on two tables, one is the main case table and for
each case, multiple logs can be raised. So there is a form and a sub
form with the sub form showing the logs for the main case. So far so
good.
If you double click a log, it pops up a modal form allowing the log to
be viewed / changed, if you do this, that modal form updates a date on
the main case screen to show that the case has been updated any by
who. The modal pop-up is then closed and if you try to change anything
on the main case screen you get the message "The data has been
changed". Conversely if you change something on the main screen then
go to a log for that case, you get the message "You Can't Assign A
Value to this object". The code stops and if you resume it it works
fine which indicates it's a lock as well (The code halts on the line
that updates the operator Id).
The update on the main form is done simply by setting the field on the
screen that is bound to the underlying record.
Anyone any ideas? This was fine under Access to Access (or at least it
didn't mind or error it)

[...]

Most likely the problem is that when Access retrieves the date field
from SQL Server, it truncates it. When you then try to update the
record, Access compares the two records and determines that its local
copy differs from the SQL Server copy. To overcome this, add a
Timestamp field to the SQL Server table. Access will then ignore all
other fields but this, and you should be able to proceed.

Edward