• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data not recognized by SQL query

T

Tobishi

New Member
Hi,

I have a read-only file, into which I load data from an Access DB using DAO / SQL - which works fine.

Then using ADO, I perform an SQL query on the this data (select distinct one field). Here the query returns nothing (nor it raises an error).

If I access the file read-write or the data is already saved in the workbook, it works.

As I need the file in read-only mode and the data is kept in the DB (and can't be saved in the file), I need somehow to manage ADO/SQL to recognize that "data is there".

I already tried with FullCalculation, RefreshAll, Saved = true but no luck so far.

Anyone any idea?
 
Can you post the query string ?
If it involves dates, are the dates in the format #mm/dd/yyyy# including the hashes?
 
One of the ADO Sql strings is:

SELECT DISTINCT [REPORT] FROM [INPUTDATA$] ;

None of them includes dates.

I doubt this can be the problem anyway, as everything works fine if I open the file read-write or the data is saved in the file (But it has to be read-only and without any data, which is loaded from an Access DB using DAO)

Regards
 
Hi ,

In that case , why not write code to save the data to a temporary workbook , which can be deleted after you have finished your data processing ?

Narayan
 
Hello,

I thought of a workaround using the temp workbook, but unfortunately it won't serve my purpose / would be more cumbersome.

I guess I will go back to my previous solution using advanced filters.

It's a pity as the "in-Excel-sql" solution is faster and more elegant..

Thank you both

Hasan
 
Hmm, can't you directly query Access DB, put recordset into array, do your operation within VBA and get your output? Without exposing actual recordset.

Or am I missing something here?
 
well the thing is:

The data is in an Oracle table, which I grab via Access. It's big and growing with every quarter (reporting frequency).

I use DAO / SQL to load filtered data (let's say "data subset") from Access into Excel. This loading is based on filters set by the user in the respective "Excel-session". (It's concurrent multi-user tool; multiple users and/or one user in multiple Excel instances can load differing data subsets)

Depending on user settings for further action (e.g. report type, report date, reporting entity), I need to determine 'distinct values' to populate various drop-downs. For this purpose I was using advanced filters before, switched to ADO / in-Excel-SQL (much faster). These user settings can be altered during the session many times, each time the distinct values queries must run.

Here I could do direct queries into Access, but it would take much more time than in-Excel-Sql (as I already loaded the relevant subset, which is only a fraction of the whole data, plus network traffic etc..).

The data subset anyway must reside in Excel during the session (saved in a dedicated structured table) due to complex calculations (which are in Excel and don't want to port them into VBA) and also again for performance.


I think I might try pivot tables as an alternative to "advanced filter" to get distinct values. I guess it will be more performant.

Regards
Hasan
 
"Scripting.Dictionary" may also be viable alternative to get distinct values. Though, like Advanced Filter, will require the source workbook to be opened.
 
You must log in or register to reply here.
Back
Top