+ Reply to Thread
Results 1 to 41 of 41

Create connection between Excel and Access - queries

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Create connection between Excel and Access - queries

    Hi Guys,

    I have connection string like below:

    Please Login or Register  to view this content.
    This is working when in SQL command there is :

    Please Login or Register  to view this content.
    Now 2 things:
    1. When i have password on my split database it is not working, where can I put the password in connection string?
    Better is using DSN or Microsoft JET?
    2. I want to create connection between Excel worksheet and Access Query, not table, it is possible?


    Bez*tytu?u.png

    Please help Guys,
    I didn't find the solution in internet,
    Best wishes,
    Jacek Antek

  2. #2
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    I am using MS Office 2010, split database (FE with accde extension and BE with accdb extension).

    It is relational database so pulling out only tables it is wrong approach so query would be the best.
    Table have foreign keys only and whole data is written in other tables...
    these tables i should combine somehow in Excel or better in source - in Access database as query.

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    hi Guys,

    anyone?

    Best wishes,
    Jacek

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Is it no possible ?

    Jacek

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Create connection between Excel and Access - queries

    Maybe you should ask moderator to move your thread here: Access Tables & Databases

    Note: I said: maybe

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Moderator,

    I am asking you to move my topic ;-)

    Jacek

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Create connection between Excel and Access - queries

    Moved as requested.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Anyone?

    the last time here,
    best wishes,
    Jacek

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Hi Guys,

    it is possible to do that?

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    I have found that power query has possibility to do it.
    But without it?

  11. #11
    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,811

    Re: Create connection between Excel and Access - queries

    Here is what my data connection looks like for getting the results of a query into an excel worksheet.
    Attached Images Attached Images
    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

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Thank you my friend,

    i have noticed that if you have query only from one table - Excel is seeing this and i can create connetion.

    But if i am building query consisting of 2 tables and relationship between them - Excel will not see it.

    could anybody check if this is true ?

    Thank you for help,
    Jacek

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    Hi,

    I can connect to a query using more than one table. What does not show up for me is any query with an invalid SQL and of course any queries using VBA functions. I cannot recall if Nz is allowed either.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    If it's two tables on same db. no issues. You can use single connection to query both in single statement.

    If you are accessing two tables, each residing in separate db. You will need two connection, one for each db.
    Then you'd query each separately into it's own recordset.

    Once done, you can then loop through recordset(s) to manipulate or put it into result array as needed.
    EDIT: For this you need to leverage ADO and use VBA. Can't do it with MS Query as far as I know.

    It's much simpler to use PQ for this. As you can easily do joins, unions etc without needing to loop through recordset, once data is brought in from each db.
    Last edited by CK76; 10-27-2017 at 08:07 AM. Reason: See Edit:
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  15. #15
    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,811

    Re: Create connection between Excel and Access - queries

    I cannot recall if Nz is allowed either.
    If NZ is part of the query, it will not come over to Excel.

  16. #16
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Hmm very strange. tahnk you for your help.

    Let's do test.

    I am connecting into Access frond-end which has linked table with Access Back-end with password.

    In Access Front-end i can not see the relationships between tables (maybe this is a reason?).

    I am joinign Excel with Access Front -end.

    I made 2 queries: test1 and test2.
    test1.png

    test2.png

    And now i am creating new connection with Excel:
    Data--> from Access database --> i am pointing to my front-end
    and the result is:

    query.png

    Why it is possible?
    I am seeing only query with one table.

    Best Wishes,
    Jacek

  17. #17
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Yep. NZ will throw error even when using ODBC DSN. And often won't be able to see query using standard method.

    [Microsoft][ODBC Microsoft Access Driver] Undefined function 'nz' in expression.

  18. #18
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Ok, Nz I am understanding.

    I created sample database in Access 2007 format (accdb).
    Password for BE is: 1234

    Can i pull out Qry1 and Qry2 from FE?

    Could you please help and check if you can do it and what is a connection string or steps how to do it ?

    Thank you in advacnce,
    Best wishes,
    Jacek
    Attached Files Attached Files

  19. #19
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    PQ is definitely able to see it.
    0.JPG

    Also using ODBC DSN I was able to see all tables and queries.
    1.JPG

  20. #20
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Power query is working without any problems, but problem is that i want to give the user posibility to resfresh table without having power query.

    The strange thing is that i have the same connection string as you , the same sql command like you:

    sql command.png

    and if i manually point to Excel my database path there will be shouting: this source data do not contain any tables.
    And if i click ok :

    odbc driver.png

    odbc access driver - wrong password.
    To front-end i do not have any password...

    [edited out]

    Maybe problem with Access ODBC driver?

    Best Wishes,
    Jacek
    Last edited by AliGW; 10-27-2017 at 09:41 AM. Reason: Unnecessarily strong language (abbreviation) removed.

  21. #21
    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,811

    Re: Create connection between Excel and Access - queries

    Agree with CK76. Using PQ and changing the path to the Back End, I was able to see both queries in Excel.

  22. #22
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Couple of suggestions.

    1. Avoid use of \Desktop\
    In many companies, IT provisions Desktop to each network terminal using Logon Script etc. What this means is that your local Desktop may not match what's provisioned. Best to avoid use of Desktop for any code/connection related folders.

    2. Update your driver, also ensure bitness of DB and driver match.

    Other than that, I'm not sure where your issue stems from.

  23. #23
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    It does appear to be an issue with the password on the BE database and the ODBC driver. If you remove that password, everything works fine. Perhaps using a pass-through query might help.

  24. #24
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Odd. I had no issue with PW on my end (just had to update path of linked table).

    I was able to pull data from Qry1 & 2.
    0.JPG

  25. #25
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    Like the OP I have 2010 and it does not work with either Data- From Access or using MS Query.

  26. #26
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Use "From Other Sources" -> "From Data connection Wizard" and use ODBC DSN.
    "From Access" will not be able to see it.

    Though I'm using 2013 & 2016, MS Query hasn't changed over multiple versions. Hence, my guess at ODBC driver being the culprit.

  27. #27
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    After removing the password from the BE, Data-From Access does work for me. With the password in place, no method works whether ODBC or directly OLEDB. I suspect it may relate to a change in the Jet provider. Interestingly, I could get the data into MSQuery itself, but as soon as I tried to return it to Excel, the password error occurred.

  28. #28
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Wait, shouldn't it be ACE instead of JET engine to query "*.accdb"?

    Try installing 2007 Office System Driver.
    https://www.microsoft.com/en-us/down...190a24fa6=True

  29. #29
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    ACE is just an updated version of Jet (it's still Jet, I think). I was using the ACE OLEDB provider since it comes with 2010.

  30. #30
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Yeah, base technology is JET. And has 4.0 based architecture for backward compatibility.
    Though JET won't connect to accdb.

    Try different ACE (Access DB Engine), versions. From your description I suspect either ACE or ODBC being culprit.

  31. #31
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    Our IT department won't allow me to do things like that!

  32. #32
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    lol. Figures.

    Thank goodness I don't work for corporation.

  33. #33
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Guys,

    i will check your solution (if i would find out what it is) on monday

    Jacek

  34. #34
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Hi!

    I tested Microsoft Jet 4.0 OLE DB Provider and this driver does not see accdb files...
    Also i was testing Microsoft Office 12.0 Access Database OLE DB Provider and still problem with password.

    What else i can use?

    Jacek

    connections.png

  35. #35
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Hi Guys,

    i have found "solution":

    https://answers.microsoft.com/en-us/...bac1d6a?auth=1

    Could you please check if is this working for you ?

    I do not know if I am implementing this ok but it is no working for me.

    I will be grateful.
    Best Wishes,
    Jacek

  36. #36
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Create connection between Excel and Access - queries

    Hmm I don't need to enter password while querying DB with Qry1 & 2.

    Have you checked using different versions of Access Database Engine?

    There are some known issue with "Microsoft Access Database Engine 2010 Redistributable". It's counter intuitive, but try using "2007 Office System Driver: Data Connectivity Components".
    https://www.microsoft.com/en-us/down....aspx?id=23734

  37. #37
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    I do not know but still is asking about new password.
    If you hit the enter window popup will appear again and again....

    echh,

    xlnitwit maybe you can try to use the method from my link before?

    Best wishes,
    Jacek

  38. #38
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    Yes- using legacy encryption works for me here.

  39. #39
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Thank you !

    I have polish access and i can not find this.

    Could you please describe your steps one by one?

    legacy description.png

    File-->options-->client setting --> and this option which i have choosen in the picture above?

    And after that you put the password down, and set up new password?

    Please give a tip ;-)

    Jacek

  40. #40
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Create connection between Excel and Access - queries

    Yes. I removed the password, selected the option you have checked there and then reapplied the password. After that, it all worked.

  41. #41
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Create connection between Excel and Access - queries

    Wow it is working !!!


    thank you Guys for your help and support.

    Jacek

+ 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. Connection Issues, Excel To Access Query, Access 2010
    By onechief in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 07:09 PM
  2. Replies: 0
    Last Post: 01-21-2013, 12:47 PM
  3. Embedded Access queries in Excel - if the database moves, how to update queries?
    By Paul_mcc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 07:52 PM
  4. Some queries in Access cannot be imported to Excel
    By Helge in forum Excel General
    Replies: 1
    Last Post: 01-10-2012, 01:54 AM
  5. 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
  6. Excel Queries using Access data
    By baconroll in forum Excel General
    Replies: 1
    Last Post: 10-12-2005, 01:05 PM
  7. [SOLVED] Run Access Action Queries from Excel?
    By Larry A in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2005, 05:05 AM

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