Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Getting data via VBA from Oracle to Access db?

    Hello experts !

    Please help me out . I have assignment to get data of oracle in access. Any one help me how to get data in access from oracle via vb?


    Thanks
    hameed

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why not link the oracle tables from access then use a make table query or if you table already exists in access use an append query.

  3. The Following User Says Thank You to patt For This Useful Post:

    farrukh (2012-10-25)

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Patt,

    Very thanks for your reply...

    Access already has its own table and columns .Shall you please assist more how can i do this ?


    Thanks

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Firstly i would link the Oracle table in Access.
    Then create a make table query that creates a new table in Access:
    SELECT * INTO NewAccessTable FROM OracleTable

    Obviously change the names to suit.

    You then have your new table in Access. I would be careful since i don't know how many records are in the Oracle table.

  6. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You mention VBA in the title of your post, but you don't indicate what version of Access you are using, so knowing that would be helpful. It is possible to open an ADO or DAO recordset and use VBA to read records from an Oracle database, but it is a very complex process that is dependent on the Oracle table structure. Pat's suggestion is to use the External Data capability in Access to create an ODBC linked table so that you can see the complete Oracle table just as you would an Access table. That requires creating an ODBC data source that uses the Oracle ODBC driver. The advantage of that approach is that no code is required and you can run queries just as you would with Access tables. However the details of doing that are different in Access 2003, in Access 2007, in Access 2010 and 2013.
    Wendell

  7. #6
    Star Lounger
    Join Date
    Mar 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another way of doing it is via excel. Export your data to excel from oracle then import it on your access db. This is just a workaround and if above suggestions are not working.

  8. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    WendellB,

    I am using access 2007. Sorry i didnt get the replies in my in box... just got the reply from jaydeee

    Thanks

  9. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In order to create an ODBC connection you will first need to download and install the ODBC driver for Oracle. Once you have that complete, you use the Data Sources (ODBC) to create a DSN to the database you are connecting to. The final step is to choose the External Data tab on the Ribbon, click on the "More" icon on the Import group, and choose ODBC. That brings up a dialog where you can choose to either import or link - choosing link brings up a dialog that lists your defined Data Sources. On the Machine Data Sources you should see the name of your Oracle database. Choose that and it will list all of the tables in your database and you can click those that you wish to link to.
    Wendell

  10. The Following User Says Thank You to WendellB For This Useful Post:

    farrukh (2013-03-22)

Posting Permissions

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