Creating a Query with CreateQueryDef Failing due to table/object being read only

Question

I modified a piece of code to create a Query from a line of SQL code and export it to a text file.

I had previously had this working but after trying to implement this in a larger subroutine it now gives an error stating that the table/object cannot be updated as it is read only (error 3027).

This occurs whether the code is executed by itself or from within other code.

Please find the export code below:

Public Sub exportQuery(exportSQL As String, FileName As String)

    Dim db As DAO.Database, qd As DAO.QueryDef
    Dim i As Integer
    Dim InitialFileName As String
    Set db = CurrentDb
    'Check to see if querydef exists and delete it if it exists  
    For i = 0 To (db.QueryDefs.Count - 1)

        If db.QueryDefs(i).Name = "tmpExport" Then
            db.QueryDefs.Delete ("tmpExport")
            Debug.Print "Deleted"
            Exit For

        End If

    Next i
    Debug.Print "This far"

    Set qd = db.CreateQueryDef("tmpExport", exportSQL)

    'Set intial filename to default if none is chosen
    If (FileName <> "") Then
        InitialFileName = "export_" & Format(Date, "mmddyyy") & ".csv"
    Else
        InitialFileName = FileName   
    End If

    'Write the query results to a File
    DoCmd.TransferText transferType:=acExportDelim, TableName:="tmpExport", FileName:="Pathtoexport\Export" & InitialFileName, HasFieldNames:=False
    'Cleanup
    db.QueryDefs.Delete "tmpExport"
    db.Close
    Set db = Nothing
    Set qd = Nothing
    Debug.Print "ExportQuery" & vbCrLf
End Sub

Also Please find the SQL in question below:

Select phone_number FROM Master WHERE list_id IN ('230');

• The Master Table both exists and is not open in Access.

• The SQL is valid and produces results when manually applied.

• The tmpExport Query does not exist prior to running the code.

• The code seems to be failing on the creation of the query.

If anyone could shed light as to what is causing this I would be most appreciative.

Answer 1

As Wayne correctly points out in the comments, the logic implemented in the following if statement present in your code is reversed:

'Set intial filename to default if none is chosen
If (FileName <> "") Then
    InitialFileName = "export_" & Format(Date, "mmddyyy") & ".csv"
Else
    InitialFileName = FileName   
End If

The above implies that a valid filename would result in the output path:

Pathtoexport\Export

---

You should also omit the parentheses here:

db.QueryDefs.Delete ("tmpExport")

---

However, looking at the bigger picture, I would personally avoid constructing the filepath within this function and would opt for supplying a complete filepath as the argument.

This way, the function has a single purpose:

"Export the result of the supplied SQL to a file with the supplied filename."

And you would no longer need to hard-code the parent folder into the function.

For example, something along the lines of:

Function ExportQuery(sql As String, fnm As String)
    Dim qry As String: qry = "tmpExport"

    Dim dbs As DAO.Database
    Set dbs = CurrentDb

    On Error Resume Next
    dbs.QueryDefs.Delete qry
    On Error GoTo 0

    dbs.CreateQueryDef qry, sql
    DoCmd.TransferText acExportDelim, , qry, fnm, False
    dbs.QueryDefs.Delete qry

    Set dbs = Nothing
End Function