Automating Access Query from Excel

[energman58]

I have some Access databases that have millions of records in them, I need to do some data analysis which will require me to run thousands or queries on the database and import the resulting data into Excel where I will do the analysis. As I have other plans for the next few years I would like to automate this process from Excel (as I know almost nothing about Access).

I have figured out what the SQL query should look like but what I need to know is how to get Excel to open the database and run the query and then return the answer. Can anyone point me in the correct direction please - all the references I have found so far are the other way around, that is using Access to control Excel.

Thanks

 

[SydneyGeek]

I'm guessing that they might be functions, but that you don't have the code for them in your database.
Alternatively they are mis-spelled field names.

Check to see whether they are meant to be fields. If not, did you copy the query from elsewhere?

Denis

 

[villy]

Actually they are UDF's present in the database module.
They are only checking whether the fields are null to be substitute to "" or not.
Let me chck the probable cause and let you know then.
Thanks anyway

 

[SydneyGeek]

If you're calling the query from Excel you can't use functions that Excel doesn't recognise. That includes:

Several Access - specific functions: Nz() is one that has caused me trouble before.
Any UDFs defined in the Access database.

You'll need to redo the query to use Iif instead of a custom function.

Denis

 

[villy]

Thanks for that it really helps.
I think I already solved the problem by changing IIf() into UDF's.

Cheers