Transfertext Export

[Georgie]

Access 2003, Windows XP

When I use this with a previously saved query, all goes well:
DoCmd.TransferText acExportDelim, "TabDelimitedWithFieldName", "Query1",
"C:\MyC\TestHistory\Trial.txt", True

If I use this code:
Public Sub BuildTextExport()

Dim strTableName As String
Dim strTNsql As String
Dim strQTemp As String

Dim dbsTestHist As DAO.Database
Dim qdfTemp As DAO.QueryDef

strTableName = "Current Testing Data"
strQTemp = "qryTemp"

strTNsql = "SELECT DISTINCT K.MeasurementScaleName, K.TestName "
strTNsql = strTNsql & "FROM " & strTableName & " as K "
strTNsql = strTNsql & "WHERE ((K.TestTypeName)<>""Survey"");"

Set dbsTestHist = CurrentDb()
Set qdfTemp = dbsTestHist.CreateQueryDef(strQTemp, strTNsql)
qdfTemp.Close

DoCmd.TransferText acExportDelim, "TabDelimitedWithFieldName", qdfTemp.Name,
"C:\MyC\TestHistory\TestNames.txt", True

dbsTestHist.QueryDefs.Delete strQTemp
Set qdfTemp = Nothing
dbsTestHist.Close
Set dbsTestHist = Nothing

End Sub

I get this error:
Run-time error '3011'
The Microsoft Jet database engine could not fin the object 'TestNames.txt'.
Make sure the object exists and that you spell its name and path name
correctly.

Everything seems to be working except the DoCmd.TransferText line.

Thanks for your help in advance.

 

[Dave]

Hi Georgie,
I've just been having the same problem and have worked out the problem. The
annoying part about the error is that the error message of 3011 is not the
actual problem. You'll find that your Export Spec file has a different field
name to one or more of those specified in your table or query. Make sure the
spec file field names match exactly with those in your table or query and
you'll be fine.