Problem exporting a query using Transfertext method

[seattlerick]

Be patient, this is my first time posting a problem in a forum.

What follows is VB code that attempts to use TransferText to export a query to a .txt file and then do a Word Merge. The function is named CreateWordDataDoc and can be called with any one of eleven queries passed in argument, "strQuery". (The names of the eleven queries are stored in a table.)

The function works correctly with only one of the eleven queries. The weird thing is that the queries are quite similar. In fact, several are virtually identical except for differing criteria. When the Transfertext fails, we get this error:

Run-time error '3011'
The Microsoft Jet database engine could not find the object 'WordData.txt'. Make sure the object exists and that you spell its name and the path name correctly.

We can call this function multiple times and it succeeds only with the one query. We do nothing about the 'WordData.txt' file. It's existence or non-existence prior to running the function is irrelevant. Compacting and repairing the database has no effect.

If you study the code, you will see that it dynamically creates a query that it names 'QueryWordDataInput'. After the function exits, the query remains. In all cases, the query is built correctly and returns the expected rows. It seems that the problem is limited to the Transfertext method.

Any ideas on what the problem may be?

Thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function CreateWordDataDoc(ByVal strDoc As String, ByVal strQuery As String, ByVal strWhere As String)
'strDoc - Word document file name, “xxxxx.doc”.
'strQuery - Original query exported for Word mailmerge data.
'strWhere - WHERE clause to be added to original query SQL, strQuery, above.
On Error GoTo CreateWord_Error

Dim db As Database
Dim qdnew As QueryDef
Dim strSQL As String
Dim snap As RecordSet
Dim rtn As Variant
Dim strMsg As String
Dim datefld As Field
Dim i As Integer
Dim objWord As Word.Application
Dim wrdMailMerge As Word.MailMerge
Dim boilerPlateDoc As Word.Document

Set db = DBEngine.Workspaces(0).Databases(0)

'creates a temporary query based on original which may
'have additional WHERE clause added to it. Original query
'is not changed.

strSQL = "SELECT * FROM " & strQuery
If strWhere <> "" Then
strSQL = strSQL & strWhere
End If

'Delete temporary query.
On Error Resume Next
db.QueryDefs.Delete "qryWordDataInput"
DoEvents
On Error GoTo 0
Set qdnew = db.CreateQueryDef("qryWordDataInput", strSQL)
'Insure that new SQL finishes compiling before proceeding
DoEvents

'Be sure that there where actually records in new query
Set snap = qdnew.OpenRecordset()
If snap.BOF And snap.EOF Then
'no records - delete temporary query and exit
MsgBox "No matching records to merge!", 48, "Error"
GoTo Exit_CreateWord
Else
snap.MoveLast
strMsg = "Creating Word Data Source for " & snap.RecordCount & " records"
'MsgBox strMsg, 48, "Info" ' Debug testing for SOCRATES conversion.
strMsg = "Placing Word Data Source into " & LOCALPATH
'MsgBox strMsg, 48, "Info" ' Debug testing for SOCRATES conversion.
snap.Close
End If

'create a Word MailMerge data doc from temporary query
DoCmd.TransferText acExportMerge, , "qryWordDataInput", LOCALPATH

'Insure that file is finished before starting Word
DoEvents

'Instantiate Word
Dim retval As Variant
'retval = Shell(WORDPATH, vbNormalNoFocus)
DoEvents

'Get a Word object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True

'Open the boilerplate
strMsg = "Opening boilerplate in " & WORDDOCPATH & strDoc
rtn = SysCmd(SYSCMD_SETSTATUS, strMsg)
Set boilerPlateDoc = objWord.Documents.Open(WORDDOCPATH & strDoc)
'Define the boilerplate as main doc for mail merge
Set wrdMailMerge = boilerPlateDoc.MailMerge
DoEvents

'Attach the data source (variable info from Access exported query.)
strMsg = "Attaching data source in " & LOCALPATH
'MsgBox strMsg, 48, "Info"

With boilerPlateDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=LOCALPATH
End With
DoEvents

'Do the mailmerge
strMsg = "Performing Mailmerge..."
'MsgBox strMsg, 48, "Info"

wrdMailMerge.Destination = wdSendToNewDocument
wrdMailMerge.Execute False
DoEvents

'When done activate the window containing the boilerplate and then close it with no save.
objWord.Documents(strDoc).Activate
objWord.ActiveWindow.Close (wdDoNotSaveChanges)
GoTo Exit_CreateWord

Exit_CreateWord:
strMsg = "Exiting function"
'MsgBox strMsg, 48, "Info"
rtn = SysCmd(SYSCMD_CLEARSTATUS)
DoCmd.Hourglass False
Exit Function

CreateWord_Error:
MsgBox "Error in CreateWordDataDoc: " & Error, 16, Error
Resume Exit_CreateWord

End Function
--------------------------------------------------------------

 

[rberman]

I am seeing the same problem -- I have a bunch of these that work, and then one doesn't. Even the same specification is being used. The query itself works fine. But just this one fails, regardless of whether the target export file is there or not. All the others are created or cleared as needed automatically.

All the file names are very short, nothing weird in any name. Can't find anything odd in the query, and it isn't the query its complaining about!

I'm using Access 2003 SP1

 

[modest]

I just took a 5 sec look at the post; I haven't looked at the code yet because I'm headed to bed. But try Repair and Compacting the database. This sometimes gets rid of unexplained errors.

 

[GumbyD]

Check your export template to make sure the fields match with the table/query you are exporting. If the template does not match your fields then you get the highly mis-leading message.

Good luck

 

[synersoft]

GumbyD you da man! I had added 'friendy' column names after creating export spec..

Thank you kindly!!

-Jay
SynerSoft Custom Software Applications