Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Question Unanswered: Using an oracle sequence in access

    Hey I hope someone can help me. I've just linked my oracle tables to access and I want to use the forms in access to add data into my oracle tables.
    So, now I've got my form up and running, I want to use the sequence (which I created in oracle) in the form for the customer ID. I've tried a couple of things but I'm getting nowhere. Please Help me!

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    sequence

    is "sequence" the ORDER how you want to see customers or is it OrderID you want to assign to each customer?

    I guess it is the ORDER. You can create QUERY based on your lined table and then use the query in your form.... you can also create PASS THROUGH query and not use linked tables at all.... PASS THROUGH query is a special query, it allows you to send SQL directly to any server - there is no syntax check, but it is very fast. When you edit the query, don't forget to right clcik it and in properties to add connection string even with password :-( I would not call PASS THROUGH query like that, I'd call it STORED PROCEDURE.



    jiri

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jiri, sequences are what oracle uses instead of autonumbers or identity fields

    e.g.
    insert into footable (foo_id, foo)
    select mysequence.nextval, 'bar' from dual;

    an oracle sequence is a special register that can be used across multiple tables

    mthould, sorry, i don't do access forms, if they generate INSERT syntax then you would just code the NEXTVAL into them as above, i guess

    rudy

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    hmmm

    I'm sorry, I'm MS SQL person. IDENTITY is IDENTITY. That's clear. MS SQL uses @@IDENTITY, I guess in ORACLE you have to use seq_name.currval or seq_name.nextval see http://www.llcsystems.com/FAQ/oracle...cle_SQL_14.htm for more details.... simply use ADO and ask ORACLE server before or after you save the form.....

    Do not BOUND forms to your linked table, create INSERTToMyTable Stored procedure, it is much better - you can use triggers and check constr. ....


    jiri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •