0

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!

braX
  • 11,506
  • 5
  • 20
  • 33
cptcherry
  • 43
  • 1
  • 7

1 Answers1

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
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 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. – cptcherry Jun 19 '19 at 13:24
  • 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. – Erik A Jun 19 '19 at 13:29
  • 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. – cptcherry Jun 19 '19 at 13:52
  • 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. – Erik A Jun 19 '19 at 13:53
  • 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. – cptcherry Jun 19 '19 at 14:09