What is the fastest way to move large amounts of data from an Access database to an Excel chart?

Question

So far the fastest way I've found it to open an ADO recordset, save it as XML, and then open the XML file in Excel. I think it would probably be even faster to go from Access to text file (maybe CSV) then open that in Excel. But I'm not sure how to quickly go from Access to a text file. I don't see a way to save from ADO to a text file. Does anyone know of a way without writing my own code to write to the text file in a loop?

This doesn't have to be pretty. I'm using a VB program to graph the data in a chart and I just need to move the data quickly from Access to the invisible spreadsheet behind the Excel chart.

Any help is appreciated.

Answer 1

Just set up a macro that exports to excel. You will want to use the TransferSpreadsheet command within the macro.

'The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic. For more information on how the action and its arguments work, see the action topic.

Syntax

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]
'

Good luck!

Answer 2

Another way you might consider is to create a link to the data using the menu path Data/Import External Data/New Database Query, then choose your access database and the table or query results you want to graph. Basically just follow the prompts to pick the db and table you want. Create your graph from the new data. This will leave you wth data in your Excel sheet that can be updated when the underlying data is updated. Anyway, just another way to easily transfer the data to Excel and update as needed.

Answer 3

it would be silly to save each and every thing in a spreadsheet as any expert might inform you. MS get entry to is for records and Excel is for calculating. You do might desire to be attentive to something approximately relational databases with the intention to make a pragmatic layout or you will no longer get the full income on MS get entry to. this suggests you will need some sq. know-how. in fact MS get entry to isn't the final selection for relational databases and different sq. structures are greater robust. Excel to MS get entry to or different sq. tables isn't a difficult conversion.