+ Reply to Thread
Results 1 to 5 of 5

Excel 2013 connection to Access via ODBC

  1. #1
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Excel 2013 connection to Access via ODBC

    Hello, I have an Access db (.accdb) and I have an odbc connection (64 bit) to it. The db has 3 tables.
    What I need to do is to create a pivot table with data from all the 3 tables and refresh data from time to time.
    Not sure about the .accdb file, probably it was created with Office 365.

    In my PC I have Excel 2013 and I am trying to connect to this data source in a workbook. I can do it starting in the Excel file (using wizard that starts in the "connctions" window) or directly from "data origin" directory ("new connection" etc.). At the end a .odc file is created.

    However, even if wizards above ask me about the tables I want to link (and I mark all the 3 tables) when I try to create the pivot I end up with data available from a single table.
    I have no chance to add new tables, relationships and so on...

    Question: am I doing something wrong or is this related to the Excel version involved (in my case Excel 2013)?
    Thank you for all the comments/suggestions.

    P.S. If you have good links / books to suggest on this specific topic, please do. I have been looking for good materials on Microsoft data connections, but I have not found anything good enough. For example I'd like to know if ODBC is the right choice...
    F

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel 2013 connection to Access via ODBC

    You have two options in my mind. You can append each of the tables in Power Query and create a pivot table. Or you can join the three tables in Access by creating a Union Query. Once you have built the Union Query, you can then export the query to Excel where you can build your pivot table.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Re: Excel 2013 connection to Access via ODBC

    Hi. Thanks you for your reply. Using a query looks fine. In the pivot I can see field from the different tables.
    I 'd like to try now the other solution with Power Query since I see it is a free add-on.
    Thank you
    F

  4. #4
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Re: Excel 2013 connection to Access via ODBC

    Quote Originally Posted by alansidman View Post
    You have two options in my mind. You can append each of the tables in Power Query and create a pivot table. Or you can join the three tables in Access by creating a Union Query. Once you have built the Union Query, you can then export the query to Excel where you can build your pivot table.
    Just to give a complete answer... with Power Query installed, my Excel 2013 can successfully fetch data from more than a Access table. And the Power Query interface is quite similar to that one I used in another situation, with (probably) Excel 365..
    So I see now why it didn't work... I just needed a piece of s/w..
    Thank you
    F

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel 2013 connection to Access via ODBC

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create Excel Function that Gets Data via ODBC Connection
    By Tyrone__ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2014, 02:38 PM
  2. [SOLVED] Use ODBC connection without setting it up in ODBC administration
    By ExcelGal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-18-2012, 09:59 AM
  3. Pass credentials to Access from Excel for an ODBC connection
    By Jasrenkai in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-06-2011, 03:13 PM
  4. Excel VBA makes ODBC connection to Access-How do you close the connection?
    By MrHockey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2010, 06:29 PM
  5. Excel ODBC Connection problem.
    By ebbo in forum Excel General
    Replies: 0
    Last Post: 04-07-2009, 04:55 AM
  6. [SOLVED] How to Switch the ODBC connection in Excel
    By Pete Petersen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 03:05 PM
  7. [SOLVED] How do I set up my ODBC Connection from AS/400 file in Excel 2003
    By HTIMAPICSAdmin in forum Excel General
    Replies: 0
    Last Post: 06-01-2005, 04:05 PM
  8. Excel / Access ODBC connection
    By Rasmus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1