Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Oracle ODBC (Access 2000)

    I am working on a project to connect an Oracle database to an Access front-end using ODBC. When I select the tables I want to import or link, I get the message
    "There are several tables with that name. Please specify owner in the format 'owner.table'. (Error 3298)"
    There are no duplicate names in the list of tables. There is no place to type in an owner's name.
    I cannot find this error on Microsoft's knowledge base, MSDN site, or several Access MVP's sites that I've visited.
    Help!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Oracle ODBC (Access 2000)

    We need to know some additional details in order to be of much help. For starters:<UL><LI>What version of Oracle are you trying to connect to?<LI>What ODBC driver are you trying to use to connect - Microsoft or Oracle?<LI>What level of service packs or releases have been installed on the workstation you are trying to use?<LI>When you install an ODBC data source does it say it successfully connected to the Oracle database?[/list]We'll probably come up with more - while you are digging up those details I will look and see what kind of web resources might be helpful.
    Wendell

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Wendell,

    Here's the information I was able to find:

    * What version of Oracle are you trying to connect to? -- Oracle Reflection for HP Version 7.00.228 P10

    * What ODBC driver are you trying to use to connect - Microsoft or Oracle? -- Microsoft ODBC for Oracle 2.575.1022.00

    * What level of service packs or releases have been installed on the workstation you are trying to use? -- Access 9.0.2720

    * When you install an ODBC data source does it say it successfully connected to the Oracle database? -- No, did not receive a message like this.

    But, the name does show up as a System Data Source in ODBC Data Source Administrator. The name also appears as a Machine Data Source when I attempt to import or link in Access, and the list of tables in the Oracle database appear in the Import Objects dialog box. After selecting the tables to import or link, the error message appears . The message appears no matter which table(s) I select.

    To add to the confusion/frustration - I can use the same ODBC Data Source in Excel and import data from the Oracle tables successfullyl.

    Thanks for any help or advice,
    Beverly

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Look in your list for a table name that is preceded with the owners name and a dot.
    For instance, If you are looking for a the Oracle table
    'someTableName'
    then look for
    'ownerName.someTableName'
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Oracle ODBC (Access 2000)

    Hi Beverly,

    Don has suggested a probable solution to your problem. Here are some other links that you might find useful in working with Oracle from an Access front-end:
    Microsoft Access Page includes tutorials, solutions, SQL, ODBC, Oracle...
    Oracle ODBC Drivers - Points to Note
    Query Designer Considerations for Oracle Databases
    Linking Oracle Tables to Microsoft Access

    I think this should get your over the hump with the connection to Oracle - but as you can see, there are lots more questions and issues to look at. Good luck - we'll do our best to help.
    Wendell

  6. #6
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Okay, I've found out that the Microsoft ODBC driver has a "quirk" and that if you try to link to tables with just the base name, you are selecting synonyms and the driver gets confused. Hence the message "There are several tables with that name. Please specify owner in the format 'owner.table' (Error 3298)". However, my list of tables does not have the owner name prefixed to the tables, it is showing only the base names, and there is no location to enter the owner name.

    How do I get the owner names to appear in the select tables box?

    Thanks for any advice,
    Beverly

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    From my experience, I have had to have my id given permission to see the owner's tables. Once that was done, I was able to connect to the tables. You will need to talk to your DBA or Security person to gain access.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  8. #8
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    I will ask the DBA to check this.

    Thank you!
    Beverly

  9. #9
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Well, the DBA tells me that the ID I'm using has select permission on the tables I want. In the Grant Select statements, the tables are specified in the owner.tablename format. If I go into Excel, I can use the ODBC connection and the ID to get at the tables I want, and the tables are listed in owner.tablename format. But it won't work in Access; for some reason, in the link/import table box, it displays only the base name and not the owner.

    How frustrating!
    Please advised if any further ideas or thoughts.

    Thanks,
    Beverly

  10. #10
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Beverly,

    If you know the owner name use the DoCmd to do your connect.

    <pre>DoCmd.TransferDatabase acLink, "ODBC"
    , "ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=ServerName;" _
    & ";TABLE=OwnerName.Your_Table_Name"
    , acTable
    , "OwnerName.Your_Table_Name"
    , "Your_Table_Name"
    , False</pre>

    or create a macro

    Action: TransferDatabase

    <pre>Transfer Type:
    Link
    Database Type:
    ODBC Database
    Database Name:
    ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=ServerName;;TABLE=OwnerName.Your_Ta ble_Name
    Object Type:
    Table
    Source:
    OwnerName.Your_Table_Name
    Destination:
    Your_Table_Name
    Structure Only:
    No </pre>


    Hope this helps.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  11. #11
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Oracle ODBC (Access 2000)

    Thank you! I'll try this!!

Posting Permissions

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