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

Question

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!

Erik A · Accepted Answer · 2019-06-19 07:23:34Z

1

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

answered Jun 19, 2019 at 7:23

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

5 Comments

cptcherry Over a year ago

A likely silly question - but are these two sections in the one 'module' or in two different modules? I tried it both ways, but when I click run, it launches the select macro box, which shows up as empty.

Erik A Over a year ago

Eh... you need to wrap the second piece of code in a sub, e.g. Public Sub SomeName(), then the code, then End Sub. I copied it from the question, I assumed you were already doing that. It doesn't matter if it's one or two modules. I do recommend taking a basic VBA tutorial, pasting code without knowledge of what it's doing is tricky business.

cptcherry Over a year ago

Yes, that's right - it was wrapped in public sub/end sub. I'm getting a bad file name or number error - I suspect it's due using ' * ' in some of my query names.

Erik A Over a year ago

Yup, either adjust your query names or write a function to sanitize them. Note that characters like that can cause all kinds of trouble when referring to queries by name in SQL, VBA, etc.

cptcherry Over a year ago

Fixed up the names - and works a treat - thanks for your help! Yes, I've realised that it causes all sorts of problems, file names, excel sheet names etc.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related