Combining Access with Excel

[billyq]

Evening all

I've been using Excel at work for some years now and have now been given a piece of work where I don't think Excel alone will work.

I'm passed a spreadsheet each morning full of raw data. Each sheet contains roughly 1000 lines. I've merged all of the data and I've got 110,000 lines (much more than Excel 2003 can handle!)

What I'm trying to do is create a dynamic dashboard where users can specify parameters such as Start Date and End Date and then create graphs and show the figures to compliment the graphs.

I was originally going to do this by hiding the raw data and having a script build the dashboard each time the parameters were changed.

I'm thinking now that I'll need to have the data in MS Access 2003 (I've done this already) and then somehow have Excel feed from that.

However, the end users (of which there will be about ten) don't have MS Access installed.

I'm constrained by the fact that I don't have the freedom to install any other software/add-ins.

Can anyone think of a way of doing this? Can Excel drag the data from the Access database without having Access installed? And what happens if a user query exceeds 65,536 lines (i.e. they ask for a full years worth of data?)

Thanks in advance for any help!

[DonkeyOte]

Can Excel drag the data from the Access database without having Access installed?

Yes, using ADO/DAO you can read data into XL from an .mdb file without requiring Access be installed on the client... the Access ODBC driver is installed onto clients by default (see Control Panel -> Admin Tools -> ODBC).

Re: row limitation -- you should find lots and lots of examples of this on the web.... better to restrict your users from downloading that much data in the first instance I'd expect - ie use GROUP statements in your SQL to limit volume as much as possible... IMO XL isn't really designed for mass-MASS data regardless of what people say and / or how many rows are available in 2007.... if you need to handle that much info on a transactional basis get your bosses to install Access as a client

[billyq]

Yes, using ADO/DAO you can read data into XL from an .mdb file without requiring Access be installed on the client... the Access ODBC driver is installed onto clients by default (see Control Panel -> Admin Tools -> ODBC).

Re: row limitation -- you should find lots and lots of examples of this on the web.... better to restrict your users from downloading that much data in the first instance I'd expect - ie use GROUP statements in your SQL to limit volume as much as possible... IMO XL isn't really designed for mass-MASS data regardless of what people say and / or how many rows are available in 2007.... if you need to handle that much info on a transactional basis get your bosses to install Access as a client

Thanks for the reply, DonkyOte! I'm stuck with Excel and one install of Access at the moment (thanks to the credit crunch! )

I'd be happy to push for Access on all clients, but is there a simple way of producing charts with Access? Would it be easiest to have Access push the data to an Excel spreadsheet and then script the graph?

One more question: With the limit of rows, is it possible with ADO and SQL to just ask for summary information? i.e. How many calls were answered between 01/01/2009-31/12/2009 (without having to pull each record and exceed the 65,536 limit)?

Thanks for your help

[DonkeyOte]

To be honest I'm not an Access-junkie, I have always used MySQL as my DB and either XL/Web as client / front-end... there is an Access forum here with some knowledgable folks who I'm sure would try and help out...

Obviously XL is great for building Charts and for any Chart questions you may have there is a dedicated Charts Forum -- in which lurks Andy Pope, the very well known Chart-Guru (MS Excel MVP - http://www.andypope.info)

Back to your XL/Access questions...

In this context ADO is essentially just a means by which you can pass SQL queries etc to your DB so you can pretty much run any query you want... ie you can fire any SQL statement from XL just as you would in Access.

If you google VBA/ADO you should find plenty (and I mean plenty) of examples ... here's one quick example taken from VBAXpress: http://www.vbaexpress.com/kb/getarticle.php?kb_id=889
(note you will need to reference the Microsoft Data Access Object Library in VBEditor (via Tools -> References) - the highest version listed ... if not listed you will need to install MDAC - repeat on each client)

[billyq]

To be honest I'm not an Access-junkie, I have always used MySQL as my DB and either XL/Web as client / front-end... there is an Access forum here with some knowledgable folks who I'm sure would try and help out...

Obviously XL is great for building Charts and for any Chart questions you may have there is a dedicated Charts Forum -- in which lurks Andy Pope, the very well known Chart-Guru (MS Excel MVP - http://www.andypope.info)

Back to your XL/Access questions...

In this context ADO is essentially just a means by which you can pass SQL queries etc to your DB so you can pretty much run any query you want... ie you can fire any SQL statement from XL just as you would in Access.

If you google VBA/ADO you should find plenty (and I mean plenty) of examples ... here's one quick example taken from VBAXpress: http://www.vbaexpress.com/kb/getarticle.php?kb_id=889
(note you will need to reference the Microsoft Data Access Object Library in VBEditor (via Tools -> References) - the highest version listed ... if not listed you will need to install MDAC - repeat on each client)

That's fantastic, thanks a lot for your help. I'm not much of an Access-junkie myself, I'm just throwing myself in at the deep end.

Thanks for your time