How to merge two sql select queries data in to a single .csv file using Macros

Question

How to merge two sql select queries data in to a single .csv file using Macros?( with Delimiters)

I have two SELECT queries, 

The date inside .csv file should be like this :

Row1 : < date from 1st query?

Row2: <date from 2nd query>

.

.

.

.

.

Row n: 

---

Manoj Nagasarapu

Answer 1

Do the tables have the same fields and structure? If so you should be able to export in two queries:

Dim cnn As New ADODB.Connection
Dim strSQL As String

cnn.Open _
   "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Users\...\Documents\My Database\Access\Northwind.accdb;"

strSQL = "SELECT * INTO [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text;].[ExportData.txt] FROM [Table1]"
cnn.Execute strSQL
strSQL = "INSERT INTO [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text;].[ExportData.txt] SELECT * FROM [Table2]"
cnn.Execute strSQL

cnn.Close
Set cnn = Nothing

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Hi Manoj Nagasarapu,

You had asked,"How to merge two sql select queries data in to a single .csv file using Macros?"

I suggest you to use 'UNION' to merge the result of 2 queries.

Save that query.

Then you can use 'DoCmd.TransferSpreadsheet Method' to export data.

Example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"

Reference:

Use a union query to combine multiple queries into a single result

DoCmd.TransferSpreadsheet Method (Access)

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi Manoj Nagasarapu,

Is your issue solved?

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].