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.OLE
AccessConnection.JPG
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.OLE
AccessConnection.JPG
Microsoft AccessMicrosoft Excel
Last Comment
Tom Farrar
can you check if your Data Source=S:\Acctg\Farrar\Fin
ASKER
Yes, it is. I can actually pick the query from the linking process, but other than headings from the query, no data.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
ASKER
WildCat - Thanks for the thought; my Connection was enabled.
ASKER
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)."
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)."
ASKER
Thanks for the comments, and the answer.