Results 1 to 5 of 5
  1. #1
    croco is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3

    Access to Hadoop by ODBC

    Hi folks! Try to import a table from a Hive database via ODBC. ODBC connection is created. From Excel I can use it and I get a (small) table. But I had to change the SQL going to the external (Hive) server - the default SQL located the source table in a schema HIVE, which doesn't exist. From Access I don't get insights in the ODBC SQL. It just asks for the ODBC connection, shows my the correct tables, I have to choose one and afterwards it answers: "MS Access can't find this object" - what's clear, if Access is constructing the SQL in the same was as Excel does. But how I can modify the import SQL in Access? Thanks in advance! Croco

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the sql shouldnt be the problem. Did you link the tables into Access?
    Then the sql should run normally as a query.

  3. #3
    croco is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3
    Sorry, may be I explained it in the wrong way. Whenever an application on the client side imports a table tab1 from the server (in this case Hive), it sends via ODBC in the background a simple SQL "select * from tab1" to the server. Has nothing to do with the SQLs in Access itself. You can see and modify this import SQL in an Excel ODBC connection. But I can't see or modify it for the Access ODBC connection.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    access may use a different name for the table via odbc,
    that is why you should link in the table.
    If you already have the ODBC and it works, then link in the table, run a query.

  5. #5
    croco is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    access may use a different name for the table via odbc,
    that is why you should link in the table.
    If you already have the ODBC and it works, then link in the table, run a query.
    Doesn't work either
    • ODBC Database
    • Create new link to source
    • Choose ODBC connection
    • A listing of all tables on the server is shown for selection
    • I select one
    • "Object not found"


    EDIT:
    I found a workaround: Load the table from VBA
    Option Compare Database


    Sub test1()


    Dim Cn1 As New ADODB.Connection

    Dim Rs1 As New ADODB.Recordset

    Cn1.Open <My DSN name>, <user>, <pw>

    Rs1.Open "SELECT * FROM tab1;", Cn1


    End Sub

    May be I can also link the table with VBA - would be more comfortable.

    Anyhow I would like to understand, whether it's possible to configure the ODBC import for Access just to do for what it's intended...
    Last edited by croco; 10-12-2017 at 08:36 AM. Reason: Additional information

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  2. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  3. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  4. odbc to another access db
    By bbxrider in forum Import/Export Data
    Replies: 6
    Last Post: 02-08-2011, 10:57 AM
  5. ODBC -- call failed. | Oracle ODBC
    By drdexter33 in forum Access
    Replies: 1
    Last Post: 04-03-2010, 09:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums