| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: MS Access using Oracle ODBC
I'm able to get this working with MS Access 97 and Oracle8. I
executed the following DDL:
CREATE TABLE foo (id NUMBER, num NUMBER);
CREATE SEQUENCE foo_id_seq;
CREATE OR REPLACE TRIGGER foo_iu BEFORE INSERT ON foo FOR EACH ROW
DECLARE
id NUMBER;
BEGIN
SELECT foo_id_seq.NEXTVAL INTO id FROM DUAL;
:new.id := id;
END;
That's all there is to it. Now, if I perform the following from
SQL*Plus:
INSERT INTO foo (num) VALUES(2000);
COMMIT;
The ID column will be automatically populated with the next value from
the sequence.
If I attach this table using MS Access, and I add rows to this table using the DataSheet view and only populate the NUM column, the ID column will automatically be populated with the sequence values.
Hope this helps.
On Thu, 30 Apr 1998 14:49:36 -0600, jjackson_at_mail.capnet.state.tx.us wrote:
>I am modifying an Access application to use attached Oracle tables instead of
>Access tables for better performance across a WAN. I can attach and use the
>Oracle tables, but I need to know how to reference Oracle sequences from
>Access. I need to generate unique IDs like I did with the Counter field in
>Access. I know how to create and use sequences in Oracle, but cannot
>reference a sequence from Access to generate the number. The Oracle
>documentation gives an example of an Insert SQL statement referencing
>sequence_name.nextval. Does this apply to PL/SQL only, or can you do this
>with standard SQL. I have tried this from Access, but it does not work. This
>application will be rewritten, but I need to use the existing Access app with
>Oracle tables as a quick fix. Please email jjackson_at_mail.capnet.state.tx.us
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |