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 Yesterday, 12:19 PM
Post#21


UtterAccess VIP
Posts: 2,895
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 Yesterday, 12:58 PM
Post#22



Posts: 332
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: Yesterday, 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 Yesterday, 03:48 PM
Post#23


UtterAccess VIP
Posts: 2,895
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 Yesterday, 05:00 PM
Post#24



Posts: 332
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: Yesterday, 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 Yesterday, 07:52 PM
Post#25



Posts: 332
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: Yesterday, 08:39 PM
Attached File(s)
Attached File  LASPAU_TABLES.PNG ( 140.54K )Number of downloads: 4
Attached File  DDL.PNG ( 38.13K )Number of downloads: 2
 

--------------------
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
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 01:09 AM