Can't Add Data on SQL Server 2005 Table in Access 2002 FE

[kagard]

Greetings:

I have an application in which I recently upsized the tables (only) to
SQL Server 2005. I can edit most of the tables directly, or on the
application's forms. Two of the child tables are locked, though. The
subforms in which their data appears are locked, as are the tables
themselves.

I did some research and found that tables without primary keys can't
be edited, so i added an identity field to one of the tables to test
it. I relinked in Access to refresh the table definition. The new
column shows in datasheet view and it contains the values I expected.
I still can't add, delete or edit values in the table, though. (I can
open this table in SQL Server and edit it's values.)

TIA
Keith

P.S. Here is some additional information that may help you help me:

Table description from Access
ODBC;DRIVER=SQL Server;SERVER=UNIBLAB\HROACCESS;APP=Microsoft Office
XP;WSID=OMG120113DARCY;DATABASE=DOHSecurityIDCardData;TABLE=dbo.tblIDCards

Primary key added
CARD_SEQ_ID
Is Identity = Yes
Identity Increment = 1
Identity Seed = 1

 

[kagard]

I forgot to mention that the following code DOES add a record to the
table that is, otherwise, locked.

TheSQL = "INSERT INTO tblIDCards (DRIVERID) VALUES (" &
TheDriverID & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL TheSQL
DoCmd.SetWarnings False
Me.Requery

I expected this to fail, but it does add a record.

Keith

 

[Stefan Hoffmann]

hi,

I did some research and found that tables without primary keys can't
be edited, so i added an identity field to one of the tables to test
it.

This is necessary, as only tables with a primary key can be modified
(DELETE, INSERT, UPDATE).

I relinked in Access to refresh the table definition.

Open your linked table in the design view. Does it display the primary
key symbol?

Delete the linked table and link it again. Relink a table normally won't
check for primary keys.


mfG
--> stefan <--

 

[Douglas J. Steele]

When you added the identity field, did you remember to create an index and
mark it as Unique?

 

[Albert D. Kallal]

Actually, I STRONGLY suggest you avoid the identity field type in sql
server.

