Problems with TransferText and Query

  • Thread starter Thread starter Bill Dearborn
  • Start date Start date
B

Bill Dearborn

Please Help...

I have a problem which really has me going around in circles...so any hint
would be helpful. I am running Access XP on Windows XP Pro - but have
duplicated the error with Access 2000 on Windows XP Home and with Access
2000 on WIndows ME. The configuration is Frontend for forms, queries,
reports and modules, with all data in a seperate backend database - all
databases are in Access 2000 format..

I have a command for writing the results of a query to a Word Mail Merge
file.

The command is:
DoCmd.TransferText acExportMerge, , "qryALLMP", "C:\DATAFILES\ALLMP.txt"

The query is:
SELECT [strPersonLastName] & ", " & [strPersonFirstName] AS SortName,
tblDelegates.strPersonLastName, tblDelegates.strPersonFirstName,
tblDelegates.strPersonPopName, tblDelegates.strPersonAddress,
tblDelegates.strPersonCommunity, tlkpProvince.strProvAbrev,
tblDelegates.strPersonPCode, Format(Right([strWorkPhone],7),"!@@@-@@@@") AS
WPhone, strHomePhone], strFaxPhone, tblDelegates.strPersonEmail,
tblPresbyteries.strPresbyteryAbrev, tblPresbyteries.strPresbyteryName,
tblDelegates.strPersonGender, tblDelegates.ynInDirectory,
tblDelegates.strLayorMinPer
FROM tlkpProvince INNER JOIN (tblPresbyteries INNER JOIN tblDelegates ON
tblPresbyteries.idnPresbyteryID = tblDelegates.idnPresbyteriesID) ON
tlkpProvince.idnProvID = tblDelegates.idnPersonProvID
WHERE (((tblDelegates.strLayorMinPer)="Ministry Personnel"))
ORDER BY [strPersonLastName] & ", " & [strPersonFirstName];


This works fine. But the moment I attempt to add formatting to the
strHomePhone and strFaxPhone that is exactly the same as the formatting used
for strWorkPhone i.e Format(Right([strHomePhone],7),"!@@@-@@@@") AS HPhone)
I get an error message...

Run-time Error 3011: The Microsoft Jet database engine could no find the
object "ALLMP.txt". Make sure the object exists and that you spell its name
and the path correctly."

The moment I remove the formatting the command works again.

When I open the revised Query directly it runs perfectly, the error only
emerges with the DoCmd.TransferText command. The same error was occuring
previously when I attempted to implement the first format for the
strWorkPhone. WE suspected a corrupt database at the time and the error
corrected itself when I created it in a new copy of the database, linmk to
the data, pasted the SQL query statment into a new query and re-wrote the
module with the Do.Cmd.Transfertext command. Then imported all the other
forms, reports and modules - that got the first formatting to work. This
time any attempts to get the query to work from DoCmd.TransferText in a new
database do not work - at least four attempts.

I have recently upgraded the Jet Database program as recommended by
Microsoft - I only did this on one machine just in case, but this does not
appear to change anything.

Previously someone wondered if the names of the files or directories might
contain spaces or be too long and hence cause the Jet to issue the error
3011 - but I have checked this - and have two other commands functioning
perfectly with the same query on different tables writing to files in the
same directory - the only difference being the lack of formatting. When I
add formatting to the telephone numbers in those other Queries I get the
3011 Error until I remove the formatting then all is well again.

I have searched Google - most references to 3011 are either for specifics of
corrupt databases ... or my earlier questions when I had my first run at
this thing... I am now re-reading supplementary books on SQL wondering it I
had made an error there...

Any assistance will be greatly appreciated...
 
Hi Bill,

I still suspect that this may be a corruption problem. Certainly there's
nothing obviously wrong with the SQL you've posted (except the ] after
strHomePhone<g>).

If you create a new .mdb file, link the tables, paste the SQL into a new
query (complete with Format()s), save the query and *without doing
anything else* run your DoCmd.TransferText statement from the Immediate
pane, does it work? If it works then and fails after you've imported
more stuff, I'd put my money on one or more of the objects you're
importing from the older mdb being corrupt.

Another thing to try is to make a copy of the back end mdb, create the
query in there (no linked tables) and run the DoCmd.TransferText from
the Immediate pane. What happens then?

If you haven't been there already, it's worth taking a look at Tony
Toews's page at http://www.granite.ab.ca/access/corruptmdbs.htm


Please Help...

I have a problem which really has me going around in circles...so any hint
would be helpful. I am running Access XP on Windows XP Pro - but have
duplicated the error with Access 2000 on Windows XP Home and with Access
2000 on WIndows ME. The configuration is Frontend for forms, queries,
reports and modules, with all data in a seperate backend database - all
databases are in Access 2000 format..

I have a command for writing the results of a query to a Word Mail Merge
file.

The command is:
DoCmd.TransferText acExportMerge, , "qryALLMP", "C:\DATAFILES\ALLMP.txt"

The query is:
SELECT [strPersonLastName] & ", " & [strPersonFirstName] AS SortName,
tblDelegates.strPersonLastName, tblDelegates.strPersonFirstName,
tblDelegates.strPersonPopName, tblDelegates.strPersonAddress,
tblDelegates.strPersonCommunity, tlkpProvince.strProvAbrev,
tblDelegates.strPersonPCode, Format(Right([strWorkPhone],7),"!@@@-@@@@") AS
WPhone, strHomePhone], strFaxPhone, tblDelegates.strPersonEmail,
tblPresbyteries.strPresbyteryAbrev, tblPresbyteries.strPresbyteryName,
tblDelegates.strPersonGender, tblDelegates.ynInDirectory,
tblDelegates.strLayorMinPer
FROM tlkpProvince INNER JOIN (tblPresbyteries INNER JOIN tblDelegates ON
tblPresbyteries.idnPresbyteryID = tblDelegates.idnPresbyteriesID) ON
tlkpProvince.idnProvID = tblDelegates.idnPersonProvID
WHERE (((tblDelegates.strLayorMinPer)="Ministry Personnel"))
ORDER BY [strPersonLastName] & ", " & [strPersonFirstName];


This works fine. But the moment I attempt to add formatting to the
strHomePhone and strFaxPhone that is exactly the same as the formatting used
for strWorkPhone i.e Format(Right([strHomePhone],7),"!@@@-@@@@") AS HPhone)
I get an error message...

Run-time Error 3011: The Microsoft Jet database engine could no find the
object "ALLMP.txt". Make sure the object exists and that you spell its name
and the path correctly."

The moment I remove the formatting the command works again.

When I open the revised Query directly it runs perfectly, the error only
emerges with the DoCmd.TransferText command. The same error was occuring
previously when I attempted to implement the first format for the
strWorkPhone. WE suspected a corrupt database at the time and the error
corrected itself when I created it in a new copy of the database, linmk to
the data, pasted the SQL query statment into a new query and re-wrote the
module with the Do.Cmd.Transfertext command. Then imported all the other
forms, reports and modules - that got the first formatting to work. This
time any attempts to get the query to work from DoCmd.TransferText in a new
database do not work - at least four attempts.

I have recently upgraded the Jet Database program as recommended by
Microsoft - I only did this on one machine just in case, but this does not
appear to change anything.

Previously someone wondered if the names of the files or directories might
contain spaces or be too long and hence cause the Jet to issue the error
3011 - but I have checked this - and have two other commands functioning
perfectly with the same query on different tables writing to files in the
same directory - the only difference being the lack of formatting. When I
add formatting to the telephone numbers in those other Queries I get the
3011 Error until I remove the formatting then all is well again.

I have searched Google - most references to 3011 are either for specifics of
corrupt databases ... or my earlier questions when I had my first run at
this thing... I am now re-reading supplementary books on SQL wondering it I
had made an error there...

Any assistance will be greatly appreciated...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Bill,

Thank you very much for the feedback. It's a mystery, however, that
neither of us found 225987 while we were searching the KB for causes of
error 3011 the other day.

John

John
It turns out to be another problem all together.

When acExportMerge writes a Word Merge file it also writes a file schema.ini
which lists the filename, fieldnames, and fieldsizes. If a change is made to
the definition of the query which changes either the fieldnames or the
fieldsizes then an error results..

Solution - after a change either edit schema.ini or delete it - and let a
correct one be generated.

Another newgroup reader pointed my to the knowledge base article: 225987

What must have happened when we were dealing with the error previously was
that I moved the output txt file to a different directory ... then
everything worked, until I tried to make another change in the query.

Thanks
Bill


John Nurick said:
Hi Bill,

I still suspect that this may be a corruption problem. Certainly there's
nothing obviously wrong with the SQL you've posted (except the ] after
strHomePhone<g>).

If you create a new .mdb file, link the tables, paste the SQL into a new
query (complete with Format()s), save the query and *without doing
anything else* run your DoCmd.TransferText statement from the Immediate
pane, does it work? If it works then and fails after you've imported
more stuff, I'd put my money on one or more of the objects you're
importing from the older mdb being corrupt.

Another thing to try is to make a copy of the back end mdb, create the
query in there (no linked tables) and run the DoCmd.TransferText from
the Immediate pane. What happens then?

If you haven't been there already, it's worth taking a look at Tony
Toews's page at http://www.granite.ab.ca/access/corruptmdbs.htm


Please Help...

I have a problem which really has me going around in circles...so any hint
would be helpful. I am running Access XP on Windows XP Pro - but have
duplicated the error with Access 2000 on Windows XP Home and with Access
2000 on WIndows ME. The configuration is Frontend for forms, queries,
reports and modules, with all data in a seperate backend database - all
databases are in Access 2000 format..

I have a command for writing the results of a query to a Word Mail Merge
file.

The command is:
DoCmd.TransferText acExportMerge, , "qryALLMP", "C:\DATAFILES\ALLMP.txt"

The query is:
SELECT [strPersonLastName] & ", " & [strPersonFirstName] AS SortName,
tblDelegates.strPersonLastName, tblDelegates.strPersonFirstName,
tblDelegates.strPersonPopName, tblDelegates.strPersonAddress,
tblDelegates.strPersonCommunity, tlkpProvince.strProvAbrev,
tblDelegates.strPersonPCode, Format(Right([strWorkPhone],7),"!@@@-@@@@") AS
WPhone, strHomePhone], strFaxPhone, tblDelegates.strPersonEmail,
tblPresbyteries.strPresbyteryAbrev, tblPresbyteries.strPresbyteryName,
tblDelegates.strPersonGender, tblDelegates.ynInDirectory,
tblDelegates.strLayorMinPer
FROM tlkpProvince INNER JOIN (tblPresbyteries INNER JOIN tblDelegates ON
tblPresbyteries.idnPresbyteryID = tblDelegates.idnPresbyteriesID) ON
tlkpProvince.idnProvID = tblDelegates.idnPersonProvID
WHERE (((tblDelegates.strLayorMinPer)="Ministry Personnel"))
ORDER BY [strPersonLastName] & ", " & [strPersonFirstName];


This works fine. But the moment I attempt to add formatting to the
strHomePhone and strFaxPhone that is exactly the same as the formatting used
for strWorkPhone i.e Format(Right([strHomePhone],7),"!@@@-@@@@") AS HPhone)
I get an error message...

Run-time Error 3011: The Microsoft Jet database engine could no find the
object "ALLMP.txt". Make sure the object exists and that you spell its name
and the path correctly."

The moment I remove the formatting the command works again.

When I open the revised Query directly it runs perfectly, the error only
emerges with the DoCmd.TransferText command. The same error was occuring
previously when I attempted to implement the first format for the
strWorkPhone. WE suspected a corrupt database at the time and the error
corrected itself when I created it in a new copy of the database, linmk to
the data, pasted the SQL query statment into a new query and re-wrote the
module with the Do.Cmd.Transfertext command. Then imported all the other
forms, reports and modules - that got the first formatting to work. This
time any attempts to get the query to work from DoCmd.TransferText in a new
database do not work - at least four attempts.

I have recently upgraded the Jet Database program as recommended by
Microsoft - I only did this on one machine just in case, but this does not
appear to change anything.

Previously someone wondered if the names of the files or directories might
contain spaces or be too long and hence cause the Jet to issue the error
3011 - but I have checked this - and have two other commands functioning
perfectly with the same query on different tables writing to files in the
same directory - the only difference being the lack of formatting. When I
add formatting to the telephone numbers in those other Queries I get the
3011 Error until I remove the formatting then all is well again.

I have searched Google - most references to 3011 are either for specifics of
corrupt databases ... or my earlier questions when I had my first run at
this thing... I am now re-reading supplementary books on SQL wondering it I
had made an error there...

Any assistance will be greatly appreciated...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top