Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Exclamation Unanswered: export ORACLE DB to mdb

    Do you guys know of any tools or ways to export certain tables from my ORACLE DB to a Microsoft Access mdb?

    Thanks,
    David

  2. #2
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: export ORACLE DB to mdb

    Originally posted by R6Diavolo
    Do you guys know of any tools or ways to export certain tables from my ORACLE DB to a Microsoft Access mdb?

    Thanks,
    David
    Are you sure you want or need to export the tables from Oracle?

    You could link the tables using ODBC and then you would always have "live" data from Oracle. If you need to have the table data in Access so you can access it when not on a network where Oracle is available you could always try using the Import option under Get External Data rather than Link Tables. This forces you to get new data from Oracle every time the Oracle data changes though.
    Regards,
    Terry

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i think i'll stick my problem in here, i'm trying to link to ODBC i get as far as choseing the table from the database called Avante, and then access closes it's self ne idea why? i'm running access 2000, and win 2000

    i can get the same thing to work through Excel which uses MSQUERY, but i really don't want to link
    ODBC -> MSQUERY -> Excel -> Access
    Last edited by m.timoney; 11-22-02 at 13:14.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by m.timoney
    i think i'll stick my problem in here, i'm trying to link to ODBC i get as far as choseing the table from the database called Avante, and then access closes it's self ne idea why? i'm running access 2002, and win 2000

    i can get the same thing to work through Excel which uses MSQUERY, but i really don't want to link
    ODBC -> MSQUERY -> Excel -> Access
    ODBC connections to Oracle can be a bit tricky. You may have the option of using Microsoft, Oracle, or some other vendor supplied Oracle ODBC drivers - each with their own specific setup.

    To use an Oracle database, you'll should have the Oracle client installed and configured so that the database service name is recognized by your PC. You'll need to know the network protocol, database port number, database instance name, and the host (server) the databse resides on. The Oracle client provides a tool for performing this configuration and then testing it once you are sure you've configured everything. I would recommend you make sure this level of communication works before doing anything else.

    ODBC links to tables in Access can also be done in many ways, depending on what you need. The original question in this thread leads me to believe the link to an Oracle table will be a one-time event, so it will be performed using the menus rather than implemented in VBA code.

    In order to use the menus to link tables, you should define a DSN (Data Source Name). This is where you will select the appropriate ODBC driver, service name, and connection parameters. The Oracle ODBC driver and MS supplied driver for Oracle are the only two I have experience with, but there are others. I would recommend using the Oracle drivers since I've had the best luck with them for most things I try to do from the menu. You'll need to know the service name your computer will expect the data from, a name for the Data Source, and a helpful description. I'm on Windows NT, so the setup is done from within Control Panel using the Data Sources/ODBC applet. I would suggest creating a system DSN rather than a User or File DSN. This so that anyone using the PC will be able to use the same DSN.


    When using the menus, Select File/Get External Data/Link Tables. A dialog bow should appear prompting for a data source. Go to the Files of Type combo box and scroll to the bottom of the list and select ODBC. A Data Source/ODBC dialog box similar to the one you defined your DSN in will pop-up. Select the DSN you created and click OK. You should be prompted to log in to the Oracle database and if successful, you will be presented with a list of the tables from the database you can select to link into Access.

    If you have problems during this process, you'll need to isolate where you are having the problem - it may not be within Access. The Oracle database may be the culprit. Get to know the Oracle DBA for the database - they can usually help you out if they know what you're trying to do.
    Regards,
    Terry

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i can get this far and when i click ok access closes it's self, if i could even get one of the usual completly unhelpful microsoft help message it would be an improvement, and i can't believe that it's Avante as it works perfectly in msquery
    Attached Thumbnails Attached Thumbnails screen.jpg  
    Last edited by m.timoney; 11-22-02 at 13:15.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by m.timoney
    i can get this far and when i click ok access closes it's self, if i could even get one of the usual completly unhelpful microsoft help message it would be an improvement, and i can't believe that it's Avante as it works perfectly in msquery
    OK, Try tracing your ODBC connection. Open the ODBC/Data Sources applet and go to the Tracing Tab. Fill in a log file path and filename and click the Start Tracing Now button. WARNING! These files get huge and tracing sucks the performance of your PC nearly to zero. I'd suggest that you have the dialog box ready to go and only click the Trace button just before you start selecting tables. After Access closes (since you say that's fairly predictable) make sure to click the same button again to stop the tracing. Reads the lesulting log file and see if there are any errors.
    Regards,
    Terry

  7. #7
    Join Date
    Aug 2002
    Posts
    3
    I need to ex/import 'cause I want to upload the created mdb to my PALM.
    So I'm searching for a way to run a script which creates a new mdb with certain tables of the ORACLE db every night.
    Do you think it's best to write a script in VBA? Does anyone have any examples?

    Thanks,
    David

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    it doesn't even create a log file, i hate microsoft!
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by m.timoney
    it doesn't even create a log file, i hate microsoft!
    You seem to be having some serious problems. I'd suggest that you get a tool to check your ODBC setup. I use one called COMCHECK. Microsoft hands it out for free at Free MDAC Downloads

    Download it and run it and see what version of Data Access Components you have. There's a part of the check that also probes into ODBC drivers too. If it detects any errors, you'll be notified.

    You can also use the same tool to upgrade MDAC. This may be a good thing to do because I've experienced relief from some really weird bugs by using later versions of MDAC. The only problem then is delivering the app to target machines with older versions of MDAC.

    Good Luck!
    Regards,
    Terry

  10. #10
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by R6Diavolo
    I need to ex/import 'cause I want to upload the created mdb to my PALM.
    So I'm searching for a way to run a script which creates a new mdb with certain tables of the ORACLE db every night.
    Do you think it's best to write a script in VBA? Does anyone have any examples?

    Thanks,
    David
    OK, I see what's happening here. My suggestion would be to write the script in VBA. You'll do yourself a tremendous favor because Macros won't be supported forever in Access - not only that, you can detect and handle errors using VBA whereas you can't with Macros.

    Give me an example of what one of your tables (in Oracle) looks like and I'll see what I can do for you over the next day or so. I can't do much before that because of a work project deliverable.
    Regards,
    Terry

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thanks thats got it, though i'm now have all sorts of fun trying to query the tables.

    http://dbforums.com/t579058.html

    Originally posted by spraguetr
    You seem to be having some serious problems. I'd suggest that you get a tool to check your ODBC setup. I use one called COMCHECK. Microsoft hands it out for free at Free MDAC Downloads

    ...Good Luck!
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by m.timoney
    thanks thats got it, though i'm now have all sorts of fun trying to query the tables.

    http://dbforums.com/t579058.html
    I'm glad to have been of some help. What did you find was wrong?
    Regards,
    Terry

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    follow the link and it will tell you
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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