Solved

Using Sybase Indentity fields with MS-Access

Posted on 1997-08-17
10
268 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
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)

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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