Import Specs Changed "Magically" in Midst of Loading Sequence

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

I am completely confused and FRUSTRATED with this latest problem with Access
2007.

We are using Access 2007 running against 2000-2003 format (.mdb). Until
yesterday, this database which has functioned famously for a long time.
Never a problem with this "automated" load process, to collect 4 text files
from a network location and load them to this database elsewhere on the
network. What is puzzling, is that the Import Specification works for the
1st two files then MAGICALLY the spec changed from delimited to fixed width
during the processing! Anyone have a clue how this is possible? Code for
import is below:

ProcDate = Format(Forms!FrmAdmin!ProcessDate, "yyyy mm") & " 01"
DoCmd.TransferText acImportDelim, _
"Chgsum Import Specification", "MonthlyDataDump", _
"\\(network location 1)\" & ProcDate & ", chgsumN.txt"
DoCmd.TransferText acImportDelim, _
"Chgsum Import Specification", "MonthlyDataDump", _
"\\(network location 1)\" & ProcDate & ", chgsumS.txt"
DoCmd.TransferText acImportDelim, _
"Chgsum Import Specification", "MonthlyDataDump", _
"\\(network location 1)\" & ProcDate & ", chgsumV.txt"
DoCmd.TransferText acImportDelim, _
"Chgsum Import Specification", "MonthlyDataDump", _
"\\(network location 1)\" & ProcDate & ", chgsumW.txt"

I checked the Import Spec manually to verify that it was correct PRIOR to
running the process, but when I go back to it after the run-time error, it
has changed from delimited to fixed width.

If I load the files manually, all runs fine. How is this possible?

Thank you in advance for taking the time to lend a hand.
 
Are you more interested in resolving the issue, or in identifying the cause?

Have you changed the import specs back to what you need them to be and
tested? Is it working? Is it "sticking"?

Now, for why ...

1) sabotage - someone did this to get you
2) inadvertency - someone changed something, not realizing what they were
doing
3) update - an update from Microsoft singled out this one thing and
changed it without telling you
4) cosmic rays/gremlins

If you have it working again, does it matter "how"?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,

I did get a chuckle out of your approach to my problems... the problem has
fixed itself. It is amazing what an errant character/cosmic ray will do to
an input file that has been rock solid for years!

I got to the point of asking the originator to resend all of the input files
when the real source of my problems jumped off the screen at me, while I had
the originator on the phone, and since I could fix the file easier than she
could re-gen it I took care of it, but identified the problem so they could
fix it on their end for next time.

Thank you again for you assist and humor.
 
I got to the point of asking the originator to resend all of the input files
when the real source of my problems jumped off the screen at me, while I had
the originator on the phone, and since I could fix the file easier than she
could re-gen it I took care of it, but identified the problem so they could
fix it on their end for next time.

Just out of curiosity (and in case someone else might run into it), can you
post the "real source of the problem"?
 
Back
Top