Using Sybase Indentity fields with MS-Access

I am having trouble with using ODBC to insert into tables with Identity fields in Sybase.

First off, I need an identity field because must have a unique key in an ODBC table to be able to do updates. A combination of all fields in the tableis unique but not desirable as an index because it includes 2 long
VARCHAR fields. All fields can be (and often are) null.

Behaviour:
  If the identity field is the primary index, then Access 97 (the client) wont allow the insert. (The identity field is mapped as a Required and so wont permit the insert without a value for that field).

  If the identity field is a simple unique index, then the ODBC driver complains "multiple rows with same value for index  ID" (or similar) when an insert is attempted. Ie it thinks that duplicate record is being inserted.

The problem seems to be firmly in the ODBC driver rather than Sybase or Access. If I use Borland's SQL explorer with native CT-lib links to the database, I can insert without problems (and likewise with WISQL). Using SQL-explorer but via ODBC instead of native links, I get same error.

The ODBC driver is Intersolv 10.0.4 v2.12 from the Sybase Openclient installation. Sybase Server is System 11 on NT 3.51.

Does anyone know of a workaround here, or better still of an update to the ODBC driver? From looking at the Sybase site, there appears to be system 11 ODBC 3.0 driver
pscaddenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozphilCommented:
The field must be a Primary key in the back end database for the update to work with this ODBC.

The table in the Back end must have had its Primary Key established by the DBA.

0
pscaddenAuthor Commented:
As indicated in problem statement, I have tried various
combinations on indexes (all in backend database - you
cant create a local index on an ODBC table). If the ID is
Primary key in Sybase, then Access wont let you enter a
record because ID is null (IDENTITY fields are autoincrement
fields in Sybase) . Sybase wont let you add a value to ID
because it is identity field (and what is point if you were setting
value yourself). If you create unique key other than primary
key in Sybase, then it complains about a duplicate when you
try to insert.  The guts of this problem is about ODBC and
Identity (server-autoincrement) fields.
0
ozphilCommented:
So so far everything youve done complies with:
http://www.microsoft.com/kb/articles/q90/1/00.htm

?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pscaddenAuthor Commented:
Yes, I have 140 tables on the Sybase servers. The only
ones causing insert problems are the ones with IDENTITY
fields used to create unique indexes. The failure on insert in
an ODBC call failed error. It reports the duplicate record for
index primarykey on field ID during attempted insert. An
SQL passthrough queury will insert happily enough but to
use this will take a massive re-write. The other approach
that works is to abandon the IDENTITY type field and replace
with it ordinary numeric field, and index that. This means ACCESS must supplied the unique value for it on update. This
is east for one user but get very complicated for multiple users.
If no other workaround is possible, then may have to do this.
0
ozphilCommented:
Then why not use a capable ODBC driver
eg http://www.simba.com/products/sybase.html
or later Intersplve version. As you say, that does appear to be an area that should be tried to avoid a lot of rework.
0
pscaddenAuthor Commented:
That is another possibility but I am not ordering a new ODBC
driver unless I know it will work. Autoincrement fields are
rare on Servers - usually you use triggers. Is any other driver
going to be better? What I want is someone working with
System 11 and ODBC that can tell me what will work.
0
ozphilCommented:
Simba has downloadable evaluations at

http://www.simba.com/demos/demos.html

'... full-featured, time-limited evaluation copies of SimbaOracle, SimbaSybase drivers, and SimbaExpress, ...'
0
CarmyCommented:
Intersolv Version 3 have been available for the last 4 month at least, some of the new features are:
Sybase
        * Long data enhancements
        * System 11 client support
        * Quoted identifier support
        * Enhanced multi-threading support
        * Many new configuration options
        * 4.9.2 support
0
pscaddenAuthor Commented:
We have got 10.0.4 drivers from Sybase (intersolv) and
yeah! problems go away.
0
ozphilCommented:
Thats good.

Id appreciate your grading of this answer then pscadden.

Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.