I'm already trying to work by exporting my data to excel and using an excel macro to create the chart.
But now I had another problem which is that, I'm allowing my users to check on some checkbox inorder to select certain fields.
e.g. (x) id (x) lastName ( ) firstName (x) position
select id, lastName, position from tmpTable;
Currently my program will then create the sql statement and amend the sql statement of the chart. (No query is created, only sql staetment is used)
But now that I'm exporting the data, is there any build in functions that I can use to export the data? Or do I have to use recordset and the "Open file" statement to download the data?
oks, you can try using transferspreadsheet function.. and you can pass an sql statement to this function instead of a table or query name, the result will be exported to excel.
if you are going to use macros in excel to create the charts, do you think pivot table can help? you need not have the data in excel, it can pull directly from the database. just that u may need to have the queryin the db.. for me, pivot tables are neater and require less programming..
Had tried your method using the transferspreadsheet function, but its giving me "The Microsoft Jet database engine could not find the object SELECT id FROM tmpTable;. Make sure the object exists and that you spell the path & name correctly."
As for using the pivot table, I have no idea how to use it!
With the pivot table, I will be able to export the chart?
hey oks, sorry. I just checked and no, cannot simply specify SQL statements, have to use query. but you can create/update an existing query (basing on SQL statements) right? update the query, save it and then export using transferspreadsheet.
you can search for help in excel about pivot tables and chart, it's easy.