Export multiple queries to different tables

Question

I need to export; Multiple queries to different tables of ms access database from ms sql.

I know it is possible by taking each query and export it to a single table and repeating the same for different queries.

What i want to know is... can it be done in one stretch?

suppose there are three queries and each query output need to be in different tables ... in that case is it possible to do with a single export ?

Answer 1

I'm not sure what you mean. You could put all the SQL statements into one command batch rather than executing each one separately, e.g:

insert into Table1 select ...
insert into Table2 select ...
insert into Table3 select ...

Answer 2

Are these passthrough queries or Jet (Access) queries? If they are Jet queries, you should create make table queries and run them as a batch through VBA. The make table queries themselves can be constructed easily enough in VBA, if you prefer. Here is an example that gets the connect string from a linked SQL Server table, but you can fill in your own connection string, it should have the same format as the connect property of a linked table.

Dim db As Database
Dim strSQL As String
Dim strConnect As String

Set db = CurrentDb
strConnect = db.TableDefs("dbo_test").Connect

strSQL = "INSERT INTO NewAccessTable (ID, Descr) " _
& "SELECT ID, Descr FROM [" & strConnect & "].[test] "

db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

Answer 3

You can save the individual exports as SSIS packages, then combine them into a single package.

The exports might even be able to run in parallel.