That is a HUGE multi GUID type field, and if you ever need to link a
sub-form (child table) to that table, you HAVE TO USE the SAME data type of
identify in the CHILD table. (you can't use int, or bigInt).

That means you will use a identify type field in the child table
(it is a foreign key..and you have to set the field as non unique,
but you MUST use a type of identity here..and that is often
confusing to us access developers).

Take a quick look at how the other tables were up-sized. You notice for
the primary key in those other fields...it is a int field..and set as
PK..and incrementing. You should do the same for those tables that did
not have a PK.

Once again, don't confuse the identity data type in sql 2005 with the
"autonumber" in ms-access. They are NOT equivalences...and are rather
much different. It should be further pointed out we don't have a
easy defined data type in ms-access for that identity field.

Furthermore, that identity field as mentioned is a large data type and it
harder for sql server to index it.

 

[kagard]

Hi Doug:

Thanks for the reply. Yes, I designated the identity field as my
primary key, which has created an index and set its unique property to
yes.

Keith

 

[kagard]

Hi Albert:

Thanks for taking the time to reply to me question.

I created a new blank db and table, and created two fields in the
table. I set up a primary key field as an AutoNumber and added a
default text field. When I upsized this table, it showed up in SQL
Server as a primary key field a data type of int. Is Identity = yes,
Identity Increment = 1, Identity Seed = 1. The only difference between
it and the one I set up is that I declared it as a bigint. I'm not
sure where that leaves me...

Keith

 

[kagard]

Hi stefan:

Thanks for your post. Actually, refreshing the link did display the
new field with its correct values.

Keith

 

[kagard]

Hi again:

Let me mention again what seem to me to be the curious part of this
problem. Why can a query that I execute via VBA code update this table
when it is otherwise locked?

Keith

 

[Albert D. Kallal]

int. Is Identity = yes,
Identity Increment = 1, Identity Seed = 1. The only difference between
it and the one I set up is that I declared it as a bigint. I'm not
sure where that leaves me...

We having a bit of a miscommunication here. When I said avoid the identity
data type field in sql 2005, I did NOT mean to avoid using the identity
**property** for a field data type of int, or bigInt.

I was referring to selecting the **actual** data field type in sql server
called

UniqueIdentifier

You can choose the above type in place of int, big int, and I was suggesting
you AVOID this data type (And I was mostly suggesting to avoid it because
of confusion it can cause).

The only difference between

it and the one I set up is that I declared it as a bigint. I'm not
sure where that leaves me...

It should work fine with bigInt. Try changing that to Int and see if it
works..(I really don't think the presence of bigInt is your problem here).

While it is fine that the field type of int or bigInt can be set as a
identify field, I am still suggesting that you avoid using UniqueIdentifier
data type field, and setting that as a identify field (and also a
autoincrement).

So use:

int - set as identify, auto increment- can use
BigInt - set as identify, auto increment- can use

UniqueIdenitfy - set as identify, auto increment- NO!!!
^ don't use ^ ^^ don't use this one ^^

So, remember, since we are dealing with the PK, make sure to delete the
table link EACH TIME you make a change to the table structure. (we
normally would not have to do this, but since your having a problem
here, we need to delete the table link EACH TIME until you get this
working). Did you remember also to add a time stamp field?

 

[aaron.kempf]

Albert;

I'm really having a hard problem trying to figure out what you're
talking about. I just wear. It's like you're confusing TimeStamp or
something ridiculous.

I'm just really really really curious what you're talking about... I'd
love to find out more about your experience with identity (INTEGER)
fields in SQL Server.

Thanks

-Aaron

 

[Albert D. Kallal]

So use:

int - set as identify, auto increment- can use
BigInt - set as identify, auto increment- can use

UniqueIdenitfy - set as identify, auto increment- NO!!!
^ don't use ^ ^^ don't use this one ^^

**** HOWEVER while the above is correct, you CAN NOT use bigInt as a primary
key!!!

So, you can set the field as unique, auto increment, and you essentially
have a field that is your primary key. However, you can't set it as as
primary key (and, therefore I suggest you AVOID bigInt if you using it for
primary key)


So, my mistake here in at least not Realizing that bigInt CANNOT be the
primary key here (bigInts work fine in linked tables...but NOT as primary
key).

You have to use Int. (remember, on sql server, int is the same as our long
data type).

As someone recent pointed out, I don't see the need for a primary key with a
range of:

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

The above is not billion, but in fact has a trillion record range. There is
simply little, if any reason to use bigInt here....

 

[Albert D. Kallal]

Albert;

I'm really having a hard problem trying to figure out what you're

talking about. I just wear. It's like you're confusing TimeStamp or
something ridiculous.

I am talking about the fact that 2005 has a data type called
unquieIdentifier. This data type is NOT to be confused with a int, bigInt,
or even a int field set as primary key, and autoincrment.

While in jet based systems we OFTEN use autonumber, the *child* data type
field is going to be a long integer. So, we tend to think this is how
sql server works also...and it not the same.

In sql server 2005, if you choose unquieIdentifier to "replace" the
autonumber we used, you will find that you CAN NOT use a int, or bigInt for
the foreign key field in a child table.
Ok...got it???? Was that too hard for you?

You have to use a data type of
unquieIdentifier for this to work. This means you will have a field of type
unquieIdentifier in the child table, but that field will not unique at all.
It simply a source of COMMON confusing here. In sql server, we don't use
different data type definitions when building relationships, but in JET we
do (autonumber and long happen to be the same data size, but THEY HAVE
DIFFERENT
data type NAMES. In sql 2005, we simply don't use differnt data type (names)
as defined in the tables.

Once again: My point in case you missed it:

Often people moving to sql server confuse the idea of using the
UniqueIdenitfy type field to REPLACE the autonumber field we used for years
in ms-access. They are VERY different, and not to be interchanged.
Especially if the data is to be upsized from ms-access, or imported from a
access application, you will find it *very* difficult to keep and maintain
the child table
relationships if you use UniqueIdenitfy on the server side.

Now, was that too hard? Or do you need additional help from me, or the local
authorities?

If you wish further clarification on this, please feel free to ask, and I
will help you further understand this issue about sql server 2005....

 

[aaron.kempf]

re: **** HOWEVER while the above is correct, you CAN NOT use bigInt as
a primary
key!!!

are you kidding me?
I've used bigints for keys all the time.

thanks

and also-- this is incorrect
-------------------------------------------------
As someone recent pointed out, I don't see the need for a primary key
with a
range of:

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
-------------------------------------------------
the limit for 32 bit integer is 2.1 billion.. not 9 trillion like you
say.
and the funny thing is that you're actually talking about a
quintillion right?

808 = ones
775 = thousands
854 = milllions
036 = billions
372 = trillions
223 = quadrillion
9 = quintillion

There is a perfectly good reason to use bigint for keys..

a) some applications honestly need to generate 2.1 billion keys over
the lifetime of their application (40 year time frame?). Why skimp? I
believe that most ordinary Enterprise level SQL Server applications
are capable of a million transactions per day. I don't think that
it's out of the question to be able to plan for more than 3 years of
use. That kinda sounds like you're reccomending that we have a new
Y2K problem every 36 months ROFL.

I don't think that is too far out of the question (I have worked on
systems with 300m new records per day so I know it's easy to go _MUCH_
higher than that). If you're getting 300m new records per day; 2.1
billion sure doesn't last very long

b) It is possible to 'encode' other types of information into a single
(BIGINT) field and it can actually be more efficient that way (rather
than having 3 distinct fields, for example).

In other words

123456781234567820080318

or more importantly
200803181234567812345678

This would allow you to eliminate the need for a seperate date field-
in addition to a supplier ID and a customerID. int = 4 + 4 digit int
+ 4 digit int + 4 digit int.

ALL IN ONE SIMPLE BIG-INTEGER KEY.

Furthermore, there are plenty of situations where you would want to
use a GUID (Unique Identifier) as a key.
I believe that you want to do this if you're doing a lot with
replication (rather than have a seperate, secondary value for the
unique identifier and associated indexes; etc) Sometimes it would be a
lot cheaper to use a GUID Key than any other data type (because you're
required to have a unique identifier key anyways).

