MS Access sequence numbers

MS Access sequence numbers

Post by chrisjle.. » Sun, 31 Dec 1899 09:00:00



Hi all,

I've got triggers on my Oracle database to increment sequence numbers
for my primary keys.  How can I get Access (2000) to read the next
sequence number before saving the record so that I can see it when I
open a new form??

Thanks,
Chris

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

MS Access sequence numbers

Post by Michael D. Lon » Sun, 31 Dec 1899 09:00:00


You aren't going to see the number that will be posted
to the database until the trigger fires.

As an alternative you could remove the sequence from
the trigger and call it from code and perform a manual
assignment when the record is made active. This will
result in the occasional skipped sequence, but it should
suffice if you truly need to display the number.

Mike


Quote:> Hi all,

> I've got triggers on my Oracle database to increment sequence numbers
> for my primary keys.  How can I get Access (2000) to read the next
> sequence number before saving the record so that I can see it when I
> open a new form??

> Thanks,
> Chris

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

MS Access sequence numbers

Post by bob sulliva » Sun, 31 Dec 1899 09:00:00


Um, Chris, did you mean to post this to an Oracle ng?

~bob


> I've got triggers on my Oracle database to increment sequence numbers
> for my primary keys.  How can I get Access (2000) to read the next
> sequence number before saving the record so that I can see it when I
> open a new form??

> Thanks,
> Chris

 
 
 

MS Access sequence numbers

Post by DNP » Sun, 31 Dec 1899 09:00:00


Why not fire a [Record Select] and then a [Record Save] command (from
VBA code) attached perhaps to the Forms!YourFormName.OnCurrent  event.

You could do these [<operations>] above  through methods on the Form's
Recordset Clone or you could do it through calling the commands by
referring to their GUI menu locations.

This will tell access to save the record as soon as you get to it.

This should be O.K. for either existing records or new records.

In the case of a new record, Access will then show the trigger generated
value because it is in effect looking at an already-saved record.

Now obviously this won't work if there are non null columns to be filled
out that don't have defaults set for them (either attached to the Form's
text box etc. controls or set through DDL on the server).

Because Access implements optimistic 'locking', it will trap a potential
lost update which could occur if someone else started committing changes
to this new 'shell' record whilst you are still working on it.

One downside to this is that if your client screens crashed, you might
never ultimately save the full info. for a record (leaving gaps in the
tables as a result).

This should be weighed up against the desire to let the user see the
sequence number (generated by Oracle) at the start of user-editing of
new records.

David P.

Oracle Certified DBA.

======================================================================

 
 
 

1. MS Access/ Oracle sequence number problem

I seem to have a problem using an Access front end to Oracle7 backend.

I am trying to retreive a sequence number from Oracle, store it as a
variable and update a record back to the database incorporating this
sequence number into the 'Quote_number' as my key in my Oracle table.

The SQL statement in Oracle looks like:

SELECT quote_sq.nextval from DUAL

Apparently, DUAL is a virtual table created by ORACLE to hold information
like the sequence number I need

I have to use the Oracle sequence number and not the MS Access counter

If you have any ideas, please mail me


Thanking you all very much in advance

Gordon Elliott
Societe Generale Australia LTD

2. NLS, order by and indexes

3. How to create sequence number in MS SQL?

4. default privs for public

5. Function sequence error accessing MS Access db over jdbc

6. pgsql-server/src/bin/psql describe.c

7. record number/sequence number in MSAccess table

8. Illinois - Oracle Developer

9. MS Access sql error [Function sequence error]

10. Sequences and MS-Access

11. using oracle sequence by odbc in MS ACCESS

12. CA-MILPITAS-111217--Management-MS Office-MS Word-MS Excel-MS ACCESS-ORACLE-GROUP CONTROLLER