VBA-Syntax Error While Printing SQL Text of all Queries (1 Viewer)

[PKJ]

Hi EveryBody,

I am trying to write the SQL-text of all my queries to a text-file using the following two functions -

Sub Query_Print()
'This procedure would write SQL-text of all queries in the current database to a text file
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     Dim qryString As String
     Dim x As Long
     Dim flName As String
     Dim flPath As String
     Set db = CurrentDb()
     flPath = CurrentProject.Path & "\" & db.Name
     flName = flPath & "_QUERIES_SQL-Text.txt"
     qryString = ""
     x = 1
    For Each qry In db.QueryDefs
        qryString = qryString & x & ". " & qry.Name
        qryString = qryString & ":" & vbCrLf & vbCrLf & qry.SQL & vbCrLf & vbCrLf
        x = x + 1
        'WriteToATextFile (qryString)
    Next qry
    [COLOR="Red"]WriteToATextFile (flName, qryString)[/COLOR]
    End Sub

Sub WriteToATextFile(MyFile As String, AppendThisString As String)

'set and open file for output
fnum = FreeFile()
Open MyFile For Append As fnum     

Print #fnum, AppendThisString
Close #fnum
End Sub

The file named for writing above in flName/MyFile variable exists at the relevant path.
Access shows syntax error in the code line put in red above. Any help on this error would be highly appreciable. Please Help.
- P K Joshi

 

[ajetrumpet]

it shows a SYNTAX error? as in, the line is simply highlighted ? can you CALL the sub? I don't think you can actually, but did you try it?

 

[PKJ]

Thanks a lot Adam, your advice for using CALL has worked fine. I had to remove the db. Name part in File path/name (flPath) as it included extension of the Access database and thus showed - bad file name.

Earlier I had used this sub without CALL statement and it worked well, after adding a second parameter of MyFile to sub WriteToATextFile, it started showing the syntax error.
You have helped a lot. Many many thanks. - P K Joshi

 

[c_smithwick]

If you are calling a sub without the "Call" statement, you do not need to enclose the parameters in parenthesis. I suspect that may have been the source of your syntax error.

 

[DCrake]

Here is a link to a sample database that does all the documentation for in a wizard.

 

[PKJ]

Thanks smithwick for adding to my knowledge of VBA. But when to CALL and when not to CALL a sub?

Many thanks to DCrake for the nice utilty Download.Documenter - had I got it earlier I would not have written the query_print procedure. The utilty improved and suggested by you is far-far good and contains the code to reload the queries/modules etc into the project - a need that had arisen for me and your link is just like GOD-SENT. Thanks Again.

Regards to all members of this forum who contribute much to novices like me.

- P K Joshi