CODEWITHSUNDEEP
ms-accessvbams-access-2003
Barry
Barry

Reputation: 31

TransferSpreadSheet gives error 3027 "Cannot update. Database or object is read-only."

I'm trying to export the results of a dynamic SQL statement but keep getting the error 3027 "Cannot update. Database or object is read-only.". I'm using Access 2003. GetYearFromDirName(sFolder) is parsing out a year from a directory structure and using that as a calculated column in the SQL results.

Here is the code in question:

sSQL = "SELECT INDEXDB1.IFIELD1 AS TestArea, INDEXDB1.IFIELD2 AS TSID, INDEXDB1.IFIELD3 AS MapCoord, " _
    & "INDEXDB1.IFIELD4 AS Community, INDEXDB1.IFIELD5 AS Address, INDEXDB1.IFIELD6 AS DocNum, " _
    & "'" & GetYearFromDirName(sFolder) & "' AS Yr FROM INDEXDB1;"

'DoCmd.TransferSpreadsheet acExport, , sSQL, sFolder & "\" & BoxNum & ".csv"
'DoCmd.OutputTo acOutputQuery, "ExportRecs", acFormatXLS, sFolder & "\" & BoxNum & ".csv"

SaveToExcel sSQL, sFolder & "\" & BoxNum & ".csv"

Calls:

Public Sub SaveToExcel(strSQL As String, strFullFileName As String)
Dim strQry As String
Dim db As Database
Dim Qdf As QueryDef

On Error GoTo SaveToExcel_err

strQry = "TempQueryName"

Set db = CurrentDb
'Set Qdf = db.CreateQueryDef(strQry, strSQL)

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQry, strFullFileName, True

'DoCmd.DeleteObject acQuery, strQry

With db
    ' Create permanent QueryDef.
    Set Qdf = .CreateQueryDef(strQry, strSQL)
    ' Open Recordset and print report.
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, strQry, strFullFileName, True
    ' Delete new QueryDef because this is a demonstration.
    .QueryDefs.Delete Qdf.Name
    .Close
End With


Exit Sub

SaveToExcel_err:
    MsgBox Error & " " & Err & " in sub SaveToExcel. Close program and start over."

End Sub

Is there a better way to export dynamic SQL statement results? In the end, I need a CSV file.

Upvotes: 1

Views: 3555

Answers (1)

pteranodon
pteranodon

Reputation: 2059

You may open it in Excel, but CSV is a text format, so you need to use DoCmd.TransferText instead of DoCmd.TransferSpreadsheet. Manually go through the export once using the Export Data Wizard. As you do so, you'll wand to create and name a Export Specification. This will specify commas as the delimiter and double quotes as text delimiters. The name of the export spec you created is passed as the second argument to TransferText.

Upvotes: 2

Related Questions