Access 97 + Linked table Oracle + Autonumber

[nryme]

Hello,

I'm making a MS Access frontend for some tables on the Oracle 8 database at work. The tables are linked ofcourse.

One table has an AUTONUMBER field on the Oracle and it seems to give me trouble to insert new records.
When I try to insert a new record (leave the autonumber field blank) I get the following error: "ODBC — insert on a linked table <table> failed. (Error 3155)" followed by the error "[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01722: invalid number (#1722)".

When I look at the Oracle documentation I got this:

ORA-01722 invalid number
Cause: The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that
they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

I checked the INSERT statement: "INSERT INTO AFM_HV_PROP_VALUE (HV_INST_ID, HV_PROPERTY_ID, TABLE_NAME, HV_PROPERTY_VALUE) VALUES (4, 'V_TESTJE', 'hv_inst', '123465')" and everything seems to be allright. The value that's causing the error is the "4" that gets in the "HV_INST_ID"-field.
Using TOAD to execute the SQL Statement, there is no problem at all.

When I look at the table design in MS Access I see that the Autonumber field is of the type "Double". That doesn't seem right to me...

Anyone some suggestions? I'm running out of courage :s

Greetings,
Niels R.

 

[Pat Hartman]

I haven't used Oracle in some years and I don't have it installed so I can't work out the answer but I recall having similar problems when my Oracle database was first created. Talk to the Oracle DBA and explain the problem. If he is worth his salt, he will know immediately what the problem is. I'm sure that some change to the table definition for the autonumber field is required. You could also try posting in an Oracle forum if no one here provides a more definitive solution.