1 2 Previous Next 15 Replies Latest reply on Aug 21, 2015 2:28 PM by Kiran Pawar

    I have an Access Database and I have to migrate it to APEX

    Roxana01-Oracle

      What is the fastest way to do that? Is there a way to connect the MS Access to APEX? Also, I have a huge table in MS Access (11 mil rows) which cannot be exported to excel, due to its size; how can I upload this table in APEX?

       

      Thank you,

      Roxana

        • 1. Re: I have an Access Database and I have to migrate it to APEX
          Kofi

          Hi Roxana,

          I haven't tried this before but if you have a test database you can try exporting your MS access to some delimited text file, e.g Pipe delimited or CSV , then import into Oracle, perhaps using SQL Developer.

          I haven't used SQL Developer to import anywhere near 11 million rows before so that plus the size of your delimited file might lead to a more low level solution but give it a go.

          Kofi

          • 2. Re: I have an Access Database and I have to migrate it to APEX
            fac586

            1164317 wrote:

            Please update your forum profile with a recognisable username instead of "1164317": Video tutorial how to change nickname available

            What is the fastest way to do that? Is there a way to connect the MS Access to APEX? Also, I have a huge table in MS Access (11 mil rows) which cannot be exported to excel, due to its size; how can I upload this table in APEX?

            See the Application Express Application Migration Guide, specifically chapter 3 "Migrating a Microsoft Access Application".

            • 3. Re: I have an Access Database and I have to migrate it to APEX
              TexasApexDeveloper

              To migrate the data you could setup and odbc connection to your Oracle database from your Access database.  As Fac586has posted, you should look at the migration guide.  However, you would be best to just migrate the data (changing table definitions where needed) and then look at redeveloping your forms & reports and learning to re-code your vba code into pl/sql.

               

              Thank you,

               

              Tony Miller

              Los Alamos, NM

              • 4. Re: I have an Access Database and I have to migrate it to APEX
                Roxana01-Oracle

                Thank you all for your answers.

                I've tried to create a new connection in SQL Developer, using the APEX username and password, with settings below, but I get the error: Status: Failure - Test failed: IO Error: The Network Adapter could not establish the connection; What am I missing here?

                @TexasApexDeveloper: I also tried to create an odbc connection - but it fails also; Yes, this is my intention, to migrate the data and then change my VBA to PL/SQL

                 

                 

                I'm very new with this - I really appreciate your help

                • 5. Re: Re: I have an Access Database and I have to migrate it to APEX
                  Kiran Pawar

                  Hi Roxana01-Oracle,

                  Roxana01-Oracle wrote:


                  I've tried to create a new connection in SQL Developer, using the APEX username and password, with settings below, but I get the error: Status: Failure - Test failed: IO Error: The Network Adapter could not establish the connection; What am I missing here?

                  @TexasApexDeveloper: I also tried to create an odbc connection - but it fails also; Yes, this is my intention, to migrate the data and then change my VBA to PL/SQL

                  I'm very new with this - I really appreciate your help

                      Have you pondered upon the note under the section "Migrating the Database to Oracle" which says:

                  Note:

                  SQL Developer release 4.1, which runs on Java Development Kit (JDK) 8, does not support the creation of connections to Microsoft Access. In order to migrate your Microsoft Access MDBs to Oracle, you need to use an earlier version of SQL Developer (for example, SQL Developer 4.0 and JDK 7).

                      NOTE : As there is "Oracle" tab in "New Connection" dialog there will be tab for "Access" as well (for older versions of SQL Developer)

                      Refer : https://docs.oracle.com/cd/E59726_01/doc.50/e39148/appmgr.htm#AEMIG29137

                   

                       Also refer the following Oracle By Example(OBE) tutorial on the same topic : Migrating a Microsoft Access Database to Oracle Database 11g

                   

                  Regards,

                  Kiran

                  • 6. Re: I have an Access Database and I have to migrate it to APEX
                    Kofi

                    So Roxana is it an entire database or just the one table?

                    If it's a whole database with many tables then you need to do the migration thing the others have specified.

                    If it's just one table you can try my quick method.

                    Kofi

                    • 7. Re: Re: I have an Access Database and I have to migrate it to APEX
                      Roxana01-Oracle

                      Hi Kiran,

                       

                      Yes, indeed, I used SQL Developer 4.1 with Java Development Kit (JDK) 8.

                      Thank you very much - I'll try using an earlier version.

                       

                      Regards,

                      Roxana

                      • 8. Re: I have an Access Database and I have to migrate it to APEX
                        Roxana01-Oracle

                        Hi Kofi,

                         

                        It's an entire database, which also contains the table I mentioned about.

                        First - I was thinking to upload in APEX just the table and recreate all queries and forms from zero, but then I thought I should try to migrate the entire database and just re-code the VBA to SQL - I'll see if I can manage to do that

                         

                        Regards,

                        Roxana

                        • 9. Re: I have an Access Database and I have to migrate it to APEX
                          Roxana01-Oracle

                          Hi,

                           

                          I installed the SQL Developer tool version 3.0, with JDK1.6.0_11 for 32 bit and I get the same error:

                           

                          The username and password are the ones which I use to connect to APEX as administrator for my assigned workspace.

                          Is it possible that the hostname and SID are wrong when I try to create the new connection in SQL Developer?

                          I do not know how to identify the correct SID and hostname from APEX interface - can you please help?

                           

                          Thank you,

                          Roxana

                          • 10. Re: I have an Access Database and I have to migrate it to APEX
                            Kiran Pawar

                            Hi Roxana01-Oracle,

                            Roxana01-Oracle wrote:


                            I installed the SQL Developer tool version 3.0, with JDK1.6.0_11 for 32 bit and I get the same error:

                            The username and password are the ones which I use to connect to APEX as administrator for my assigned workspace.

                            Is it possible that the hostname and SID are wrong when I try to create the new connection in SQL Developer?

                            I do not know how to identify the correct SID and hostname from APEX interface - can you please help?

                                 Regarding Connecting to MS Access Database:

                                 You have to use the "Access" tab nearby "Oracle" tab to create an "Access" DB connection (See your screenshot above).

                                 Refer : http://docs.oracle.com/cd/E18464_01/appdev.30/e17472/dialogs.htm#BACJJDIA (Creating Access DB Connection).

                             

                                 Regarding Connecting to Oracle Database:

                                 Are you using Oracle DB 11g XE? If yes then SID = xe is correct otherwise contact your DBA to know appropriate SID/DB Service Name. Also, are you accessing locally installed DB or a DB installed on a machine in your intranet network? If remote DB you have mention the appropriate IP Address/Hostname.

                                 Refer :

                                 Regarding "IO Error : The Network Adapter could not establish the connection", it is due to faulty listener configuration or windows firewall.

                                 Refer :

                             

                            Regards,

                            Kiran

                            • 11. Re: I have an Access Database and I have to migrate it to APEX
                              Roxana01-Oracle

                              Hi Kiran,

                               

                              I didn't use Access tab, that was the reason for the previous error, thank you very much.

                              Now, I created the connection with my MS Access DB and I encounter another error:

                              I should now create a new user but the connection I've just created doesn't provide the option of creating new user (please see print screen below).

                               

                              I am using Access 2010 -> I've converted my database to an earlier version for extension .mdb and applied the changes as listed below:

                               

                              Access tab

                              For a connection to a Microsoft Access database, click Browse and find the database (.mdb) file. However, to be able to use the connection, you must first ensure that the system tables in the database file are readable by SQL Developer. To do this using Access 2003:

                              1. Open the database file in Microsoft Access.
                              2. Click Tools, then Options, and on the View tab ensure that System Objects are shown.

                                (With Access 2007, to view the system tables, click the Office button, then Access Options, then Navigation Options; and in Display Options group ensure that Show System Objects is enabled.)

                              3. Click Tools, then Security, and, if necessary, modify the user and group permissions for the MSysObjects, MsysQueries, and MSysRelationships tables as follows: select the table and give the Admin user at least Read Design and Read Data permission on the table.
                              4. Save changes and close the Access database file.
                              5. Create and test the connection in SQL Developer.

                               

                               

                              Can you please tell me what I should check next, so I can create the new user?

                               

                              Thank you for your help,

                              Roxana

                              • 12. Re: I have an Access Database and I have to migrate it to APEX
                                Kofi

                                Hi Roxana, you might want to look at this link Connect Step Access as it's a more visual tutorial and has linked Video about migrating to MS Access.

                                Kofi

                                • 13. Re: I have an Access Database and I have to migrate it to APEX
                                  Kiran Pawar

                                  Hi Roxana01-Oracle,

                                  Roxana01-Oracle wrote:


                                  I didn't use Access tab, that was the reason for the previous error, thank you very much.

                                  Now, I created the connection with my MS Access DB and I encounter another error:

                                  I should now create a new user but the connection I've just created doesn't provide the option of creating new user (please see print screen below).

                                  I am using Access 2010 -> I've converted my database to an earlier version for extension .mdb and applied the changes as listed below:

                                  Access tab

                                  For a connection to a Microsoft Access database, click Browse and find the database (.mdb) file. However, to be able to use the connection, you must first ensure that the system tables in the database file are readable by SQL Developer. To do this using Access 2003:

                                  1. Open the database file in Microsoft Access.
                                  2. Click Tools, then Options, and on the View tab ensure that System Objects are shown.

                                    (With Access 2007, to view the system tables, click the Office button, then Access Options, then Navigation Options; and in Display Options group ensure that Show System Objects is enabled.)

                                  3. Click Tools, then Security, and, if necessary, modify the user and group permissions for the MSysObjects, MsysQueries, and MSysRelationships tables as follows: select the table and give the Admin user at least Read Design and Read Data permission on the table.
                                  4. Save changes and close the Access database file.
                                  5. Create and test the connection in SQL Developer.

                                       The steps 1 to 4 are to be done in MS Access. 5th you have already done. This is to be done for SQL Developer to read the data from the mdb files. It doesn't ask to create a user. It asks to modify the user and group permissions for Admin user.

                                   

                                  Regards,

                                  Kiran

                                  • 14. Re: I have an Access Database and I have to migrate it to APEX
                                    Roxana01-Oracle

                                    Hi Kiran,

                                     

                                    The steps 1 to 4 I made them in MS Acess.

                                    The step 5 I made it in SQL Developer.

                                    After this - I should create a new user in SQL Developer, as stated in procedure:

                                     

                                    Step 2: Migrate the Microsoft Access Database to Oracle

                                    To migrate the Microsoft Access database to Oracle:

                                    1. Start Oracle SQL Developer version 1.2.0 or later. For example:
                                      1. Create an Oracle user named MIGRATIONS with the default tablespace USER and temporary tablespace TEMP and grant it at least RESOURCE, CREATE SESSION, and CREATE VIEW privileges.

                                      2. Create a database connection named Migration_Repository that connects to the MIGRATIONS user.
                                      3. Right-click the connection, select Migration_Repository, and then select Migration Repository and then Associate Migration Repository to create the repository.
                                    2. Load the database metadata (.xml file) created in step 1. Select Migration and then Capture Microsoft Access Exported XML.

                                      This captures the schema and creates a Captured Model of the Microsoft Access database.

                                    3. Convert the captured database schema to Oracle. Right-click Captured Model and select Convert to Oracle Model.
                                    4. Generate the migrated Oracle database schema. Right-click Converted Model and select Generate.

                                      The generated DDL statements should execute against your Oracle database instance, to generate the migrated schema objects.

                                     

                                     

                                    What am I missing here? Should I skip steps 1.1. and 1.2, as I have already created the connection with my MS Access DB and go directly to step 1.3 (Migration Repository)?

                                     

                                    Thank you for your help,

                                    Roxana

                                    1 2 Previous Next