SQL*XL: Excel Access Query |
|
To retrieve data from an Access database into Excel is easy. You need to execute a query - a select statement from the SQL dialog. In most cases you can simply type the following select statement which will export an Access table into Excel:
select * from my_table;
You can even execute a saved query in Access. Simply use the name of the saved query as table name in the select statement
select * from my_query
If you suspect that the table may be large (larger than a few thousand records) you may wish to restrict the query. You can use the following syntax to just retrieve 10 rows (an arbitrary 10):
select top 10 * from my_table;
To retrieve the top 10% of a table you can use the following syntax
select top 10 percent * from my_table;
Table Names and column names:
To find out which tables you can query please use the Describe dialog.
Also use the Describe command to find out the column names in a table. Executing the describe command will display the list of columns of a table or view: desc mytable;
To quickly dump the contents of a table you can use the Actions menu in the SQL editor. Select the table of interest and right click. The actions menu is displayed. You can choose to let SQL*XL generate the SQL to dump the table into Excel or to sample some records only.
To see how many rows a table or view contains use the syntax:
select count(*) from mytable
See also: