Access Linked to SQL: Wrong data shown for a newly created record

Question

I'm using tables in Access (2013), that are linked to an SQL database. The primary key is a column 'RowId', containing a GUID.

When I'm adding a new record to the table, and I leave the RowId blank. The record is inserted in the SQL database, but then the added record is filled up with data of another record.

After investigating the problem with SQL Server Profiler, I discovered that this happens:

First, an INSERT statement is processed:

exec sp_executesql
    N'INSERT INTO  "{table}"  ("{column1}","{column2}", {column4}") 
      VALUES (@P1,@P2,@P3)',
    N'@P1 int,@P2 nvarchar(50),@P3 nvarchar(50)',
    3438,
    N'Smith',
    N'Sofia'

But then, a second statement is passed, in order to get the values of the saved record (I guess):

declare @p1 int
set @p1=5
exec sp_prepexec
   @p1 output,
   NULL,
   N'SELECT "{column1}","{column2}", {column3}","{column4}","{column5}", {column6}",
     "RowID","RV"  FROM "{table}"  WHERE "RowID" IS NULL'
select @p1

The RowID is not initially given when creating the record, so now Access asks for an row with RowId IS NULL. This happens twice, and fails twice. Then, the RowId is asked by the parameters that are given in the insert statement:

exec sp_executesql
   N'SELECT "{table}"."RowID"
     FROM "{table}"
     WHERE "{column1}" = @P1 AND "{column2}" = @P2 AND "{column4}" = @P3',
   N'@P1 int,@P2 nvarchar(50),@P3 nvarchar(50)',
   3438,
   N'Smith',
   N'Sofia'

If there is another record with the same parameters (eg 3438, Smith, Sofia) exists, the RowID of this record given back, and the new record in Access is filled in with data from this other record (including the wrong RowID). Every action on this record is made on the wrong record now, as the wrong RowID is given with the statements. After I refresh the table, the newly inserted record is shown, with the right data and new generated RowID.

How can I prevent this from happening? How can I make it so the right data is shown immediately after I insert the row?

are you using passthrough query? why not just use vba to insert the rows? — Krish
as @krishKM says: just use DAo like if it was an Access table. I know IT guys always think that inerts/updates should be done through SP, but then why using Access ? Just multiply your budget by 10 and use Visual Studio+SPs — Patrick Honorez
This sounds very close to a support incident that I submitted several years ago. Basically, they know about it and don't care. See this thread for some possible ideas that may help in your case: groups.google.com/forum/#!original/comp.databases.ms-access/… — AVG

Andre Andre · Accepted Answer · 2015-09-24T15:02:05

I suspect that either Access doesn't know that RowId is the primary key, or it (or the ODBC driver) cannot handle the GUID datatype properly.

If you open the linked table in design view, what datatype does Access show for RowId? And does it show the "key" icon for the primary key?

In a comment to Recommendations on using SQL Server GUID from MS Access , David-W-Fenton (who knows a lot about these things) writes:

If your front end is in Access, then there are major problems with handling GUIDs: http://www.trigeminal.com/usenet/usenet011.asp?1033

Unfortunately this page is down.
Edit archive.org to the rescue: INFO: Replication and GUIDs, the Good, the Bad, and the Ugly
Although this seems to be about Jet GUIDs, not SQL Server, so probably not applicable.

Some more things about GUIDs: What are the best practices for using a GUID as a primary key, specifically regarding performance?

Access Linked to SQL: Wrong data shown for a newly created record

1 Answers