+ Reply to Thread
Results 1 to 2 of 2

Pulling data into Excel's query tables without Access installed

  1. #1
    Registered User
    Join Date
    09-05-2013
    Location
    Camarillo, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Pulling data into Excel's query tables without Access installed

    I wasn't sure where to put this, but it shouldn't be too uncommon of a problem. When I try to update query tables on a PC with Access installed, I have not had problems. Otherwise, I'll get a question that asks me if I want to connect to that database and if I say yes, I get some kind of very general application error (using the Refresh method on a ListObject.)

    I've tried installing the 2010 redistributable which seemed to have no effect and am waiting for my IT department to try the 2013 runtime since we're using Office 2010, but some people like myself work with Access 2013 which is where I built this particular database.

    It seems to me this shouldn't require special software, but I know very little about connections strings. Any time I work with them, it's in large part on a trial and error basis. Is it possible that modifying the default connection string that Excel creates to connect with Access would solve the problem, or do I really need these Microsoft redistributables on every PC? If it's the connection string, I'd much appreciate if someone could give me a good idea as to what might work. Unfortunately I don't have access to any other PCs right now that don't have Access installed, and so am very limited in how much I can experiment.

    Incidentally, here's the current string: Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=Y:\Prolacta Manufacturing\Manufacturing Lead Data Input\Manufacturing Input DB.accdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

    Another possibility - I'm running a 32 bit version of Access 2013 and at least the machine I tried to connect with the Access DB I believe has the 64 bit version. According to the article at http://www.altova.com/Access-Databas...bit-64bit.html, it says "There is currently an unfortunate limitation of the 64-bit ACE driver – it cannot co-exist with 32-bit versions of Microsoft Office."
    Last edited by tbaker818; 02-17-2015 at 07:38 PM.

  2. #2
    Registered User
    Join Date
    09-05-2013
    Location
    Camarillo, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Pulling data into Excel's query tables without Access installed

    I found a resolution to this problem - by default, Excel's query tables use an OLEDB connection string. When I switched to an ODBC string, I was able to refresh the tables without having Access installed.

+ 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. VBA Code pulling Query from Access
    By Spear86 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-16-2015, 09:13 AM
  2. Excel 2010 userform to access and edit the data in MS ACCESS DB tables
    By anand_y59 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2012, 07:46 AM
  3. Access VBA or SQL to create a query that has matching data from 2 tables
    By ewong in forum Access Programming / VBA / Macros
    Replies: 15
    Last Post: 09-13-2012, 04:42 PM
  4. Excel Pulling Data from Access
    By cchampagne17 in forum Access Tables & Databases
    Replies: 1
    Last Post: 07-16-2012, 12:56 PM
  5. Pulling a Query from Excel via Access
    By Masact in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2009, 11:56 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