Exporting Files

[RaisinGirl]

I am trying to write a macro to export files...and I am receiving the following message:

Cannot update. Database or object is read-only.

Yet, if I MANUALLY export the data in the table to a file, no problem!

Any suggestions would be most appreciated!

 

[hennep]

I had exactly the same problem using docmd.transfertext in code, could not find the solution.
In my case I needed to export to a fixed length file with no field headers. To help the customer I had to rewrite the transfertext method, see code below. If you find a better solution please post it in this thread.

Hennie

Public Sub ExportToFixedLenFile(sSpec As String, sTable As String, sFile As String)
' DoCmd.TransferText acExportFixed, "ExportSpecification", "qryNAW", "C:\PD" & UCase$(txtStrCBB) & ".REN", False
' ExportToFixedLenFile "ExportSpecification", "qryNAW", "C:\PD" & UCase$(txtStrCBB) & ".REN"
Dim rsData As Recordset
Dim rsSpec As Recordset
Dim rsSpecCol As Recordset
Dim lngSpec As Long
Dim sSQL As String
Dim f As Integer
Set rsData = CurrentDb.OpenRecordset(sTable, dbOpenSnapshot)
If Not rsData.EOF Then
Set rsSpec = CurrentDb.OpenRecordset("MSysIMEXSpecs", dbOpenSnapshot)
rsSpec.FindFirst "[SpecName]='" & sSpec & "'"
If rsSpec.NoMatch Then
MsgBox "Export specification [" & sSpec & "] NOT FOUND.", vbExclamation, "NAW97"
Else
lngSpec = rsSpec.Fields("SpecID&quot
End If
rsSpec.Close
Set rsSpec = Nothing
End If
sSQL = "select * from MSysIMEXColumns where [SpecID]=" & lngSpec & " order by [start]"
Set rsSpecCol = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
If rsSpecCol.EOF Then
MsgBox "Error in export specification [" & sSpec & "].", vbExclamation, "NAW97"
Else
f = FreeFile
Open sFile For Output As #f
Do While Not rsData.EOF
rsSpecCol.MoveFirst
Do While Not rsSpecCol.EOF
Print #f, Left$(rsData.Fields(rsSpecCol.Fields("FieldName&quot) & Space(rsSpecCol.Fields("Width&quot), rsSpecCol.Fields("Width&quot);
rsSpecCol.MoveNext
Loop
Print #f, ""
rsData.MoveNext
Loop
Close #f
End If
rsSpecCol.Close
Set rsSpecCol = Nothing
rsData.Close
Set rsData = Nothing
End Sub