Transfertxt problem; difference A97 and A2k

Hi all,

All of a sudden I have problems importing and exporting textfiles with A97.
Code was working allright for years and years, but not today...

Since I have this app in two versions I tested the behaviour in A2k also.
To my surprise the *same* code with the *same* files works with A2k and *not* with A97
FYI: App is split FE-BE and the BE is in Access97 format, so the same BE is used with these versions.
So I have FE97-BE97 and FE2k2-BE97 here to test with.

Export:
the code DoCmd.TransferText acExportDelim, , "TabVoorraad", "C:\svb\export\voorraad.txt", True
works in Access 2000 and does not work in Access 97 anymore.
Again: I tested this with the very SAME TabVoorraad (A97 format)
Error A97 generates: "3441 Text file specification field separator matches decimal separator or text delimiter"

Import:
the code DoCmd.TransferText acImportDelim, , "TempTabImportVoorraad", "C:\svb\import\voorraad.txt", True
works in Access 2000 and does not work in Access 97 anymore.
Again: I tested this with the very *same* file voorraad.txt
Error A97 generates: "3274 External table not in expected format"

Same PC - same Files. The error messages are useless here

I am stumped here... I Googled around but to no avail yet.
Any ideas??

Arno R

re: Transfertxt problem; difference A97 and A2k

Hi Arno,

some questions:

-what characters are used in voorraad.txt as decimal separator and text
delimiter, respectively?
Are they "." and "," ?

- Does the file contain headers on the first row?

- Does the file contain intermixed data?

-tom

Arno R ha scritto:
[color=blue]
> Hi all,
>
> All of a sudden I have problems importing and exporting textfiles with A97.
> Code was working allright for years and years, but not today...
>
> Since I have this app in two versions I tested the behaviour in A2k also.
> To my surprise the *same* code with the *same* files works with A2k and *not* with A97
> FYI: App is split FE-BE and the BE is in Access97 format, so the same BE is used with these versions.
> So I have FE97-BE97 and FE2k2-BE97 here to test with.
>
> Export:
> the code DoCmd.TransferText acExportDelim, , "TabVoorraad", "C:\svb\export\voorraad.txt", True
> works in Access 2000 and does not work in Access 97 anymore.
> Again: I tested this with the very SAME TabVoorraad (A97 format)
> Error A97 generates: "3441 Text file specification field separator matches decimal separator or text delimiter"
>
> Import:
> the code DoCmd.TransferText acImportDelim, , "TempTabImportVoorraad", "C:\svb\import\voorraad.txt", True
> works in Access 2000 and does not work in Access 97 anymore.
> Again: I tested this with the very *same* file voorraad.txt
> Error A97 generates: "3274 External table not in expected format"
>
> Same PC - same Files. The error messages are useless here
>
> I am stumped here... I Googled around but to no avail yet.
> Any ideas??
>
> Arno R[/color]

re: Transfertxt problem; difference A97 and A2k

Hi Tom,
Answered inline

<tommaso.gastaldi@uniroma1.it> schreef in bericht news:1145398669.621099.218980@i39g2000cwa.googlegr oups.com...
[color=blue]
> Hi Arno,
>
> some questions:
>
> -what characters are used in voorraad.txt as decimal separator and text
> delimiter, respectively?
> Are they "." and "," ?
> [/color]

No decimal separaters in the file, delimiter is ;
Part of the file goes here... (watch for line wrap)

"Winkel";"Categorie";"Artikelnummer";"0";"35";"35+ ";"36";"36+";"37";"37+";"38";"38+";"39";"39+";"40" ;"40+";"41";"41+";"42";"42+";"43";"43+";"44";"44+" ;"45";"45+";"46";"46+"
"B";2;4201;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 ;0;0;0;0;0
"B";2;4205;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;2;1;2 ;-1;0;0;0;0
"B";2;4501;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;1;1;1;1;1 ;0;1;0;0;0

[color=blue]
> - Does the file contain headers on the first row?[/color]
Yep
[color=blue]
> - Does the file contain intermixed data?[/color]
No, same (most numeric) data all over

Thanks
Arno R

[color=blue]
> Arno R ha scritto:
> [color=green]
>> Hi all,
>>
>> All of a sudden I have problems importing and exporting textfiles with A97.
>> Code was working allright for years and years, but not today...
>>
>> Since I have this app in two versions I tested the behaviour in A2k also.
>> To my surprise the *same* code with the *same* files works with A2k and *not* with A97
>> FYI: App is split FE-BE and the BE is in Access97 format, so the same BE is used with these versions.
>> So I have FE97-BE97 and FE2k2-BE97 here to test with.
>>
>> Export:
>> the code DoCmd.TransferText acExportDelim, , "TabVoorraad", "C:\svb\export\voorraad.txt", True
>> works in Access 2000 and does not work in Access 97 anymore.
>> Again: I tested this with the very SAME TabVoorraad (A97 format)
>> Error A97 generates: "3441 Text file specification field separator matches decimal separator or text delimiter"
>>
>> Import:
>> the code DoCmd.TransferText acImportDelim, , "TempTabImportVoorraad", "C:\svb\import\voorraad.txt", True
>> works in Access 2000 and does not work in Access 97 anymore.
>> Again: I tested this with the very *same* file voorraad.txt
>> Error A97 generates: "3274 External table not in expected format"
>>
>> Same PC - same Files. The error messages are useless here
>>
>> I am stumped here... I Googled around but to no avail yet.
>> Any ideas??
>>
>> Arno R[/color]
>[/color]

