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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Dbseechanges Can Allow A Value For Identity Column, Access 2010    
 
   
AlbertKallal
post Dec 5 2019, 12:19 PM
Post#21


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


@Dave

As a general rule? Well ODBC is just ODBC. However, as I pointed out, Access ALWAYS sends a identity insert command to SQL server for near everything it does. As I pointed out, Access can't send this command to other servers when they don't have that command. So, it would seem that Access makes "special" efforts when using SQL server.

I will also point out that access does not send a corresponding command to turn off identity-insert. (it turns on implicit transactions - that means the command that follows is by default wrapped in a transaction - and then the identity-insert goes out of scope.

it is rather un-likely that access sends this command (identity-insert) to other servers (it is possible it does and error traps it - this detail I don't know). So, even the start of a transaction, sending the idenity-insert command is a set of special steps that Access always uses when working with SQL server.

I don't know MySQL etc. that well - don't know how to use their profiles or whatever they call it. However, for SQL server I can confirm that Access spits out and sends a identity-insert for every set of commands it spits out. Including recordsets (so you can use any PK you want - as long it don't clash you can insert that record. But you ONLY are able to do this because of all the extra efforts Access does in regards when working with SQL server.

So, for anyone using MySQL etc., if they can run a profiler and look at the commands sent - we would know this answer.

R
Albert.
Go to the top of the page
 
FrankRuperto
post Dec 5 2019, 12:58 PM
Post#22



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


Albert,

Me thinks that since no ODBC error msg is received in Access when inserting rows into Informix db that Access is not wrapping a set_identity_on command into transactions. I dont know if turning on the ODBC trace log would reveal anything.

In Access, when viewing table designs of Informix tables linked via ODBC, the serial (autonumber) type fields appear only as Number fields. With Oracle it also behaves the same, so I suspect its the same with SQL Server, MySQL, SQLite, etc.

The only new Acess feature I have heard of is BIGINT support when linking to db servers.
This post has been edited by FrankRuperto: Dec 5 2019, 01:08 PM

--------------------
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 Dec 5 2019, 03:48 PM
Post#23


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


QUOTE
In Access, when viewing table designs of Informix tables linked via ODBC, the serial (autonumber) type fields appear only as Number fields.


Ok, but what about when such a column is defined as a PK? "Most" of this discussion centers around PK's and allowing to insert them.

Remember, Access is sending this identity-insert command not because of a auto number, but BOTH auto number and a PK column.

Access tables ONLY allows one auto number column in a given table. SQL server allows as many as you want. But the idenitfy-insert is sent to allow updates of the PK column.

It just so happens for most of this discussion that we talking about auto numbers, but in fact we REALLY are talking about a auto-number column that is a PK column.

From what I can read?
MySQL and most of these other systems allow inserting of PK column without having to issue special commands. This is not the case for SQL server.

R
Albert
Go to the top of the page
 
FrankRuperto
post Dec 5 2019, 05:00 PM
Post#24



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


QUOTE
Ok, but what about when such a column is defined as a PK? "Most" of this discussion centers around PK's and allowing to insert them.


First off all, PK's in Access or in any other db server dont necessarily have to be an autonumber or serial datatype field. PK's can even be a text/CHAR type field, although using the latter is ill-advised. However we customarily will use autonumber type fields as PK's in order to establish joins with child tables.

Okay now that I got that out of the way, to answer your question, when viewing ODBC-linked db server tables in Access table design mode, a table with an identity PK field will appear to Access like any other ordinary 'Number' field with a little key icon next to it, which means it has an index on it, however Access does not know if its the primary key for that table, it just knows the Number-type field has an index on it. This is due to how ODBC is mapping the field and Access has no control over it, the db server is the one handling the incrementing counter, the no dups/no nulls constraints, etc.

QUOTE
Access tables ONLY allows one auto number column in a given table. SQL server allows as many as you want. But the idenitfy-insert is sent to allow updates of the PK column.


lol, I seroiusly doubt SqlServer, or any other engine allows more than one autonumber field in a table. All Relational SQL-compliant databases only allow one autonumber datatype field. Also, only one PK per table is allowed, that usually being the autonumber field. But maybe SqlServer is not relational-compliant in that aspect since it has no true native autonumber-type field, rather they're integer-type fields with an 'identity' property on it that manages the incrementing counter for it, as well as imposing the no dups/no nulls constraints. Does having multiple autonumber field in one table make sense anyway?

At this point, I can not verify if Informix is receiving an identity_insert_on command when inserting rows into a table, my suspicion is "NO", Informix or any other db server with a true autonumber-type field will not receive it because the only way of turning on/off a native autonumber-type field is by altering the field from an autonumber to an integer field and vice versa. The db server would cough out an error at the Access client, probably with the same catch-all ODBC err msg it displays most of the time.
This post has been edited by FrankRuperto: Dec 5 2019, 05:21 PM

--------------------
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 Dec 5 2019, 07:52 PM
Post#25



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


I forgot to include this table design view and the DDL in my last post. GRANT_ID is the Informix table's PK and its a SERIAL datatype.
This post has been edited by FrankRuperto: Dec 5 2019, 08:39 PM
Attached File(s)
Attached File  LASPAU_TABLES.PNG ( 140.54K )Number of downloads: 8
Attached File  DDL.PNG ( 38.13K )Number of downloads: 8
 

--------------------
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 Dec 7 2019, 01:05 PM
Post#26


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


My point was that autonumber (or identity column) does not have be the PK.

So yes, I would say a most don't allow multiple increment columns, but certainly some do! For example PostgreSQL or Oracle allow this.

However, use of update triggers or even by setting a default value as a expression would allows you to have "many" auto increment columns.
(now to be fair, that would be a cheat answer! and to be fair, were are talking about automatic increments here - not triggers).

However, Oracle for a long time did not have a auto-increment column - so you had to use a trigger. So, not all databases have or always did support some auto-increment column.

And as I point out, a number of them them allow you to specify a auto increment "setting" that is independent of "identify" or PK.
As a result, one needs to distinguish between a "identity" column (most only allow one - most don't force this to be PK), and a auto increment feature of that database. They are NOT always attached as the same concept. The term "identity" column in most cases does suggest a increment column, and only one per table is "implied" by that term and concept.
But as noted, some databases allow more then one column to increment.

And some even extend this concept even farther - Oracle has a create-sequence command and again this allows more then one column to increment.

So, like most things in life? Such things are not always "always" or "always not".

To be fair, I thought SQL server allowed more then one auto-increment column per table - it does not (unless you use a trigger, or a default expression as a function (like we could in foxpro).
So some server database systems do allow this, even if we restrict the "thing" we are talking about as meaning no triggers, some don't restrict the specification to "sequence" the column to one column in the table.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
gemmathehusky
post Dec 7 2019, 04:41 PM
Post#27


UtterAccess VIP
Posts: 4,750
Joined: 5-June 07
From: UK


@albert/frank

my expertise is clearly insufficient to discuss this technically and my comments are more from empirical evidence.


For instance given
set rst=currentdb.openrecordset("somequery")

produces no error with access, but produces an error with sqlserver that dbseechanges is required when the table has an identity bit set. (or something similar).

so this sort of thing instead.
set rst=currentdb.openrecordset("somequery", dbopendynaset, dbseechanges)

so assuming the OP's statements are "correct", then it would seem that the process would fail only if the update requested by the statement was not permitted, but ODBC might not (be able to) tell you the precise reason it failed, just that it did fail.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 07:46 AM