UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> A Heads Up To Anyone Using Access With A SQL Server Back End    
 
   
GroverParkGeorge
post Jan 16 2020, 12:20 PM
Post#1


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


A new Access bug, apparently related to the recent update for version 1912 of Office (out since January 8th, 2020) can result in Access failing to recognize Identity Insert on AutoNumber fields , rendering them as Long Integer Numbers (not AutoNumber) in Linked Tables in Access. Details are emerging. However, if you experience problems with linking/relinking tables correctly, or using Recordset.Bookmark to retrieve a newly inserted Primary Key in VBA, please share your experiences so we can more fully update the Microsoft Access team.

Update: Microsoft posts an official notice: https://support.office.com/en-us/article/ac...f1-7e043812d60d

Thank you.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 16 2020, 12:42 PM
Post#2



Posts: 644
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Does this have any relation to the implied IDENTITY INSERT ON mentioned in this topic: https://www.UtterAccess.com/forum/index.php...t&p=2735879

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 01:01 PM
Post#3


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Not likely, no. The problem here is that Access no longer recognizes that a field in SQL Server has the Identity_Insert property, and instead of rendering it as an AutoNumber, it renders the field as a standard Long Integer. This, of course, causes lots of problems.

I understand that it is going to manifest if a table is linked or relinked in Access.

There is another report of an issue related to using the BookMark property of a recordset to retrieve the most recently inserted value in such fields.

I hesitate to be more specific until we get better clarity.

That problem predates this update by a couple of months and is not likely to be related.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 16 2020, 02:02 PM
Post#4



Posts: 644
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


AFAIK, whenever I view from Access a SQL Server table, or any other db server, that has a field with an identity constraint, ODBC always maps it as a long integer with a unique index, but the db engine always increments the valueas new rows are inserted.

Note: I wanted to post an image of an Access table design view of a db server table in this post, but I dont see the browse and upload buttons.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 04:44 PM
Post#5


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


"...the db engine always increments the valueas new rows are inserted."

That is what IDENTITY INSERT refers to, and that is what is now broken in some (but not all versions of Access).

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 04:45 PM
Post#6


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA



Microsoft is working on a public notice about this problem. We'll link to it when it appears.

An existing work around is to roll back from version 1912 to version 1911, for those impacted.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Jan 16 2020, 05:49 PM
Post#7


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


In the meantime, this thread may help explain the issue some users are reporting https://social.msdn.microsoft.com/Forums/en...forum=accessdev

I can confirm the issue with new or refreshed tables in my own Access 365 applications.

Luckily I mainly use A2010 which isn't affected.
As an experiment I created a new A2010 database, linked several SQL tables with autonumber fields.
I then reopened that app in A365 - the autonumber fields were still correct (as long as you don't refresh the links in A365)

If you do that in reverse - link in A365 then open in A2010 THEN refresh the links, the autonumber datatype is once again correctly identified
So that's an additional work-round that may help some people

UPDATE:
The official MS response is at https://support.office.com/en-us/article/ac...f1-7e043812d60d
(Sorry I hadn't seen Frank's reply)

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Jan 16 2020, 08:37 PM
Post#8



Posts: 644
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


MS published the bug here: https://support.office.com/en-us/article/ac...f1-7e043812d60d

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 04:13 PM
Post#9



Posts: 644
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


I am wondering if this bug also extends to other db servers linked to Access. We have Access users linking to PostgreSQL, Oracle, Informix and FireBird backends. We're going to check and report if they're also affected.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Jan 17 2020, 06:11 PM
Post#10


UtterAccess VIP
Posts: 2,954
Joined: 12-April 07
From: Edmonton, Alberta Canada


Don't know, but I don't think this applies to other systems.

the issue is that if you have a PK column, and a identify (for auto increment), then when ODBC maps that table to Access, access WILL show that column as autonumber (not just a long with a unique index).

As pointed out here:
If you link the tables with a non broken version then in table design mode (for the linked table), then Access correctly shows autonumber.

However, if you re-link with a broken version then the "autonumber" setting is lost. Adding the record to SQL server still works, but the book mark that we OFTEN use to re-point the reocrd back to the just added record fails.

So, code like this:
CODE
   rstRecords.Add
   ' code here to set columns and values

   ' now save - and get new autonumber
   rstRecords.Update
   rstRecords.Bookmark = rstRecords.LastModified
   lngNext = rstRecords!ID

You do not (and never did) do the above bookmark for a existing record, but for ".add", you ALWAYS had to do the above.
This is because when you add a record (dao), the update command causes the recordpointer to move off the record. DAO always did this - my real pet peeve - since ADO never did this.

If the database was JET/ACE?
Then
CODE
   rstRecords.Add
   ' code here to set columns and values

   lngNext = rstRecords!ID

   rstRecords.Update


So the above was often typical code for JET/ACE but the first code snip is required for SQL server (because you don't get the autonumber UNTILL the record is saved (the update command is executed). With jet, the instant the record is dirty, you can freely grab the autonumberr ID - even before the update command.

So DAO recordsets (and ONLY on a insert) requires the above to get/grab the JUST added (created) autonumber. So with JET, the autonumber is available right away. However, the book mark now fails on this line:

CODE
   lngNext = rstRecords!ID

The error is "record is deleted". So, this is because the linked table shows "long" and not autonumber. It will cause some code to break, and I spent the whole day yesterday dealing with support calls as a result.

So a lot of code will work, but code that grabs the autonumber as per above will fail.

If you link the tables to the server and then deploy - you not have this issue. However, if the bad version of Access re-links the tables (by code, or the UI), then the autonumber setting is lost - and you find bugs cropping up.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 07:31 PM