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
> SQL-server And Postgresql Identity Column Behavior With Access Fe's    
post Jan 19 2020, 08:27 PM

Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA

I am sharing the following remarks made by our Access/PostgreSQL guru, with his permission, to obtain your feedback:

No modifications were made to record ID controls in the Access forms to get them to work with Postgres. Where Access displays the auto-number value upon creating a record in the form, when using a server database it displays a placeholder in the ID field on form until after the record is saved.

As you stated, the ID is generated upon committing the record. Since Access doesn't have the ID at the time the record is committed it performs a read-back from the table using every field as matching criteria. The first matching record is the one Access displays in the form. At that time the sequence number is included with the results.

The auto-number at the server levels comes in two forms. There are sequence tables and IDENTITY columns. Postgres has used sequence tables for years and only recently added IDENTITY columns. An IDENTITY column is still dependent on a sequence. SQL Server has used IDENTITY columns forever and introduced sequences in SQL Server 2012. In SQL Server, IDENTITY columns and sequences are two different things.

In Postgres, a column that is based on a sequence is an int or bigint type. The default value is set from the sequence table value. In Access it should show up as a long integer with a unique constraint. I have never used bigint for sequences but suspect those will break Access after reaching 4G.

The ODBC driver dictates whether an IDENTITY or sequence value is returned to Access.

I'm not sure which IDENTITY bug you are referring to.

If it's the large gaps in IDENTITY values, it's always been there but starting in SQL Server 2012 the value now jumps by 1000 after a server restart. Microsoft claims it is a feature. Elitist developers say that if you used an IDENTITY column for anything other than a uniquer record identifier, such as an invoice ID that you are a bad programmer. They are both wrong, as IDENTITY columns were the only option to get a guaranteed unique automatic sequence before SQL Server 2012.

The fix for the jump in IDENTITY values since SQL Server 2012 is to use a sequence column with the NO CACHE option. Sequence values don't change when restarting the server. I think best practice moving forward is to use IDENTITY columns as record IDs and sequences as user viewable record IDs. Do the same for any application where IDENTITY value gaps are causing issues. Maybe just use sequences and no IDENTITY columns.

If the bug is a problem where Access switches to the wrong, previously existing record after saving a new record, that's always been there and always will be. The problem is what I mentioned earlier. On a new record, Access has to do a read-back that matches on all columns. The ID won't be in the inserted record on the Access side for comparison. So, if all of the other columns that have values in them are identical to one or more existing records, Access will display the first one it finds.

If Access returns the wrong record after an update on a server table that also has a trigger function that inserts or updates other records, the problem is because the @@IDENTITY value is set to the IDENTITY column of the last record updated, regardless of the table. Access will attempt to find that IDENTITY value in the table that's bound to the form. The work-around is to capture the IDENTITY value at the start of the trigger and restore it at the end.

Sequences can still have gaps but they will be caused by removing records and not by simply restarting the server.

Postgres is the full version no matter where it is installed, and its free! It can be installed on Windows, Linux and Mac, workstation or server and there are no restrictions, and no server or software license fees to be paid.

There's now a Linux version of SQL Server. I've had no need to try installing it.

Besides the storage size restriction in Express versions of SQL Server, they also only support one processor core so no parallelism.

Hope that helps. You may forward this information if you want.

This post has been edited by FrankRuperto: Jan 19 2020, 08:35 PM

Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    26th May 2020 - 04:38 PM