Linked Table, cannot copy/paste new records

[paul]

I have an Access database with linked tables from Sybase SQL Anywhere,
via an ODBC data source. I have just linked the tables recently, all
the data used to be in native Access tables. It is fairly common for
members of our team to open these tables in datasheet view, and
copy/paste several records to create new records, then edit a few
fields on the new records. This used to work fine with native Access
tables, but when we try it with the linked tables we either get an ODBC
error - "primary key value already exists" or the new records show up
with #Deleted.

The root of the problem is this: In the old Access tables, the primary
key was an autonumber field, and Access was smart enough to assign new
ID's when you copy/pasted records. In the linked table, the primary key
is type "Number" in Access, and Access is not smart enough to let
Sybase assign new ID's when you copy/paste records in datasheet view.
Access is trying to force the existing ID's into the primary key field,
and Sybase says "too bad so sad".

Of course, I could just write some quick append queries to copy/paste
the data. And in the short term, that's exactly what I will have to do.
But is there any way, long term, to allow members of my team to do it
the "quick & dirty" way by copy/pasting in datasheet view? I have tried
to change the primary key to an autonumber field in design view, but
Access doesn't allow that. Is there a way to do it in code, or a way to
force Access to allow Sybase to always handle the primary key field?

 

[John Vinson]

But is there any way, long term, to allow members of my team to do it
the "quick & dirty" way by copy/pasting in datasheet view?

Nope. This is one of the many, many limitations of datasheet view,
and one reason that most developers avoid making table datasheets
available to users.

You can programmatically increment a custom-counter ("roll your own"
autonumber) using a Form, but tables have no usable events.

John W. Vinson[MVP]