Auto Number on a form

[CherylStraw]

I have created a form using two tables that are linked from our Oracle Database. There is a field on a table called CALLS that is the CALL ID, I would like this to be an autonumber field but this isn't an option in Oracle (or at least that's what I have been told). Is there some way to make this field automatically populate with a number? Please let me know if you need more details to answer my question correctly. I don't have much experience using Access and even less using Oracle so thanks for your patience!

 

[aqif]

Hi

If u r creating a new table in Access then u can easily set the field datatype to "Autonumber". If u r linking ur form to Oracle database table and then u have to write a procedure for picking the maximum ID and then incrementing that. If u have experience with database triggers in Oracle than u can easily write this code in On Insert event of table. Please tell me what do u actually want and send me the name of the field and table on which u want autonumber.

Cheers!
Aqif

 

[CherylStraw]

Hi aqif,

Thanks for responding to my question. Here is what I am trying to do: I have created a table in Oracle that is linked to an Access database. The table is called Calls. The first field in this table is Call ID. This is the field I would like as the autonumber. I don't have any idea how to write a "trigger" in Oracle to accomplish this. Please let me know if I left any details out.

Thanks again for your reply!

Cheryl

 

[Pat Hartman]

I would go back to the person who told you that autonumbers were not available in Oracle to look again. This time look up Sequence. If it was your Oracle DBA who told you this, you're in for a tough time because he's going to need to do some work for you and he may not know how to do it.

Two things need to be done to make this work well for you from Access.

1. He needs to create a sequence
2. He needs to create an insert trigger that gets the NextVal for this sequence number and inserts it as the primary key field.

In your Access append query, you would omit the sequence field since the trigger will add it just before Oracle inserts the new row.

Technically the insert trigger is not required (and the DBA may argue about it) but without it, you will not be able to open a linked table in datasheet view and insert a row. You would only be able to insert via a pass-through query where you could refer to the NextVal function using Oracle SQL syntax.

 

[CherylStraw]

Pat,

Thanks for the info. I will be taking it to our DBA to see if he can help me. Thanks again for your input.


Cheryl