Import Spec Problem

[Guest]

Hi, I am in the process of converting a DB from 97 to 2002, all is mostly
going well but I have come across a problem when trying to import a file. The
specification exists and the format matches the table to which it refers but
I keep getting the error message 31519 (You cannot import this file) when
running the code:

DoCmd.TransferText acImportDelim, "ImportPRN", "Import", ImportFile

The actual filename to be imported is entered by the user and stored as the
variable ImportFile.

Any suggestions would be greatly appreciated.

Thanks

Paul

 

[Allen Browne]

The original release of Access 97 was smart enough to figure out that if you
used the TransferTEXT method, it should treat the file as text.

Microsoft broke it. Now TransferText has been dumbed down, so the import
will only work if the file to be imported has an extension that is
registered with Windows as a text file, such as .txt, or .csv. You will have
to insist that the user's file is named like that for the import to work.

 

[Brian]

The original release of Access 97 was smart enough to figure out that if you
used the TransferTEXT method, it should treat the file as text.

Microsoft broke it. Now TransferText has been dumbed down, so the import
will only work if the file to be imported has an extension that is
registered with Windows as a text file, such as .txt, or .csv. You will have
to insist that the user's file is named like that for the import to work.

MS did claim some security-related justification for this, I seem to recall.
I think it's in the KB somewhere.

 

[Guest]

Hi Allen, yes I had worked out that they've made it less intelligent and have
changed the output from the 'feeder' system to *.csv.

Paul

 

[Guest]

Hi Allen, yes I had worked out that they've made it less intelligent and have
changed the output from the 'feeder' system to *.csv, but still have the problem.

Paul

 

[Dirk Goldgar]

Hi Allen, yes I had worked out that they've made it less intelligent
and have changed the output from the 'feeder' system to *.csv.

What you can do is either copy the file to a temp file with the proper
extension (for which you could use the FileCopy statement), or
temporarily rename the file to the proper extension (for which you could
use the Name statement).

 

[Guest]

Thanks Dirk, but as I said, I have changed the feeder file to *.CSV now (e.g.
RT17.CSV) and it still returns error 31519. I suspect it may have something
to do with using a variable for the file name, but the system needs that
flexibility.

Paul

 

[Dirk Goldgar]

Thanks Dirk, but as I said, I have changed the feeder file to *.CSV
now (e.g. RT17.CSV) and it still returns error 31519. I suspect it
may have something to do with using a variable for the file name, but
the system needs that flexibility.

Using a variable for the file name shouldn't make any difference, and
doesn't for my copy of Access 2002. Have you verified, by stepping
through the code and examining the variables, that the variable does in
fact contain the correct name of the file? The only way I can make that
particular error come up is by using an extension that is not in the
approved list.

My only other thought is that something might be wrong with the import
specification, but I tried importing a .csv file with an incorrect
specification, and didn't get that error.

 

[Pieter Wijnen]

Sounds as though the mstext*.dll (? Or something like that) Reference is
invalid. try to locate it
& run regsvr32 on it. Else I seem to remember that It might be hardcoded
somewhere in registry to point to %windir%\system32 (that can at least be
the problem with the linked table manager showing all blanks)

HTH

Pieter

 

[Guest]

Hi again Dirk,

Yes, I have stepped thru the code but am unable to see where the error lies,
I have tried replacing the variable with a hard-coded file name
("C:\RT17.csv") but this failed with File Not Found (error 53) ??? So I now
cannot use the 'Copy the variable file to a known place' method of handling
this.

I'm now beginning to suspect I may have to re-install the package!

Thanks

Paul

 

[Guest]

Hi Pieter,

Thanks for the idea, I tried this but still get the same error.

Thanks

Paul

 

[Dirk Goldgar]

Hi again Dirk,

Yes, I have stepped thru the code but am unable to see where the
error lies, I have tried replacing the variable with a hard-coded
file name ("C:\RT17.csv") but this failed with File Not Found (error
53) ??? So I now cannot use the 'Copy the variable file to a known
place' method of handling this.

So I have to ask: when you tried hard-coding the file name like that,
did the file "C:\RT17.csv" actually exist at that location?

 

[Guest]

Yes, it contains 2 lines of (valid) data which the '97 version copes with
quite happily.

 

[Dirk Goldgar]

Yes, it contains 2 lines of (valid) data which the '97 version copes
with quite happily.

Very odd. Needless to say, it works fine for me. I begin to think
you're right, and that there's something messed up about your
installation. Before giving up, though, try creating a new, minimal
database that does nothing but import that file. If that doesn't work,
there's probably something wrong with your installation -- but e-mail me
a zipped copy (at the address you can get from my website, listed
below), and I'll check it out here. If it does work, then maybe the big
database's VB project is corrupted somehow (and a decompile might fix
it), or maybe there's a flaw in your code somewhere.

If you send me the "minimal" database, make sure you compact it and zip
it so that it's less than 1MB in size. It ought actually to be tiny.
And *don't* post my e-mail address here in the newsgroup!

 

[Guest]

Hi Dirk, sorry for the delay, long weekend (gotta take 'em when you can get
'em). Out of desperation/frustration I re-wrote the code to copy the variable
file to a known name and tried the import via a macro, and guess what ? It
worked, although this is essentially the same as a previous attempt using
code with a 'known' file name.

Thanks for your help with this.

Paul