Problem with Table on SQL Server 2005 using Access 2003 as a Front End

[MSAccessRookie]

My group is currently reaching the end of an Access 2003/SQL Server 2005 Migration Project.

• The original system has a standard Front End/Back End setup with about 35 Tables in the back End.
  • The first Phase of the Project required that four of the Tables be migrated to SQL Server while the remaining Tables would remain in Access and the Front End would in effect have two Back Ends (One in MS Access and one on SQL Server)
  • The SQL Server Tables were created using the MS Access 2003 Export Option.
    • The Tables were compared and appeared to be the same in structure
    • The Data was compared and verified as the same

It has been determined that there is a problem updating or deleting records in one of the Tables. None of the other Tables has any problems.

Users are able to enter Records into the Table, but are not able to Modify or Delete them once they are entered.

If a user attempts to MODIFY a record, an MS Access Error Message appears.

This record has been changed by another user since you started editing it.

If a user attempts to DELETE a record, an MS Access Error Message appears.

The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Note that in each case, there is only one user using the database during the test (as far as we can tell).

The Table having the problem is the only Table that also contains an AutoNumber Field, and we are looking into what (if any) that fact contributes to the problem.

Has anyone else had any similar issues with Tables Linked to SQL Server?


UPDATE:

The problem has been resolved. Apparently, MS Access is unable to determine the update status of a record in a Foreign Database, nad uses a combination of "Optimistic Locking" and Field Data comparison to determine the status, and this does not always work. In my case, it worked when we were adding, but not when we were updating or deleting.

The end result is that we needed to modify each Table on the SQL Server to add a new field (type Timestamp) called tStamp at the end of the record. Once that was in place, all of the problems went away.

 

[Rx_]

Thank you for sharing your soltution.
What tool was used to migrate the tables to SQL?
What driver was used to link the tables from Access to SQL Server? (i.e. ODBC, SQL Native Client, ...)

In my recent migration from Access 2010 to SQL Server 2008 it was necessary to modify the code by adding dbSeeChanges (wher strSQL is the SQL statement:
Set rsCountyNumber = CurrentDb.OpenRecordset(strsql, 2, dbSeeChanges)

To migrate: Microsoft SQL Server Assistant for Access It is a free MS tool.
The Microsoft SQL Server Native Client was downloaded and now appears on the ODBC connections.
Code was added for a DSN-Less connection http://support.microsoft.com/kb/892490
for the linked tables.
In my case, the autocount, date fields and other datatypes appear to have moved over (almost) perfectlly.
The date types did have some issues with the standard odbc drivers in my case.
A while back, the same Native Driver was used for an Access 2003 on a SQL 2005 to SQL 2008 upgrade with no issues.

 

[mdlueck]

The end result is that we needed to modify each Table on the SQL Server to add a new field (type Timestamp) called tStamp at the end of the record. Once that was in place, all of the problems went away.

I will take that as affirmation to my Client/Server architecture design. When I perform UPDATE stored procedures, I do so via updating the record based on the key field(s) AND the lastsavetimestamp of the record. Only if the record has not been changed since it was brought into edit mode is the UPDATE successful. If someone else has changed the record, the lastsavetimestamp will not match, and the UPDATE will not change a record. In that case I pop an informational dialog that "someone else seems to have changed the record. Roll back and begin the edit again" which bringing the record into edit mode always does a fresh SELECT of that particular record.

The problem you were describing was very bazaar indeed!

 

[Galaxiom]

BTW There is also another problem that has identical symptoms. Check the SQL table for any Bit datatype fields. These are used in SQL Server as a replacement for the Boolean (Yes/No) fields in Access's tables.

Access cannot update records in SQL Server linked tables if there are Bit fields holding Null values. This happens because the database engine used by Access thinks the record has been changed due to the confusion between Null and 0 in the two systems.

This is evident when you check the bit field for Nulls from an Access Select query with an Is Null criteria. The results appear incongruous because they will be selected as Null yet return 0 as the value.

Update them to 0 or 1 with a query or enter True/False directly via Management Studio. Then put a default value on the SQL table and set them to not allow Nulls.

Another alternative if you do need a Null option is to use TinyInteger. This is mapped as a Byte field in Access and there is no confusion.

 

[Galaxiom]

The end result is that we needed to modify each Table on the SQL Server to add a new field (type Timestamp) called tStamp at the end of the record. Once that was in place, all of the problems went away.

It may well be that you have the Null Bit field issue. When a record includes a timestamp field the engine relies entirely on that field to determine the update status. The bit field is no longer a problem because it is ignored.

The bit field issue should still be fixed if the difference between Null and False matters.

BTW. TimeStamp datatype is actually nothing to do with Time at all. It is actually a type of UID (a big random hex value).

 

[daliborpuljiz]

The end result is that we needed to modify each Table on the SQL Server to add a new field (type Timestamp) called tStamp at the end of the record. Once that was in place, all of the problems went away.[/QUOTE]

Thanks. I've been facing similar problem for a month now and avoiding it by different techniques, among which was the one that converts bit values to smallint. Just until this post came along which proved to be valid solution.

 

[BigHappyDaddy]

FYI - I had a simliar story. SQL Server backend, Access front end. I could append new records to the backend, but couldn't update or delete. I don't remember the technical reason, but basically Access and SQL Server don't handle indexing the same way using an ODBC connection. I converted my connections to ADO and problem was solved.

 

[SQL_Hell]

It may well be that you have the Null Bit field issue. When a record includes a timestamp field the engine relies entirely on that field to determine the update status. The bit field is no longer a problem because it is ignored.

The bit field issue should still be fixed if the difference between Null and False matters.

BTW. TimeStamp datatype is actually nothing to do with Time at all. It is actually a type of UID (a big random hex value).

Thanks for this, I knew of the BIT field issue and knew that adding a timestamp
field was one of the solutions, but never knew why.
Good to get the final piece of the puzzle

 

[Lightwave]

Hey guys I came here from another thread

Here's a TSQL script that should sort through a database find all tables where there are Bit fields / set values to 0 where existing value is null and alter the default field behaviour of allow nulls to no..

BEWARE USE AT YOUR OWN CAUTION

credit to our DBA Steven McKinstray - SQL Server 2008 R2

BEGIN TRANSACTION

DECLARE @tableName varchar(50)
DECLARE @columnName varchar(50)

DECLARE db_cursor CURSOR FOR
SELECT 
	t.name as [Table Name],
	c.name as [Column Name]
FROM 
	sys.tables t,
	sys.columns c,
	sys.types ty
WHERE 
	t.object_id = c.object_id AND
	ty.system_type_id = c.system_type_id AND
	ty.name = 'bit' AND
	c.is_nullable = 1
ORDER BY
	[Table Name], [Column Name]
	
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName, @columnName

WHILE @@FETCH_STATUS = 0   
BEGIN   
	EXEC('
	UPDATE [' + @tableName + ']
	SET [' + @columnName + '] = 0
	WHERE [' + @columnName + '] IS NULL
	')

	EXEC('
	ALTER TABLE [' + @tableName + ']
	ALTER COLUMN [' + @columnName + '] bit NOT NULL
	')

	EXEC('
	ALTER TABLE [' + @tableName + ']
	ADD DEFAULT 0 FOR [' + @columnName + ']
	')

	FETCH NEXT FROM db_cursor INTO @tableName, @columnName
END

CLOSE db_cursor   
DEALLOCATE db_cursor

ROLLBACK
--COMMIT