import queries

[zizerzizer]

Hi. How to import this exported queries:


Code:

Sub ExportQueries()
Dim db As Object
Dim qdf As Object
Dim ff As Long

Set db = CurrentDb
ff = FreeFile()

Open "C:\Queries.txt" For Output As #ff

For Each qdf In db.QueryDefs

Print #ff, "Query: " & qdf.Name & vbCrLf
Print #ff, "SQL:" & vbCrLf
Print #ff, qdf.SQL & vbCrLf

Next qdf

Close #ff
End Sub


I need to change data and import again to Microsoft access.

 

[Joe4]

Welcome to the Board!

Big picture, what exactly are you trying to do?
Are you trying to export the results of a query, and then import as a new table?
If so, it would probably be far easier to use a Make Table Query than to do this.

 

[jackd]

I agree with Joe ---What are you trying to accomplish?

I worked with your code as an exercise and limited the queries to a few in my database.
These routine will export queries to a .txt file and import them from the txt file to the current database and will suffix the query name with "_X"

' ----------------------------------------------------------------
' Procedure Name: ExportQueries
' Purpose: Routine to export some queries to a specified .txt  file
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 05-Jan-22
'=============================
'see ImportQueriesFromText
'From https://www.mrexcel.com/board/threads/import-queries.1191851/
' ----------------------------------------------------------------
Sub ExportQueries()
    Dim db As Object
    Dim qdf As Object
    Dim ff As Long
    Dim i As Integer
    Set db = CurrentDb
    ff = FreeFile()

    Open "C:\users\jp\documents\Queries.txt" For Output As #ff

    For Each qdf In db.QueryDefs
        If qdf.name Like "B*" Then
            i = i + 1
            Print #ff, "Query: " & qdf.name & vbCrLf
            Print #ff, "SQL:" & vbCrLf
            Print #ff, qdf.SQL & vbCrLf
        End If
    Next qdf
    Debug.Print "Queries exported: " & i
    Close #ff
End Sub

' ----------------------------------------------------------------
' Procedure Name: ImportQueriesFromText
' Purpose: Import the queries that were previouslyexported to Text
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 05-Jan-22
'see ExportQueries
' ----------------------------------------------------------------
Sub ImportQueriesFromText()
10    On Error Resume Next
          Dim db As Object
          Dim qdf As Object
          Dim ff As Long
          Dim i As Integer
20        Set db = CurrentDb
30        ff = FreeFile()
          Dim sbuf As String
          Dim qname As String
          Dim qsql As String
40        qsql = ""
50        Open "C:\users\jp\documents\Queries.txt" For Input As #ff
60        Do Until EOF(ff)
70            Line Input #ff, sbuf
80            If sbuf Like "query:*" Then
90                qname = Trim(Mid(sbuf, 7, Len(sbuf)))
100               Debug.Print qname
110               GoTo ReadNext
120           End If
130           Select Case sbuf
                  Case vbCrLf, ""
                      'Do nothing
140               Case "SQL:"
                      'Do nothing
150               Case Else
160                   qsql = qsql & " " & sbuf
170                   If Right(qsql, 1) = ";" Then
180                       Debug.Print qsql & vbCrLf
190                       i = i + 1
200                       qdf = CurrentDb.CreateQueryDef(qname & "_X", qsql)
210                       qsql = ""  ''reset qsql
220                   End If
230           End Select
ReadNext:
240       Loop
          
250       Debug.Print "Queries imported/created from txt: " & i
260       Close #ff
End Sub