-1

I have an Access database that i need a complete dump of into Excel to import into another data source.

Is there a way i could run a SQL query using "Get External Data" function from Excel and if so what should the query look like?

PeanutsMonkey
  • 6,919
  • 23
  • 73
  • 103
  • Access has ordinary facilities that can accomplish this. Read your user guide. Look for "Export to CSV," or something similar. – Robert Harvey Jun 09 '14 at 18:47
  • @Robert Harvey - Unfortunately i can't use this method as we don't have Microsoft Access installed. The database is updated using a third party interface that offers no ability to dump the entire database. – PeanutsMonkey Jun 09 '14 at 19:04

2 Answers2

2

Try using Microsoft Query and access Access, http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx. There's an option to "View data or edit Query in Microsoft Query".

enter image description here enter image description here

Ola Ekdahl
  • 1,484
  • 1
  • 11
  • 21
  • I did that however i can only select a single table at a time hence the question for a SQL query. – PeanutsMonkey Jun 09 '14 at 19:08
  • I only have Excel 2013 installed so maybe it has changed since 2010, but I can select from more than one table when I go through the Microsoft Query Wizard. There's also an option to "View or Edit Query" at the end of the wizard. – Ola Ekdahl Jun 09 '14 at 19:15
1

This is for a SQL database, however you can use the same principal and just change the connection string to Access. Then, you would loop through the Tables collection, and within that loop you would loop through the Fields collection using something like this.

Also, have a look here and see the syntax used to loop through the Access tables collection using ADOX from Excel.

Community
  • 1
  • 1
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117