CODEWITHSUNDEEP
vbapowershellms-access
cptcherry
cptcherry

Reputation: 53

Using VBA OR PowerShell to export all MS Access SQL queries to text files

Wishing to export the underlying sql of ~200 queries from access 2013 to separate txt/*.sql files - not the data that the queries collect.

This q/a provided a suitable answer: Using VBA to export all MS Access SQL queries to text files but I was not able to get it working to generate the output files. I'm a vba novice, but require to get this working as a once off. It would save a lot of time.

I also looked here at using a PowerShell script: Export all queries of an access database with powershell As similar, I was not able to get it to generate the output files.

I don't mind which process I use, I just need the sql :)

Dim db As DAO.Database
 Dim qdf As DAO.QueryDef

 Set db = CurrentDB()
 For Each qdf In db.QueryDefs
   Debug.Print qdf.SQL
 Next qdf
 Set qdf = Nothing
 Set db = Nothing

$dbe = New-Object -com DAO.DBEngine.120
$db = $dbe.OpenDatabase("C:\Users\Public\Database1.accdb")
$queries = $db.QueryDefs
ForEach ($query in $queries) {
    $name = $query.Name
    If (!$name.StartsWith("~")) {
        $name
    }
}

VBA: No error messages are returned, but I suspect it's missing an output component of the code.

PowerShell: No error messages are returned but again, missing an output component of the code.

Appreciate your help!

Upvotes: 0

Views: 1002

Answers (1)

Erik A
Erik A

Reputation: 32642

Let's adjust the VBA solution. Step 1 would be to declare a function that takes input, and writes it to a text file:

'Writes content to a text file
'Note: encoding = UTF-16, overwrites without prompt
Public Sub ToTextFile(path As String, content As String)
    If Dir(path) <> "" Then Kill path
    Dim f As Integer
    f = FreeFile
    Open path For Binary Access Write As #f
    Put #f, , content
    Close #f
End Sub

Then, we can easily adjust your current code to put each query in a separate text file. I'm going to use the path of your database and the name of the query as the location of the text file:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

 Set db = CurrentDB()
 For Each qdf In db.QueryDefs
   If qdf.Name Not Like "~*" Then
       ToTextFile CurrentProject.Path & "\" & qdf.Name & ".txt", qdf.sql
   End If
 Next qdf
 Set qdf = Nothing
 Set db = Nothing

Upvotes: 1

Related Questions