Exporting sql query in access to text

Question

I currently export the data from a passthrough query to a text using VBA in access and I would like to add to the VBA. Once it exports the data I would then like it to export the actual sql query to a seperate text. Does anyone know how to add this?

What exactly are you having a problem with? Getting the SQL? Creating a text file? — Tim Williams
– Tim Williams, Commented Aug 22, 2012 at 18:39

Fionnuala · Accepted Answer · 2012-08-22 20:46:07Z

1

You can use the FileSystemObject. The example below uses late binding, but you can set a reference to the Windows Script Host and use the commented types.

Dim fs As Object ''FileSystemObject
Dim tsOut As Object ''TextStream

sFileOut = "z:\docs\FileOut.txt"

Set fs = CreateObject("Scripting.FileSystemObject")
Set tsOut = fs.OpenTextFile(sFileOut, 8) ''ForAppending
''Or
'' Set tsOut = fs.CreateTextFile(sFileOut, True) ''Overwrite

sSQL = CurrentDB.QueryDefs(qry).SQL

tsOut.WriteLine sSQL

tsOut.Close

answered Aug 22, 2012 at 20:46

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KFleschner · Accepted Answer · 2012-08-22 19:00:43Z

0

If you have the exporting string to a text file figured out, you can get the SQL text of a query from:

Dim strSQL as string
strSQL = CurrentDb.QueryDefs("Your query name here").SQL

answered Aug 22, 2012 at 19:00

KFleschner

4993 silver badges13 bronze badges

Collectives™ on Stack Overflow

Exporting sql query in access to text

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related