Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Link Oracle sequence to MS Access application

shirinshirin Posts: 28Member
I don't know how to do this. From the "table" object on the application, I clicked on "new", "link table", "OK". Then clicked on "files of type" = "ODBC Databases".

select data source = "machine data source"
clicked on correct Data Source Name (OK)
logged in

Sequence is not on list, although all tables in selected schema are present.

Are sequences linked differently from tables?

Thanks.

Shirin


Comments

  • infidelinfidel Posts: 2,900Member
    [b][red]This message was edited by infidel at 2003-12-2 12:33:38[/red][/b][hr]
    : I don't know how to do this. From the "table" object on the application, I clicked on "new", "link table", "OK". Then clicked on "files of type" = "ODBC Databases".
    :
    : select data source = "machine data source"
    : clicked on correct Data Source Name (OK)
    : logged in
    :
    : Sequence is not on list, although all tables in selected schema are present.
    :
    : Are sequences linked differently from tables?

    Sequences are not tables. And they are pretty much Oracle-specific. Access has "autonumber", Informix has "serial", Oracle has sequences. I'm not familiar with other databases, but there isn't any standard.

    I guess the question to ask here is "what are you trying to do?"

    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]



  • infidelinfidel Posts: 2,900Member
    Incidentally, there [italic]is[/italic] as way to create your own "sequence" functionality using a table and a stored function if you are searching for a way to view your next values in a table.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • shirinshirin Posts: 28Member
    : [b][red]This message was edited by infidel at 2003-12-2 12:33:38[/red][/b][hr]
    : : I don't know how to do this. From the "table" object on the application, I clicked on "new", "link table", "OK". Then clicked on "files of type" = "ODBC Databases".
    : :
    : : select data source = "machine data source"
    : : clicked on correct Data Source Name (OK)
    : : logged in
    : :
    : : Sequence is not on list, although all tables in selected schema are present.
    : :
    : : Are sequences linked differently from tables?
    :
    : Sequences are not tables. And they are pretty much Oracle-specific. Access has "autonumber", Informix has "serial", Oracle has sequences. I'm not familiar with other databases, but there isn't any standard.
    :
    : I guess the question to ask here is "what are you trying to do?"
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    :
    :
    I'm trying to access and increment (*.nextval) an Oracle sequence within MS Access, without running a batch job. If I ran a PL/SQL procedure in batch, then the sequence number would be available. However, the application has no batch jobs, only inline SQL code.
  • infidelinfidel Posts: 2,900Member
    : I'm trying to access and increment (*.nextval) an Oracle sequence within MS Access, without running a batch job. If I ran a PL/SQL procedure in batch, then the sequence number would be available. However, the application has no batch jobs, only inline SQL code.

    The only way to increment a sequence is to select nextval from it or drop it and recreate it with the new starting value.

    When you say "the application has no batch jobs", do you mean there are no stored procedures? Can you create stored procedures?

    You can get a list of sequences from the data dictionary views:

    select * from all_sequences


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • shirinshirin Posts: 28Member
    : : I'm trying to access and increment (*.nextval) an Oracle sequence within MS Access, without running a batch job. If I ran a PL/SQL procedure in batch, then the sequence number would be available. However, the application has no batch jobs, only inline SQL code.
    :
    : The only way to increment a sequence is to select nextval from it or drop it and recreate it with the new starting value.
    :
    : When you say "the application has no batch jobs", do you mean there are no stored procedures? Can you create stored procedures?
    :
    : You can get a list of sequences from the data dictionary views:
    :
    : select * from all_sequences
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    I see the light! I was previously thinking only of batch jobs (which we aren't going to use) vs. in-line code, but forgot that I can initiate a procedure independently of the application (and not as a batch job), then return control and valus back to the application upon completion.


  • infidelinfidel Posts: 2,900Member
    : I see the light! I was previously thinking only of batch jobs (which we aren't going to use) vs. in-line code, but forgot that I can initiate a procedure independently of the application (and not as a batch job), then return control and valus back to the application upon completion.

    You should be using stored procedure for all of your database access anyways. Jonathan will be happy to use sql-injection attacks on your system if you would like to see why.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • niko66623niko66623 Posts: 2Member
    im having same problem
    its a link table made from oracle linked to MS Access form
    i have a MIS_WO for work order number
    i want it to be autonumber from a Sequence Made from Oracle

    i dont know the code on how to run it
    any help is most appriciated
Sign In or Register to comment.