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=DOHSecurityIDCardD ata;TABLE=dbo.tblIDCards

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

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

hi,

kagard@gmail.com wrote:
> 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 <--

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<kagard@gmail.com> wrote in message
news:75fa7197-66ec-4cbf-a288-814ee1c19a4a@e60g2000hsh.googlegroups.com...
> 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=DOHSecurityIDCardD ata;TABLE=dbo.tblIDCards
>
> Primary key added
> CARD_SEQ_ID
> Is Identity = Yes
> Identity Increment = 1
> Identity Seed = 1

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

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

On Mar 18, 11:47*am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> When you added the identity field, did you remember to create an index and
> mark it as Unique?

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

On Mar 18, 12:09*pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal...@msn.com> wrote:
> 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).
>
> 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.

Hi stefan:

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

Keith

On Mar 18, 11:45*am, Stefan Hoffmann <stefan.hoffm...@explido.de>
wrote:
> hi,
>
> kag...@gmail.com wrote:
> > 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 <--

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

> 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?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com