Solved

Using Sybase Indentity fields with MS-Access

Posted on 1997-08-17
10
263 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now