Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Sybase Indentity fields with MS-Access

Posted on 1997-08-17
10
Medium Priority
?
272 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:pscadden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:ozphil
ID: 1955699
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
 

Author Comment

by:pscadden
ID: 1955700
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
 
LVL 4

Expert Comment

by:ozphil
ID: 1955701
So so far everything youve done complies with:
http://www.microsoft.com/kb/articles/q90/1/00.htm

?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pscadden
ID: 1955702
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
 
LVL 4

Expert Comment

by:ozphil
ID: 1955703
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
 

Author Comment

by:pscadden
ID: 1955704
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
 
LVL 4

Expert Comment

by:ozphil
ID: 1955705
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
 
LVL 1

Expert Comment

by:Carmy
ID: 1955706
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
 

Author Comment

by:pscadden
ID: 1955707
We have got 10.0.4 drivers from Sybase (intersolv) and
yeah! problems go away.
0
 
LVL 4

Accepted Solution

by:
ozphil earned 400 total points
ID: 1955708
Thats good.

Id appreciate your grading of this answer then pscadden.

Thanks.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question