ms Access import table from file in a query

Question

Hello, Is there a way to have a msAccess DB query import a table from file?

Answer 1

Yes, as long as the data is organized. You can use VBA or a macro with TransferText or you can use Get External Data from the menu or ribbon, which will guide you through the steps.

EDIT

You can import into a new or existing table from say, CSV, like so:

SELECT * INTO NewTable
FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Docs].Test.csv

Answer 2

The solution will vary depending on the format of the file. If it's simple enough, checking out the options on the External Data tab (MS Access 2007) in the Import section, may do the trick.

For complex integrations, I'll often use SQL Server Integration Services (SSIS) to migrate the data into Access, where I can then process it with SQL queries. Of course, SSIS is a much "heavier" solution with a bit of a learning curve, but its been handy when the wizards aren't flexible enough.