Access does not overwrite existing Excel file after 2 times

I have an Access form where a user chooses from a drop down list the person they want to send the info to.

Access exports a query to an Excel file.  It needs to overwrite this file each time for a new person.

It works fine for the first 2 people.  When I go to the 3rd or 4th person, it stops overwriting the Excel file.

In the attached file, it's form frmVacyQuestMgrsORIG

Please let me know what I'm doing wrong.

Thanks!
VacyQuest-Data-for-Mgrs---Copy.accdb
Patty01AccessAsked:
Who is Participating?
 
omgangIT ManagerCommented:
It's got something to do with the TransferSpreadsheet method.  I changed your procedure to use the OutputTo method and it works fine.  Only drawback is Excel file is xls not xlsx.

DoCmd.OutputTo acOutputQuery, "qryVacyQuestWord", acFormatXLS, "C:\Vacancy\Quest\Test.xls"

I tried creating a blank .mdb database and copying all your objects into it but the same problem manifests.

Haven't seen this before but I don't think I've tried repeatedly exporting the same query to Excel and overwriting the output file.

Weird.
OM Gang
0
 
omgangIT ManagerCommented:
How bizarre, how bizarre!  When I right-click on the query and choose export --> Excel and overwrite the Text.xlsx file it exhibits the same behavior, e.g. the file last modified time stamp changes but the data in the file does not nor does it match what it should.

OM Gang
0
 
Patty01AccessAuthor Commented:
Yes, it's driving me crazy!

 Even when I do it manually (right click on the query-Export to Excel), I say to overwrite the existing file.  It says it does, but it does not.  I don't see a time stamp change after the 3rd one--still shows the time stamp for the 2nd one only.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
omgangIT ManagerCommented:
I saw the time stamp change each time I performed the export.  The content didn't change so I'm thinking the process exported the same data some how.
OM Gang
0
 
Patty01AccessAuthor Commented:
That's it!  Changing it from "transferspreadsheet" to the outputto worked perfectly!!

Thanks much OM Gang!!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Do you have "write" privileges to the target location??

Is it always after the "second" file, or is this always after a "specific" file"?

Have you tried OutputTo instead:
        DoCmd.OutputTo acOutputQuery, "qryVacyQuestWord", acFormatXLS, "C:\Vacancy\Quest\Merge\Word\" & xlFile

As a quick and dirty test:
Create a new DB and import all the objects and try this code in the new db.

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
opps, I see this has already been resolved...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.