Avatar of Tom Farrar
Tom Farrar
Flag for United States of America asked on

Excel link to Access query not bringing back any data

I have linked an Excel spreadsheet to an Access query, and it does not bring back any data.  See attached example.  I have tried to bring the data in as a table and as a pivot table, but with no luck.  Running the query in Access brings in multiple rows.  

I am using Access 2016 and Excel 2016.  Any ideas on what is going on here?  Thanks.  - Tom

Here is the Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Acctg\Farrar\FinancialAnalysis-WIP.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;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
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
Professor J

can you check if your Data Source=S:\Acctg\Farrar\FinancialAnalysis-WIP.accdb is correct path and file?
Tom Farrar

Yes, it is.  I can actually pick the query from the linking process, but other than headings from the query, no data.
Flora Edwards

can you check if your workbook data connections is enabled?  To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

Hi jrb1 - Your comment sounds familiar, and getting rid of the "*" worked.  Is there somewhere in Excel to enable the * wildcard?

You are running into an issue with the ODBC Wildcard characters, which are "%" and "_".  I don't see an ODBC option to change this.
Tom Farrar

WildCat - Thanks for the thought; my Connection was enabled.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

For anyone interested:  It appears there is an Access option for changing ANSI standard (see below) though it may cause more problems than solutions.

From Internet:
"Unfortunately, Nick, the “%” and / or “*” can both be used as wild card functionality.  Although they may behave differently based upon the version of ANSI you are using.  I don’t believe ANSI was created by Microsoft and I think the standard was made by the American National Standards Institute.  Applications can then decide which standard they want to use.   Some applications make use of ANSI 89 and some ANSI 92.  From what I can see Excel by default makes use of the ANSI 92 standard, similar to Microsoft SQL Server.  Access databases by default makes use of a different version of ANSI, hence the difference in wild card behavior.  With that being said, Access does give you the option to make use of ANSI 92 in case you need to integrate with other applications that also make use of it.


In Access 2010, this option can be changed by doing the following:

File Menu -> Access Options -> Object Designers -> Check options below SQL Server Compatible Syntax (ANSI 92)."
Tom Farrar

Thanks for the comments, and the answer.