Autonumber on linked SQL table

[Eric_LA]

Hi,

I have an Access front-end linked to a SQL table. I recently made a change
to the table by adding a column. On the back-end, I copied the original
table, let's call it tblA, to tblB. I added new columns to tblB. I then
changed the table names: tblA to tblA_old and tblB to tblA. (Perhaps not the
best way to handle the change - but that's how I did it.)

This table has an integer for a primary key, defined as autonumber on the
Access front-end. Now, when I try to add records to the table using the
front-end (either through a form or entering it in the table itself), as soon
as the record gets dirty, the key field is cleared. I cannot enter a number
manually and I cannot save the record because the primary key field is now
null.

The exact error is "ODBC call failed ... Cannot insert the value NULL into
column 'pk_ID' ... (#3621)"

Is the autonumber generator somehow out of synch with this new table (but
with the original name)? How can I resolve this?

Thanks for any help.

Eric

 

[Sylvain Lafontaine]

Everytime you change the structure of a backend table, you should refresh
the link using the Linked Tables Manager (or by deleting/recreating it).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Eric_LA]

Thanks for the tip - I should've thought of that - but it did not do the trick.

 

[John W. Vinson]

you should refresh
the link using the Linked Tables Manager (or by deleting/recreating it).

IME Linked Tables Manager will often fail (leave the table in a mixed-up state
that causes all sorts of odd problems). Just delete the link and relink, in my
experience.

This also gives you the ability to specify which field is the primary key,
essential for this kind of problem.

 

[Eric_LA]

I tried that too - no luck. Since the PK is defined on the SQL table, that
carries over into Access so there's no prompting for that when the table is
re-linked.

:-/

 

[James A. Fortune]

Hi,

I have an Access front-end linked to a SQL table. I recently made a change
to the table by adding a column. On the back-end, I copied the original
table, let's call it tblA, to tblB. I added new columns to tblB. I then
changed the table names: tblA to tblA_old and tblB to tblA. (Perhaps not the
best way to handle the change - but that's how I did it.)

This table has an integer for a primary key, defined as autonumber on the
Access front-end. Now, when I try to add records to the table using the
front-end (either through a form or entering it in the table itself), as soon
as the record gets dirty, the key field is cleared. I cannot enter a number
manually and I cannot save the record because the primary key field is now
null.

The exact error is "ODBC call failed ... Cannot insert the value NULL into
column 'pk_ID' ... (#3621)"

Is the autonumber generator somehow out of synch with this new table (but
with the original name)? How can I resolve this?

Thanks for any help.

Eric

I suggest creating an additional field in the SQL Server table to use as
the SQL Server primary key. Leave the Access primary key field in as an
AutoNumber and use it as normal, ignoring the new field. My convention
is simply to append 'SS' to the name of the Access primary key when
creating the new field. Hopefully, that will allow you to make the
change. I don't understand how your key field is getting cleared.

James A. Fortune
(e-mail address removed)