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

 

[energman58]

Ah - I just looked at what you have to do to make SQL queries run in VBA - this looks very hard and as I dont understand SQL this is probably beyond my level of competence. Anyone got any suggestions of an easier solution to automate getting data out of these huge (and badly designed) databases and into Excel?

Thanks

 

[SydneyGeek]

See if this tutorial helps.

Denis

 

[chuckles1066]

Ah - I just looked at what you have to do to make SQL queries run in VBA - this looks very hard and as I dont understand SQL this is probably beyond my level of competence. Anyone got any suggestions of an easier solution to automate getting data out of these huge (and badly designed) databases and into Excel?

Thanks

I did it this way (as Frank Sinatra might have once sang):

Set db = CreateObject("Access.Application")
db.Visible = False
db.OpenCurrentDatabase (dbloc)
db.DoCmd.RunMacro "mcr_import_data"
Application.StatusBar = False

The macro in Access contains the queries I want to run.

 

[energman58]

Wow - this board is great. Answers in less than an hour that do the job!

Denis

Thanks for this - it seems to be perfect for what I want to do which is to use Excel to drive the Access DB .

I presume that the line (from Part 5 of the Tutorial):

sSQL = "SELECT * FROM tblPopulation WHERE Region ='" & ShDest.Range("K1").Value & "'"

Can be extended to include multiple criteria in a single statement - like this for example:

sSQL = "SELECT * FROM tblPopulation WHERE Region ='" & ShDest.Range("K1").Value & "' AND IndependenceDate >'" & ShDest.Range("L1").Value & "'"

And so on? If so I can do exactly what I want to do using this code without too much faffing about. Is the syntax I deduced correct here?

If I want to add more than 2 criteria would you happen to know what is the syntax is for that - SQL seems to "nest" criteria so you get something that looks a bit like this:

WHERE Type ="Car" AND (Brand ="Ferrari" AND Colour="Red")

I have no idea how to translate this into the correct format for this ADO

Many thanks

Peter

Chuckles

Thanks for the suggestion - what I was struggling with was how to get Excel to actually write the SQL query so that it could be dynamic (which seems to be what the ADO option allows) rather than executing stored queries.

What I need to do is to query a database that has a hundred thousand+ customers in it with data for each entered roughly each month for 5 years and I want to be able to run queries that return data to excel that let me analyse how the population is changing but at the moment I am not sure what criteria I need to look at ahead of time - maybe I need to look at the distribution of customer purchases in a month over time and I need to make sure I only look at active customers as customers who have left are not identified in the data - they just return no data. So I want to build an application that lets me do this in Excel fetching the data from the Access database and I can see I am going to have to automate this so I can extract data in sufficiently small chunks to analyse the distribution - hence the wish to do it in Excel.

Thanks

Peter

 

[SydneyGeek]

Hi Peter, yes you can make the queries dynamic. Just watch out for dates.
1. They need the # symbol around them to mark them as dates. Using single quotes will get them treated as text.
2. When you use VBA to write SQL the dates will ALWAYS use the US date formats, regardless of your regional settings. Because of that I tend to use the CLng function to convert the date to a serial number, and insert that into the query.
3. For multiple criteria the syntax is WHERE Type = 'Car' AND (Brand = 'Ferrari' AND Colour = 'Red'). Note that for text criteria, single quotes are probably the easiest to use. Otherwise you will need to double up the quotes to prevent errors.

Denis

 

[energman58]

Denis

Thanks for this - so if you convert your date to a serial number the SQL query will work OK?

So if you had

BigDate= CLng(ShDest.Range("L1").Value)
SmallDate= CLng(ShDest.Range("M1").Value)
sSQL = "SELECT * FROM tblPopulation WHERE InvoiceDate> SmallDate AND InvoiceDate< BigDate"

It would select records between the two


And


sSQL = "SELECT * FROM tblPopulation WHERE InvoiceDate> SmallDate AND (InvoiceDate< BigDate AND PaymentDate>BigDate)"<BIGDATE)"< p>
Would use both the AND statements to select records?

Or have I got to add in a series of quotes and/or ampersands around the variables to make it a valid SQL query?

Sorry for being a dope but as I said I have zero knowledge of SQL and I am not clear what the various bits of

'" & ShDest.Range("K1").Value & "'

In the example file are doing!

 

[energman58]

Denis

I got all the logical ANDs working with no problems (there are only a finite number of combinations of brackets, ampersands and inverted commas to try before you figure out what works!)

Can you use other operators other than straightforward mathematical ones - for example:

IS NULL
IS NOT NULL
Like "A*"
NOT

? Is there a handy list of the allowed operators somewhere helpful?

Thanks for all your assistance; your tutorial was really helpful - great website by the way with tons of useful stuff. You (and the Mr Excel board) just saved me months of effort (again)!

Regards

Peter

 

[SydneyGeek]

Hi Peter,

All of the operators you listed are valid. I also use BETWEEN and IN for building queries.
IN is great where you may have multiple items in a list, and want to filter by all of them:
SomeID IN (1,3,5,6,7,12,26)
or
Fruit IN ('Apple','Banana','Grape')

Here is a tutorial on logical operators: http://www.databasedev.co.uk/logical_operators_in_sql.html
Also: http://eis.bris.ac.uk/~ccmjs/acc97-r5.htm#ops
And a good general tutorial on SQL / VBA: http://www.fontstuff.com/access/acctut15.htm

And thanks for the feedback on the site. It's good to know when people find it useful

Denis

 

[villy]

Hi Mr. Denis
I have an sql saying error: undefined 'propertext' and 'propervalue'..
what can I substitute to these functions?
Thanks