In most unique identifier situations; you can generate keys from 2
different servers and predict with 99.9999999% accuracy that you will
never have a duplicate key. (I believe that you could even get more
reliable if you were to take the steps of specifying a common MAC
address for each server's primary NIC; but that might be a little bit
out of scope of this discussion). I just know that the algorithm for
the uniqueidentifier will take into account the macAddress of the
machine; and I've seen probably half of the NICs allow to specify a
particular MAC address.. So it seems to me like there is some sort of
work around there

I have read an article.. they were talking about putting a distinct
serial number on _EVERY_PACK_ of cigarettes.. so that they could crack
down on teenage smoking (by keeping track of who purchased pack X of
cigarettes).

They found that they coudn't physically generate enough integer
identity fields (it was something like 10,000 integers per second
where they hit this limit) and so they _HAD_ to use guids for
performance reasons.

There wasn't a database system in the world that could generate 10,000
identity values every second... on a high end machine that was
dedicated to this activity-- it was just impossible to generate that
many integers at once.

I just wish that we all could provide more accurate information for
people interested in SQL Server.

I believe that you've mis-quoted 3 different things in a single thread
about SQL Server. I felt the need to illustrate the need for better
factual information about SQL Server on this newsgroup.

Thanks

-Aaron
MCITP: Database Administrator SQL Server 2005

 

[Rick Brandt]

re: **** HOWEVER while the above is correct, you CAN NOT use bigInt as
a primary
key!!!

are you kidding me?
I've used bigints for keys all the time.

thanks

and also-- this is incorrect
-------------------------------------------------
As someone recent pointed out, I don't see the need for a primary key
with a
range of:

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
-------------------------------------------------
the limit for 32 bit integer is 2.1 billion.. not 9 trillion like you
say.
and the funny thing is that you're actually talking about a
quintillion right?

SQL Server 2005 Books Online (September 2007)
int, bigint, smallint, and tinyint (Transact-SQL)
Updated: 14 April 2006

Exact-number data types that use integer data.

Data type Range Storage
bigint
-2^63 (-9,223,372,036,854,775,808) to 2^63-1
(9,223,372,036,854,775,807)
8 Bytes

int
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
4 Bytes

smallint
-2^15 (-32,768) to 2^15-1 (32,767)
2 Bytes

tinyint
0 to 255
1 Byte

 

[aaron.kempf]

EXACTLY MY POINT

he was saying that INT went up to 9 quintillion.

It's the 3rd thing that he's gotten wrong on this thread.

I just think that we should start having accurate information at all
levels.

-Aaron

 

[Albert D. Kallal]

EXACTLY MY POINT

he was saying that INT went up to 9 quintillion.

---------------------------

No, I was not, here is the text:

<quote>
So, my mistake here in at least not Realizing that bigInt CANNOT be the
primary key here (bigInts work fine in linked tables...but NOT as primary
key).

You have to use Int. (remember, on sql server, int is the same as our long
data type).

As someone recent pointed out, I don't see the need for a primary key with a
range of:

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

The above is not billion, but in fact has a trillion record range. There is
simply little, if any reason to use ***bigInt*** here....

</quote>

note how I highlighted the word ***bigInt*** text here. I was taking about
bigInt, and how there is LITTLE reason to use it. Got it????

I state again:

If you set a bigInt field as primary key, then you can't edit the linked
table in ms-access directly, it don't work. More interesting is that you CAN
link to tables with bigInts, just not as the primary key. (and, more
specific, you can actually *add* records when you link to the table *even*
when the PK is a bigInt, but you can't view them correctly..and this was
also verified/stated by the original poster).

The basic conclusion here is if you actually really do need bigInt as the
pk, then you're going to have trouble using ODBC linked tables from
ms-access to sql server.

 

[kagard]

Greetings:

FWIW, the problem was a data field that had 6/1/605 in it. When I set
this to 6/1/1900, I could update any record in the table. This is
weird to me at so many levels. How did this get in to Access in the
first place when an access datetime field can only hold values
starting at 1/1/1900, why did SQL Server import it, and why did it
lock the entire table for updating when 1 field in 1 record had a bad
value. I don't get it.

Thanks for your replies. I appreciate the time you took to answer my
question.

Keith

 

[Douglas J. Steele]

Dates in Access can be anything between 01 Jan 100 and 31 Dec 9999.

I'll agree, though, that it doesn't make sense in SQL Server, since the SQL
Server datetime data type is limited to dates between 01 Jan 1753 and 31
Dec, 9999, while the smalldatetime is limited to 01 Jan 1900 to 06 Jun 2079.

 

[Tony Toews [MVP]]

FWIW, the problem was a data field that had 6/1/605 in it. When I set
this to 6/1/1900, I could update any record in the table. This is
weird to me at so many levels. How did this get in to Access in the
first place when an access datetime field can only hold values
starting at 1/1/1900, why did SQL Server import it, and why did it
lock the entire table for updating when 1 field in 1 record had a bad
value. I don't get it.

1/1/1900 is the epoch date. As Doug states you can have dates back
to 1/1/100.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/