CODEWITHSUNDEEP
ms-accessms-access-2007
user2993456
user2993456

Reputation:

Excel table not importing data from properly functioning Access query

Alright, I have an Access DB which I am trying to import data from, into Excel. Specifically, I am attempting to import the results of a query which I wrote in Access.

I wrote this query in Access SQL (I have also enabled the ANSI 92 compliant syntax feature in all my Access DB's).

They query isn't any thing too mind boggling. It runs just fine in Access, all the time. No issues with Access. The problem is, that when I add a specific criteria to the WHERE clause of my query, the linked Excel file will not import the data. The Excel file just shows a table-ized area with no data.

However, with this criteria removed from the WHERE clause in my query, the linked Excel table is able to refresh just fine, and the table populates with the query results, no-problemo.

Here are pictures of the query: (I drew on the first one with the paint-brush, because it made me laugh, and I am bored in my cubicle) enter image description here

Here is a picture of the Query after the additional criteria is added, which then seems to prevent my Excel table from showing any data from the query results: enter image description here

My question is simple, why exactly (in detail, please) does this happen? Is there some sort of query size limit within Excel that I don't know about?

Also, a work-around would be nice, if possible. But I can run the query without this criteria if necessary. I am mainly more interested in knowing the reason behind this issue.

Upvotes: 0

Views: 14433

Answers (7)

Tony P
Tony P

Reputation: 1

I managed to get around this by selecting, Data, Get Data, From Other Sources, From ODBC. Then select MS Access Database and advanced options. Put in a connection string (I updated an existing string that I had). String looks like this:

DSN=XXXX;DBQ=[location of the database, including the DB name. Eg. M:\stuff\databse.mdb];DefaultDir=[location of the database, excluding the DB name. Eg. M:\stuff];DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

I am sure you can look up how to compile this.

After this I can see the query data and import it into excel.

Hope this helps.

Upvotes: 0

David
David

Reputation: 1

Another work around is to create and run a make table query with the same fields as your original query, essentially turning your existing query into a table.

Then import the table into Excel instead of the query.

You have to have 2 queries instead of 1, and you have to remember to run both queries anytime you update your data, but you don't have to change the code that the original query runs.

Upvotes: 0

rohrl77
rohrl77

Reputation: 3337

I came accross the same problem and found a similar solution. When using SQL in Excel, commands using Like "Car*" do not work. Alike "Car%" yields the correct result.

The reason why this is the case is documented here: CodeVBA

In SQL-89 compatibility mode LIKE only works with * and ? patterns but if you change the compatibility to SQL-92, you need to rewrite your queries with % and _ instead. ALIKE allows you to write ANSI compliant patterns regardless of the compatibility level you choose (so in SQL-92 compatibility mode, both are behaving the same).

Upvotes: 2

Mart
Mart

Reputation: 1

Thanks to Davon M. I managed to solve my own problem. (modified query in access, and suddenly no data in Excel, even though the Access query was fully functional)

It would seem that Excel's query brains are different from Access's query brains because it is going via the data access libraries on the machine.

After moving my SQL from access to the Excel data connection, I spotted the where clause - [fieldname] LIKE "ABC" . IT works fine in Access, but Excel was giving me nothing. - even though the original query was (in my mind) 'running' in Access.

So... Having picked up all the hair I tore out getting this far, I have also worked out that... LIKE 'ABC', works in access, but not via the engine that excel uses to get the data. However, LIKE '%ABC%' does work through that engine.

So... I have resorted to:- * a version of the query in Access for use in access, * AND a copy-pasted version of the query (with alternate wildcards in the SQL) for use by Excel.

daft, but tolerable in my circumstances

Upvotes: 0

MarioB
MarioB

Reputation: 1

I had the same issue, and after some debugging I found part of the query was using an object from Access (a toggle button in a form). Hence, this is not available when the form is closed, and it does not make sense to Excel, so it does not offer to import it. That's just what happened to me, hope it helps.

Upvotes: 0

AGreatBigBear
AGreatBigBear

Reputation: 1

I was having a similar issue in a new role, and I can only imagine my manager thought I was an imbecile. However, I figured it out. It's the the trust centre settings in excel options, you need to enable all external data.

Upvotes: 0

Davon Modlin
Davon Modlin

Reputation: 36

Once establishing a connection with you Access database have you tried going to Connections -> Properties -> Definitions Tab. Change you Command Type to SQL and copy the SQL text into the box that says Command Text. I had the same issue you're having but this appears to work around it. Works for me.

Upvotes: 2

Related Questions