Solved

Using Sybase Indentity fields with MS-Access

Posted on 1997-08-17
10
265 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

770 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