how to export two MS ACCESS table into one csv file without getting overwritten?

Question

I am trying to export two MS Access table into one .csv file. I am not able to do so, i am able export these two table into two different csv files. I need both tables to get exported into one csv file. Both tables having different numbers of columns.

Table1 has follwing columns

f1, f2, f3, f4, f5

table2 is having

t1, t2, t3, t4

I am trying to import through vb in this manner but not able to append two tables into this query. I tried to import in this following manner

DoCmd.TransferText acExportDelim, , "table1", "t1.csv", True

DoCmd.TransferText acExportDelim, , "table2", "t1.csv", False

But i am getting file with records of table2 only. I am not able to append two tables into one export query so that i can get desired result

From table1 i want headers only and from table2 i need only data to be exported. Is there any way in MS Access i can get headers from table1 and records from table2 into one csv file

Answer 1

Great question! A lot of times I will create a query first, which contains all the output, just like I want in my final output file. Then I will use a single DoCmd.TransferText command to output my query to the CSV file.

If you are joining data from two tables together, you will use what is called a Union Query. This can be a little more confusing to create because you need to type in the SQL yourself instead of dragging and dropping in the visual query editor. But once you get the hang of it, it is not as complicated as it might seem.

I am going to make some assumptions here, and give you an example of how this might work. Let's say table1 only has one row, and these are the headers you want in your output file:

select f1 as t1, f2 as t2, f3 as t3, f4 as t4, f5 as t5
from table1
union select t1, t2, t3, t4, '' as t5
from table2

The key factor is that both tables we are merging together in this union query are using the same column names. That's why we use f1 as t1. This makes the f1 column use t1 as its column name (commonly called an alias).

Also notice how in table2, we don't have a t5 column, so we just use an empty string and alias it as t5. This allows the columns to match what we have in table1, even though table2 has no data for this column.

When you view this query, you will notice that the header row is in a random place in the query results. We want to make sure it ends up as the top row in the query, so let's add another column so we can sort the results and force the header to the top row.

select f1 as t1, f2 as t2, f3 as t3, f4 as t4, f5 as t5, 0 as mysort
from table1
union select t1, t2, t3, t4, '' as t5, 1 as mysort
from table2
order by mysort;

Beautiful! Now the header is in the top row! Now we can run our export command, and dump the results into a CSV file:

DoCmd.TransferText acExportDelim,,"qryExample","myfile.csv", False

If you don't want the sort order column in your output file, then simply nest the union query inside another query where the mysort column is not included.

Hope this helps!