Export All Ms Access SQL Queries to Text Files

Export all MS Access SQL queries to text files

This should get you started:

  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

You can use the File System Object or the built-in VBA File I/O features to write the SQL out to a file. I assume you were asking more about how to get the SQL than you were about how to write out the file, but if you need that, say so in a comment and I'll edit the post (or someone will post their own answer with instructions for that).

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

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

How do you export MS Access Query Objects to text file

The value of the acQuery constant is 1. (AcObjectType Enumeration)

Perhaps your results are because the code is using 6 instead of 1. I don't know what should happen in that situation because none of the AcObjectType constants have a value of 6. Using Access VBA, when I tried SaveAsText with 6, something strange happened: the output file was created but Windows denied me permission to view its contents; shortly later, a dialog box appeared which looked like Access was looking for something on SQL Server ... although the query definition I was saving does not involve SQL Server. Strange stuff!

Import previously exported Access SQL queries from text files

You can use a VBA procedure to modify both your query names and their SQL as needed. That approach should be much simpler than dumping the query definitions to a text file, doing search and replace in the text file, and then (somehow?) modifying your queries based on the text file changes.

For example, using the procedure below, you can do a "find/replace of BNK30 with BNK31" like this ...

ModifyQueries "BNK30", "BNK31"

However as written, the procedure does not change the queries. It only shows you the changes it would make if you enable the .Name = strNewName and .SQL = strNewSql lines. Please review the output in the Immediate window before enabling those lines.

Public Sub ModifyQueries(ByVal pFind As String, ByVal pReplace As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strNewSql As String
    Dim varNewName As Variant

    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        With qdf
            varNewName = Null
            strNewSql = vbNullString
            If .Name Like "*" & pFind & "*" Then
                varNewName = Replace(.Name, pFind, pReplace)
                Debug.Print "change " & .Name & " to " & varNewName
                '.Name = strNewName
            End If

            If .SQL Like "*" & pFind & "*" Then
                strNewSql = Replace(.SQL, pFind, pReplace)
                Debug.Print Nz(varNewName, .Name) & " SQL: "
                Debug.Print strNewSql
                '.SQL = strNewSql
            End If
        End With
    Next
End Sub

Beware that code has not been thoroughly tested. It is intended only as a starting point; you must test and refine it.

You should add error handling. The procedure will throw an error if/when it attempts to name a query with a name which matches an existing query or table.

Note, I wrote that procedure to rename queries. If you prefer to create new queries instead, revise the code to do this ...

db.CreateQueryDef varNewName, strNewSql

Finally make sure to backup your database before running the "enabled" version of that code. I doubt you need that warning, Nathan, but I cringe at the thought of anyone else inadvertently hosing their queries.

Can I export from a query in access to a text file without wrapping strings in quotes

Method 1

You have to add manually a schema.ini in the directory you wish to export

In your case, it should contain :

TextDelimiter="none"

---

Method 2

Another way to do it is to use the TransferText method, with :

SpecificationName Optional Variant. A string expression that's the
name of an import or export specification you've created and saved in
the current database. For a fixed-width text file, you must either
specify an argument or use a schema.ini file, which must be stored in
the same folder as the imported, linked, or exported text file. To
create a schema file, you can use the text import/export wizard to
create the file. For delimited text files and Microsoft Word mail
merge data files, you can leave this argument blank to select the
default import/export specifications.

for your export specification, which is a oneshot operation, you will use the wizard and there you have an "advanced" button bringing a menu where you can set the text delimiter to nothing.

---

Google is your friend. You've got enough clues now to sort it out.

---

Related Topics