Excel Macro linked with Access

[lolatu2]

Is there a way to use a macro in Excel to create or edit a query in a linked Access database?

 

[Luke M]

No, XL can not modify or create queries within the Access database. XL can run it's own set of queries from the database, however.

 

[lolatu2]

Then is the linked database limited to Excel's database limits (1 million rows, etc.)?

 

[Hui]

Lolatu2


Using VBA Excel can modify its own queries retrieving data from an Access or any other DB


Yes, Excel is limited to retrieving 1,048,575 records and 1 Header row at a time and if required you may need to structure your queries accordingly


You can get around this limit by using Power Pivot:

Refer: http://www.powerpivot.com/

or

http://chandoo.org/wp/2010/01/08/powerpivot-review/