473,218 Members | 1,422 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

Help SQL Server / Access locking problem

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!!
Nov 12 '05 #1
2 2104
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
Nov 12 '05 #2
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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: ACP | last post by:
Hi all, Have a situation that my company has never run across before. Client is running NT4 for the domain server, using terminal services 2000 and running an application with a SQL Server...
19
by: Mark C. | last post by:
The company I work for has been running Access 2000 on a Windows NT server with Opportunistic Locking turned off on the server without issue for almost a year. We have just switched to a Windows...
6
by: alanknipmeyer | last post by:
Hi, I`m in the process of migrating a Access 2002 (Run in 2000 mode) from Windows 98 to Win2K Server. It is a shared resource via a file share on the 98 Server. Client systems are Win98 with the...
3
by: swingingming | last post by:
Hi, for 5 weeks, I finished my mdb project. Thanks to all you guys. Now, I would like to put it on a server then 5-6 people can share it. I heard about the splitting back-end database, put it on a...
6
by: Emily Jones | last post by:
Dear All I've got a MS Access application. Split FE/BE. 9 user LAN. I'm considering moving to a server backend. Probably SQL Server, with an Access FE still. Because: 1. We've been getting...
1
by: pj | last post by:
Previous question on record locking problems drew no response. Two users hit the same area in an Access 2003 SP1 db running on Terminal Services. Constant record locking messages appear although...
2
by: cryon.b | last post by:
Hi To All, I took up the IBM sample testfor Exam 700 today and I have the test tomorrow,I have some questions for which Iam not sure about the right answer,can anyone please guide me as to what is...
2
by: goodlie | last post by:
Hello; I have an access 2003 db on XP OS split with backend on sql2K. I want to put the front end on a fileserver for easier maintenance. I have 7 users using the database at any given time. ...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.