re: Transfertxt problem; difference A97 and A2k

For problem 1.

Try placing this ini file:

http://cam70.sta.uniroma1.it/Technic...rno/schema.ini

in the same directory as voorraad.txt, and run the import. Let's see
what happens...

-tom

re: Transfertxt problem; difference A97 and A2k

Hi Tom,

I don't understand how a file like schema.ini would make any difference
but I am willing to try.

So I copied the schema.ini to the same directory as the file
voorraad.txt
It makes no difference (as I expected...)
3274 External table isn't in the expected format

Thanks
Arno R
(answering this one from my google-account)

tommaso.gastaldi@uniroma1.it schreef:
[color=blue]
> For problem 1.
>
> Try placing this ini file:
>
> http://cam70.sta.uniroma1.it/Technic...rno/schema.ini
>
> in the same directory as voorraad.txt, and run the import. Let's see
> what happens...
>
> -tom[/color]

re: Transfertxt problem; difference A97 and A2k

hi Arno,

Sorry, it was just an attempt. In similar cases it has worked. It
depends on the provider which does the job. From the error message you
reported, it seemed likely to me the import was being done through the
Microsoft TEXT provider (odbc or oledb), which does read the local
"schema.ini". If this is the case, it may be worth to play with the
schema.ini to establish if: 1.It is read, 2.if it is, what are the
right settings.

this might help:
http://windowssdk.msdn.microsoft.com...a_ini_file.asp
http://origma.ru/support/default.asp...omTextFile.htm
http://www.codeproject.com/cs/databa...lCSVReader.asp

Another way to do a similar thing is to run Regedit and manually
changes the values in:
HKEY_LOCALMACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Text

If the import is done by others methods, the above, clearly, will not
be of any help. But I guess, it would be useful to know details on how
import/export is done.

-tom

PS
when you find the solution, let me know, I am curious about it :)

[further, if you are willing to send the code lines where the
connection/import/export is done
I am willing to make some attempts on my pc]

re: Transfertxt problem; difference A97 and A2k

Another idea.
I noticed that you actually posted:

DoCmd.TransferText acExportDelim, , "TabVoorraad",
"C:\svb\export\voorraad.txt", True
DoCmd.TransferText acImportDelim, , "TempTabImportVoorraad",
"C:\svb\import\voorraad.txt", True

You are not specifying an import specification at all in your code.
I've added Import Specification Name in the code to show you where it
goes:

DoCmd.TransferText acImportDelim, "Import Specification Name",
"TabVoorraad",
"C:\svb\export\voorraad.txt", True

"Import (oe Export) Specification Name" represent the specification
that you
save through the Import/E Wizard.

If not specified ACCESS uses "default" settings for importing text
(which probably overrides the "schema.ini"), which may cause those
errors. Try Writing the correct specification, and your problems should
go away (I hope).

-Tom

Arno R ha scritto:
[color=blue]
> Hi Tom,
>
> I don't understand how a file like schema.ini would make any difference
> but I am willing to try.
>
> So I copied the schema.ini to the same directory as the file
> voorraad.txt
> It makes no difference (as I expected...)
> 3274 External table isn't in the expected format
>
> Thanks
> Arno R
> (answering this one from my google-account)
>
> tommaso.gastaldi@uniroma1.it schreef:
>[color=green]
> > For problem 1.
> >
> > Try placing this ini file:
> >
> > http://cam70.sta.uniroma1.it/Technic...rno/schema.ini
> >
> > in the same directory as voorraad.txt, and run the import. Let's see
> > what happens...
> >
> > -tom[/color][/color]

re: Transfertxt problem; difference A97 and A2k

Hi Tom

I am having these problems with A97 ...
I understand that using an importspec could be of use here. WIll try
this evening when I am back home.
Should have thought of this myself!
Still stumped why this worked for years and suddenly dies now.
Also stumped why it works in A2k (same codeline without importspec).

Thanks, I will let you know if importspec works

Arno R

re: Transfertxt problem; difference A97 and A2k

Hi Tom,

As I expected indeed: with an import specification the import works again,
Maybe I should have used an importspec from the beginning... (this is an app I 'inherited'...)
Still stumped why this import worked for years and suddenly stops working... but glad the issue is solved
(Access keeps surprizing me sometimes)

Thanks for your help

Arno R


"Arno R" <arracom@tiscali.nl> schreef in bericht news:1145452848.502460.121000@i40g2000cwc.googlegr oups.com...[color=blue]
>
> Hi Tom
>
> I am having these problems with A97 ...
> I understand that using an importspec could be of use here. WIll try
> this evening when I am back home.
> Should have thought of this myself!
> Still stumped why this worked for years and suddenly dies now.
> Also stumped why it works in A2k (same codeline without importspec).
>
> Thanks, I will let you know if importspec works
>
> Arno R
>[/color]