Error 3073 with TransferText

[Freeflyer]

Hi,

I am using TransferText to export the results of a query to a CSV file.
Originally, I was using the TransferTextType acExportDelim, but this enclosed
th exported fields in quotations marks which is not suitable for my purposes.
I then used the export text wizard to create a new export specification,
without any text qualifier, which I called acExportPricesheet.

However, when I changed the TransferText command to use the new
specification I now receive an error message:
"Run-time error '3073':
operation must use an updateable query."

Can anyone explain what is causing this error and how to fix it? The query
that is being exported should not be updated by the export. I've included by
code below.

Thanks,

Michael

*** VBA Code ***
Private Sub cmdExport_Click()
Dim strFilter As String
Dim lngFlags As Long
Dim intWindowHandle As Long
intWindowHandle = Screen.ActiveForm.hwnd
strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.CSV")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
ahtCommonFileOpenSave(Flags:=lngFlags, InitialDir:="H:\My Documents\",
Filter:=strFilter, FilterIndex:=3, DefaultExt:="csv",
FileName:="Pricesheet.csv", DialogTitle:="Save Pricesheet as...",
hwnd:=intWindowHandle, OpenFile:=False)
End Sub
*** End Code ***
I'm using the Open/Save file API from http://www.mvps.org/access/ to obtain
the filename to export to.

 

[Klatuu]

The problem will be in the query you are using for the transfer. The query
structured so that it is not an updatable recordset. In most cases, this is
because there are related tables in the query and they have a one to many
relationship. Jet can't do updates with that type of recordset.

 

[Freeflyer]

Ok, I can accept that the query is not an updatable recordset, but I do not
see why it should be. This is a fixed query that is extracting data from the
database. All I want to do is export this to a CSV file.
As I understand it, the Export Specification simply defines the format of
the exported data. Changing this should not make any changes to the data in
the query, should it?

 

[Klatuu]

I would not think so.
Just as a test, try doing the export without using the spec and see what
happens.

 

[Freeflyer]

Without the spec, the export works fine. As I said, I originally used the
acExportDelim specification, and the export worked fine. However, this
specification encloses the fields in quotation marks which is not suitable
for my purposes.
I assume I must be doing something wrong with the specification, but haven't
found much info on how they work, other than to use the wizard.

Any ideas would be greatly appreciated.

 

[Klatuu]

Interesting the specs would cause such an error. When it comes to building
specs, I am in about the same boat you are.

 

[Freeflyer]

Thanks for trying.
Thanks for trying anyway.
I have finally tracked down my error, I was misusing the TransferText command.
My original line was:
DoCmd.TransferText acExportPricesheet, , "qryContract Old",
"C:\Pricesheet.csv"
Where acExportPricesheet was the name of the specification I created.
The correct use is:
DoCmd.TransferText acExportDelim, "acExportPricesheet", "qryContract Old",
"C:\Pricesheet.csv"
The first argument is the transfer type, not the specification, so it should
remain as acExportDelim for a delimited file. The second argument is the
(optional) specification name.

Thanks again for your help.

Michael

 

[Klatuu]

Glad you tracked it down.
I would suggest you not use a name that starts with ac.
A prefix of ac usually means it is an Access Intrinsic Constant. There is
nothing technically wrong with it, but another person reading your code may
be confused by it.