Access frontal interface for Oracle database

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Gloops

    Access frontal interface for Oracle database

    Hello everybody,

    Is anyone able to give me some indications about how to develop an
    Access interface for an Oracle database ?

    I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and
    ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.

    I failed executing an Oracle stored procedure from Access, and a trigger
    to store data to a temporary table was active from SQL*Plus, but
    not from Access.

    So, my access base can connect one user, not a hundred as requested.
    Anybody ?
  • gym dot scuba dot kennedy at gmail

    #2
    Re: Access frontal interface for Oracle database


    "Gloops" <gloops@invalid .zailes.orgwrot e in message
    news:g7l3k5$fi8 $1@aioe.org...
    Hello everybody,
    >
    Is anyone able to give me some indications about how to develop an Access
    interface for an Oracle database ?
    >
    I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and ODBC
    3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.
    >
    I failed executing an Oracle stored procedure from Access, and a trigger
    to store data to a temporary table was active from SQL*Plus, but not from
    Access.
    >
    So, my access base can connect one user, not a hundred as requested.
    Anybody ?
    Don't use temporary tables. Oracle!=MS Sql Server
    You would probably have to use a pass through query to do a stored proc.


    Comment

    • Gloops

      #3
      Re: Access frontal interface for Oracle database

      gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 03:15 :
      >I failed executing an Oracle stored procedure from Access
      You would probably have to use a pass through query to do a stored proc.
      Well, would you mind giving a few details ?


      Comment

      • gym dot scuba dot kennedy at gmail

        #4
        Re: Access frontal interface for Oracle database


        "Gloops" <gloops@invalid .zailes.orgwrot e in message
        news:g7mah7$9m0 $1@aioe.org...
        gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 03:15 :
        >I failed executing an Oracle stored procedure from Access
        You would probably have to use a pass through query to do a stored proc.
        Well, would you mind giving a few details ?
        I haven't used access in many years. This is my best guess. Did you do a
        search for an example similar to the one you are trying to do?
        Jim



        Comment

        • Gloops

          #5
          Re: Access frontal interface for Oracle database

          gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 15:34 :
          >Well, would you mind giving a few details ?
          I haven't used access in many years. This is my best guess. Did you do a
          search for an example similar to the one you are trying to do?

          Of course I did, I should very pleased to learn that your search appears
          more efficient ...

          The Execute method (of ADODB ? not sure I remember, I have a look
          tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
          not EXECUTE, I could not execute a stored procedure in Oracle that way.

          I searched "Access Oracle ODBC", do you see a better search ?
          I have to admit I only had a look to the ten first anwsers.

          Comment

          • sybrandb@hccnet.nl

            #6
            Re: Access frontal interface for Oracle database

            On Sun, 10 Aug 2008 16:06:41 +0200, Gloops <gloops@invalid .zailes.org>
            wrote:
            >The Execute method (of ADODB ? not sure I remember, I have a look
            >tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
            >not EXECUTE, I could not execute a stored procedure in Oracle that way.
            It could in the past and I don't see why it can't now.
            As 'execute' is a sql*plus keyword, and not a SQL keyword,
            the syntax is { BEGIN <procedure>; END; }

            (from memory)

            --
            Sybrand Bakker
            Senior Oracle DBA

            Comment

            • Timmy!

              #7
              Re: Access frontal interface for Oracle database

              On Aug 9, 7:53 pm, Gloops <glo...@invalid .zailes.orgwrot e:
              Hello everybody,
              >
              Is anyone able to give me some indications about how to develop an
              Access interface for an Oracle database ?
              >
              I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and
              ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.
              >
              I failed executing an Oracle stored procedure from Access, and a trigger
              to store data to a temporary table was active from SQL*Plus, but
              not from Access.
              >
              So, my access base can connect one user, not a hundred as requested.
              Anybody ?
              Salut, Gloops.

              I don't do this (running stored procs) very often not because one
              can't but because I have little experience in PL/SQL. However, you
              should be able to do this. If I can execute DDL, I'm sure stored
              procs won't be a problem.

              You need a pass through query, though you've probably figured that out
              already if you've tried and failed to execute.

              Take the syntax that you type into SQLPlus to execute the query and
              copy it into the PTQ query SQL View.

              If the Oracle user that is being used for your ODBC connection string
              is not the (Oracle guys may need to help me out, I'm not 100% sure of
              the terminology) main user, ie, the user that makes up the DSN you are
              using was made under another "main" (again, bad term, sorry) user, you
              are going to need to prefix the proc name with the main user and a
              dot.

              Here's an example using a create view statement (not Pl/sql), I know,
              but hopefully you will see what I am getting at:

              Main user (again apologies the Oracle folks): Timmy
              User with specific privileges created under user Timmy: Apples

              Apples is the user in the DSN.

              Now, the following will run in SqlPlus when logged in as Timmy:

              Create or Replace view v_my_view as
              SELECT Fruit_type, Fruit_name, Fruit_locations
              FROM Fruits
              WHERE Fruit_type = 'Apple'

              If you were to run this in the Access environment as a PTQ using the
              DSN that uses the user name Apples, the syntax you need in Access is:

              Create or Replace view v_my_view as
              SELECT Fruit_type, Fruit_name, Fruit_locations
              FROM Timmy.Fruits
              WHERE Fruit_type = 'Apple'

              Note the from clause. You would need to do something similar to your
              stored proc.

              If you are trying to do this via VBA code, let me know. There is one
              small trick to avoid an error message when VBA runs a PTQ that is a
              stored proc or update/delete/insert SQL.

              Hopefully some of the cdo flks will correct some of my poor
              terminology above.
              --
              Tim http://www.ucs.mun.ca/~tmarshal/
              ^o<
              /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
              /^^ "What's UP, Dittoooooo?" - Ditto

              Comment

              • Gloops

                #8
                Re: Access frontal interface for Oracle database

                sybrandb@hccnet .nl a écrit, le 11/08/2008 00:18 :
                On Sun, 10 Aug 2008 16:06:41 +0200, Gloops <gloops@invalid .zailes.org>
                wrote:
                >The Execute method (of ADODB ? not sure I remember, I have a look
                >tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
                >not EXECUTE, I could not execute a stored procedure in Oracle that way.
                It could in the past and I don't see why it can't now.
                As 'execute' is a sql*plus keyword, and not a SQL keyword,
                the syntax is { BEGIN <procedure>; END; }

                (from memory)
                OK thanks, I shall try BEGIN, although the ODBC error message box
                answered me that the only recocknized keywords were INSERT, DELETE and
                UPDATE (and perhaps one more, that was on Friday).

                I hope you do not mix up, as BEGIN is a key word that begins the code of
                an Oracle procedure.

                In SQL*Plus, I launch the stored procedure by typing EXECUTE followed by
                the name of the procedure -this is why I tried this from Access.

                So, I am going to type BEGIN instead, that is not expansive after all :)
                I tell you that tonight.

                Comment

                • Gloops

                  #9
                  Re: Access frontal interface for Oracle database

                  Well, here is some feedback.

                  I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
                  MAJSES", either by ADODB.Execute, or by
                  Workspace.Conne ction.QueryDef. Execute

                  If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
                  or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
                  INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
                  "PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
                  clause, and same thing if I try to save the record to define parameters
                  from a function.

                  I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
                  fact, the code of MAJSES), but after that the TMPCLIENTS table was still
                  empty.

                  A session is opened by the sub to execute a query, and closed before the
                  end of the sub. I fear that the temporary table is proper to each
                  session that is opened like that, so, if ten sessions are opened that
                  way, ten copies of the same table are created with no way to see data
                  from one to the other.
                  So, I presume that a session has to be opened public in a module when
                  opening the database, to be closed only when closing the database, and
                  all subs that have to operate on the base must refer to that session
                  object, but I do not have enough time to test the different syntaxes and
                  objects to implement this, so this will be for my next mission on
                  Oracle. I tried with other newsgroups before, I discovered these two
                  ones by browsing a book last Saturday once I could somewhat situate the
                  question.

                  One special point of attention is how to obtain that the session object
                  in the code refers to the same session as the user interface.

                  It would be tempting to launch OraDatabase.Exe cute "PROCEDURE MAJSES" or
                  "EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
                  OraDatabase.Exe cute "BEGIN MAJSES", ORA-06550 SQL execution error, line
                  1 column 12, PLS-00103, end of file symbol met in place of one of the
                  following symbols :
                  := . ( @ % ;

                  In the same time, if you see how to correct the syntax, it could be
                  interesting to do some tests about it, even if I have no time to use it
                  for the final results. I see some examples in the help of
                  OraParamArrays, but as I do not exactly situate how much time I need for
                  this, I have to start up.

                  In the meanwhile, I could create temporary tables in Access. Of course,
                  like that, the Access interface will become very fat, but at least
                  several people can access at the same time.

                  It was a very good idea to tell me I do not need temporary tables.
                  Please feel free to propose a way to do like that, from Access.

                  Comment

                  • Gloops

                    #10
                    Re: Access frontal interface for Oracle database

                    gazzag a écrit, le 11/08/2008 12:21 :
                    Well thank you, now that I could establish concurrency thanks to
                    temporary tables inside the Access interface, I think this can help
                    ensure consistency.

                    Supposing I can cope with the treatments I have to finish, I presume a
                    good moment to read this can be on next Friday.

                    Comment

                    • Shakespeare

                      #11
                      Re: Access frontal interface for Oracle database


                      "Gloops" <gloops@invalid .zailes.orgschr eef in bericht
                      news:g7q0ad$hpl $1@aioe.org...
                      Well, here is some feedback.
                      >
                      I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN MAJSES",
                      either by ADODB.Execute, or by Workspace.Conne ction.QueryDef. Execute
                      >
                      If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
                      or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE, INSERT,
                      PROCEDURE, SELECT or UPDATE awaited". If I try and launch "PROCEDURE
                      MAJSES" I am blamed for a syntax error in the PARAMETERS clause, and same
                      thing if I try to save the record to define parameters from a function.
                      >
                      I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
                      fact, the code of MAJSES), but after that the TMPCLIENTS table was still
                      empty.
                      >
                      A session is opened by the sub to execute a query, and closed before the
                      end of the sub. I fear that the temporary table is proper to each session
                      that is opened like that, so, if ten sessions are opened that way, ten
                      copies of the same table are created with no way to see data from one to
                      the other.
                      So, I presume that a session has to be opened public in a module when
                      opening the database, to be closed only when closing the database, and all
                      subs that have to operate on the base must refer to that session object,
                      but I do not have enough time to test the different syntaxes and objects
                      to implement this, so this will be for my next mission on Oracle. I tried
                      with other newsgroups before, I discovered these two ones by browsing a
                      book last Saturday once I could somewhat situate the question.
                      >
                      One special point of attention is how to obtain that the session object in
                      the code refers to the same session as the user interface.
                      >
                      It would be tempting to launch OraDatabase.Exe cute "PROCEDURE MAJSES" or
                      "EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
                      OraDatabase.Exe cute "BEGIN MAJSES", ORA-06550 SQL execution error, line 1
                      column 12, PLS-00103, end of file symbol met in place of one of the
                      following symbols :
                      := . ( @ % ;
                      >
                      In the same time, if you see how to correct the syntax, it could be
                      interesting to do some tests about it, even if I have no time to use it
                      for the final results. I see some examples in the help of OraParamArrays,
                      but as I do not exactly situate how much time I need for this, I have to
                      start up.
                      >
                      In the meanwhile, I could create temporary tables in Access. Of course,
                      like that, the Access interface will become very fat, but at least several
                      people can access at the same time.
                      >
                      It was a very good idea to tell me I do not need temporary tables. Please
                      feel free to propose a way to do like that, from Access.
                      It should be
                      "BEGIN MAJSES ; END ;"

                      Shakespeare


                      Comment

                      • gym dot scuba dot kennedy at gmail

                        #12
                        Re: Access frontal interface for Oracle database


                        "Shakespear e" <whatsin@xs4all .nlwrote in message
                        news:48a0876f$0 $184$e4fe514c@n ews.xs4all.nl.. .
                        >
                        "Gloops" <gloops@invalid .zailes.orgschr eef in bericht
                        news:g7q0ad$hpl $1@aioe.org...
                        >Well, here is some feedback.
                        >>
                        >I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
                        >MAJSES", either by ADODB.Execute, or by
                        >Workspace.Conn ection.QueryDef .Execute
                        >>
                        >If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
                        >or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
                        >INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
                        >"PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
                        >clause, and same thing if I try to save the record to define parameters
                        >from a function.
                        >>
                        >I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
                        >fact, the code of MAJSES), but after that the TMPCLIENTS table was still
                        >empty.
                        >>
                        >A session is opened by the sub to execute a query, and closed before the
                        >end of the sub. I fear that the temporary table is proper to each session
                        >that is opened like that, so, if ten sessions are opened that way, ten
                        >copies of the same table are created with no way to see data from one to
                        >the other.
                        >So, I presume that a session has to be opened public in a module when
                        >opening the database, to be closed only when closing the database, and
                        >all subs that have to operate on the base must refer to that session
                        >object, but I do not have enough time to test the different syntaxes and
                        >objects to implement this, so this will be for my next mission on Oracle.
                        >I tried with other newsgroups before, I discovered these two ones by
                        >browsing a book last Saturday once I could somewhat situate the question.
                        >>
                        >One special point of attention is how to obtain that the session object
                        >in the code refers to the same session as the user interface.
                        >>
                        >It would be tempting to launch OraDatabase.Exe cute "PROCEDURE MAJSES" or
                        >"EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
                        >OraDatabase.Ex ecute "BEGIN MAJSES", ORA-06550 SQL execution error, line 1
                        >column 12, PLS-00103, end of file symbol met in place of one of the
                        >following symbols :
                        >:= . ( @ % ;
                        >>
                        >In the same time, if you see how to correct the syntax, it could be
                        >interesting to do some tests about it, even if I have no time to use it
                        >for the final results. I see some examples in the help of OraParamArrays,
                        >but as I do not exactly situate how much time I need for this, I have to
                        >start up.
                        >>
                        >In the meanwhile, I could create temporary tables in Access. Of course,
                        >like that, the Access interface will become very fat, but at least
                        >several people can access at the same time.
                        >>
                        >It was a very good idea to tell me I do not need temporary tables. Please
                        >feel free to propose a way to do like that, from Access.
                        >
                        It should be
                        "BEGIN MAJSES ; END ;"
                        >
                        Shakespeare
                        >
                        In Oracle you do NOT need temporary tables. In Oracle you can keep the data
                        consistent with several users reading the same table. In Oracle they each
                        see the table and do not see uncommitted transactions from other users.
                        Jim


                        Comment

                        • Gloops

                          #13
                          Re: Access frontal interface for Oracle database

                          gym dot scuba dot kennedy at gmail a écrit, le 12/08/2008 05:59 :
                          In Oracle you do NOT need temporary tables. In Oracle you can keep thedata
                          consistent with several users reading the same table. In Oracle they each
                          see the table and do not see uncommitted transactions from other users.
                          Well, perhaps you remember, the topic of the thread is "how to cope
                          without temporary tables from Access" ?
                          Or in case of no answer, "how to update temporary tables".

                          I answered point 2 by hosting the temporary tables in Access, not in
                          Oracle, as in Oracle the update failed. The drawback is a big Access
                          file, but at least it works.

                          If you have an idea without temporary tables ...

                          I think I already saw that part of the solution is to forbid
                          modifications and use a snapshot recordset for the form, as well as
                          avoiding requests to fulfill the lists, but that is not suffisant.

                          If anybody tested something that works ...

                          Comment

                          • Shakespeare

                            #14
                            Re: Access frontal interface for Oracle database


                            "Gloops" <gloops@invalid .zailes.orgschr eef in bericht
                            news:g7r5ui$41d $1@aioe.org...
                            gym dot scuba dot kennedy at gmail a écrit, le 12/08/2008 05:59 :
                            In Oracle you do NOT need temporary tables. In Oracle you can keep the
                            data consistent with several users reading the same table. In Oracle they
                            each see the table and do not see uncommitted transactions from other
                            users.
                            Well, perhaps you remember, the topic of the thread is "how to cope
                            without temporary tables from Access" ?
                            Or in case of no answer, "how to update temporary tables".

                            I answered point 2 by hosting the temporary tables in Access, not in
                            Oracle, as in Oracle the update failed. The drawback is a big Access
                            file, but at least it works.

                            If you have an idea without temporary tables ...

                            I think I already saw that part of the solution is to forbid
                            modifications and use a snapshot recordset for the form, as well as
                            avoiding requests to fulfill the lists, but that is not suffisant.

                            If anybody tested something that works ...


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

                            You asked for a corrected syntax. I think I did that in my previous post.
                            Did you try it?

                            Shakespeare


                            Comment

                            • Gloops

                              #15
                              Re: Access frontal interface for Oracle database

                              Shakespeare a écrit, le 12/08/2008 07:52 :
                              You asked for a corrected syntax. I think I did that in my previous post.
                              Did you try it?

                              Shakespeare
                              Oh yes, add END after BEGIN MAJSES;

                              Did you really test it like that ?
                              Promised, I shall try.

                              You know, there has been a time to test different possibilities, and now
                              a time (too short) to apply one. It is right that the first one was too
                              short as I took a long time to find a newsgroup where I obtained
                              answers. Because of this, I did only let you two chances to provide the
                              good answer, I am sorry about this, it is very few.

                              I tell you tomorrow.
                              And if you have another idea I can also test on Thursday (and Monday and
                              Tuesday if it is very quick), if it works I shall know for my next
                              Oracle base.

                              Comment

                              Working...