By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Linked Tables - Identity column needed ?

P: n/a
Hi All,
1. Created table in SQL Server 2K with Primary key as int Identity
2. Link to table in MS Access 2K
3. Using form in MSAccess to update the linked table

I want SQL server to automatically update the int identity column as it
would normally. i.e. no need to enter a value even though it is a NOT
NULL field. SQL Server recognises this , allows the row details to be
entered without the int identity and creates it when moving to the next
blank row (in Enterprise Manager) or allows an Insert in Query Analyser
without the int column used (in fact it demands that the int column is
not in the Insert)

How do I update the row in Access, as it returns an error message
saying that the int column cannot be null. The properties cannot be
changed as it is a linked table. I would appreciate help from those
that KNOW the answer as I have tried all the silly ideas such as trying
to set the int identity to NULL in SQL Server (which is not allowed of
course but it was a long shot!)

Thanks

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
OOPS - Problem solved
Don't we all love MS ?
For those that would like the answer....
If you do not set up the SQL Server int identity as a Primary Key,
Access linkes to it as a Number field NOT as an Autonumber.
Setting it as a Primary key solved the problem.
Why on earth MS Access cannot link a table and recognise int identity
and make it an autonumber is beyond me - maybe there is a good reason !

Nov 13 '05 #2

P: n/a
It's you that's at fault, not MS.

SQL Server doesn't hold your hand quite as much as Access, so you can
set up tables with no primary key and it doesn't complain, whereas
Access prompts you to do so. In your first post you say that you set
the Int Identity column as the Primary Key which is, as you surmise,
analogous to Access' Autonumber. But in your second post you admit
that you simply set up the int column to be an identity column, but
hadn't made it the PK, and you lambast Access for not knowing that that
was your intention! There may be any number of reasons to set up an
identity column and not to make it a PK. It's up to you to be specific
in your data design. I, for one, would be absolutely furious with a
product that assumed that an Int Identity column in a SQL Server table
was the PK unless I had made it so.

Edward

Nov 13 '05 #3

P: n/a
Thanks for a rather odd reply.
Perhaps I wasn't clear. If I define an int identity in SQL Server, I
would have expected it to recognise this as an Autonumber.
If I specify a field as a Primary Key in SQL Server, I would expect
this to be recognised as a primary key.
Simple really.

te********@hotmail.com wrote:
It's you that's at fault, not MS.

SQL Server doesn't hold your hand quite as much as Access, so you can
set up tables with no primary key and it doesn't complain, whereas
Access prompts you to do so. In your first post you say that you set
the Int Identity column as the Primary Key which is, as you surmise,
analogous to Access' Autonumber. But in your second post you admit
that you simply set up the int column to be an identity column, but
hadn't made it the PK, and you lambast Access for not knowing that that
was your intention! There may be any number of reasons to set up an
identity column and not to make it a PK. It's up to you to be specific
in your data design. I, for one, would be absolutely furious with a
product that assumed that an Int Identity column in a SQL Server table
was the PK unless I had made it so.

Edward


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.