Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Programmatically Generate Charts (Access2K, Win2KPro)

    I have an Access db with several tables and would like to programmatically create, say, Excel Charts using VBA/SQL rather than assembling saved queries in Access. I can record a macro that follows the steps for creating a datalink between Excel and Access for a stored procedure, but this is not optimal as I want to run SQL on the fly to dump the data. I'd also like to use Excel to store the actual SQL for each chart to make it easier to maintain/follow the logic of data generation. Access doesn't support conveniences like Tree View in the Query window, which makes it difficult to the point of near impossiblity to track lots and lots of queries -- which is why I'd rather just set the queries up programmatically.

    So, the basic question is, how does one, in Excel, declare a connection to Access, open specific tables, run SQL on the tables and return the results in a predictable way (say, to a named range) in a given worksheet? I'm trying to do in Excel what is fairly easy to do in ASP, that is, declare an ADO connection with Access, run SQL and return results.

    TIA

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    What you envision can be done either in Excel or in Access using Automation. We routinely do it from Access, and then create charts in Excel from the data returned from a qeury. The ability to use forms in Access to specify which queries to use in a given situation make it easier to maintain (IMHO), and I can turn the process over to a user who is not trained in designing queries. But you should also be able to do it the other way as well with User Forms in Excel. Unfortunately in Office 2000, the ADO capabilities are pretty limited; Office 2002 and 2003 are much more robust. In 2000, I would probably use ODBC and DAO to do the things I wanted to do.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    OK, a few things:

    ODBC means setting up a System DNS and referring to that in a connection string, right? I have no issue using that in this case as the project is destined to work on a specific machine and I'll be able to set up/maintain a System DNS just fine. (In ASP I use DNS-less connection and ADO because I don't have access to the webserver). Basically, I'm not too concerned with the technology; just want to create a connection.

    I thought I might be able to use Excel and some kind of SQL generation function to grab the basic table data from Access, run whatever Queries I need and output the result as needed to support charts -- I am not interested in creating lots and lots of queries in Access to just datalink into Excel, and I found a few potentials in Excel for starting in on this. One problem is I keep having to dance with MSQuery, which may or may not be all that bad, but it's a bit mysterious to work with (I generated a query and MSQuery makes up a name for the output column -- ?? but I'm certainly not doing "something").

    I'm thinking of a front-end userform in Excel and, ideally, a lot of VBA code to grab the data from Access and output it to predefined places in a Workbook & that's where I was asking about VBA/Excel db connection logic and output. Once I can get an array into a spreadsheet from code, I will have gotten a lot of the way to the general solution that I can build a nice UI Form to manipulate. At that point the queries will all be hidden in code and the results will be from drop-downs and buttons, which is peachy.

    Do you have any experience/ideas to suggest to do the following:

    In Excel, write the VBA to:
    1) Establish a connection with Access
    2) Execute a SQL statement (and the syntax)
    3) Output the result to a predefined part of the workbook

    I think it should be doable and hopefully someone has already done a little work in this and may be able to be a 'beacon in this night of darkness', so to speak <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    TIA!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    Look up the CopyFromRecordset method of the Range object in Excel VBA. I have attached some code I once used; it uses DAO but could probably be adapted for ADO.
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    Hans has done more in this area than I have when using Excel as the client - I almost always use Access as the client and push things out. It's largely a matter of how you get started with Automation, though I worked extensively in Excel a number of years ago. Give his attachment a go - it looks like it should get you well down the road.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    I tried using the code in an Excel module and it says dbs as Database is not a defined function. is this Excel VBA or Access VBA code? If Excel, is there a reference that I need to set?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    As I wrote, it uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programmatically Generate Charts (Access2K, Win2KPro)

    thanks! i now have Excel outputting charts from Access data and can more easily organize all these queries externally. now for a whole lot of slogging...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •