Test Output

[Guest]

I have inherited an ancient green screen system which produced a strange file
output for a major customers EDi app. Luckley the green screen can go, but
only when I can replicate this file format from our new system. I have tried
every veriation of DoCmd. TransferText acExportDelim I can find but to no
avail.
The output file uses a "|" (pipe) as the delimiter, contains no column
headings and has a .EDI as the file name extension.

Can someone point me I the right direction to solve this problem ?

Thanks

Kevin PD

 

[Guest]

Your main problem is that microsoft access wants a file name it recognises
(like .txt). Otherwise you get a "cannot update, database or object is read
only" message.
You should be able to include the | delimiter in the export spec --- I've
certainly changed the delimiter character this way before.

However, the EDI extension is trickier. You'll need to edit the registry -
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions
should have "edi" added to it. This will need to be done on any machine you
want to be able to run this (by default access text isam is only set up to
work with certain file types to stop you accidentally knacking stuff).

For more info, read this article
http://support.microsoft.com/?kbid=306144

 

[Nikos Yannacopoulos]

Or, you could export in a .txt fiel, and then change the extension
programatically, no messing with the registry or any other
machine-specific settings.

HTH,
Nikos

 

[Guest]

I am currently running a query which extracts the data from our new system,
so I can call this whatever whatever I want extension wise, so I can overcome
the first point you make.

The second one about the export spec, is this the same as the schema.ini
file ? If not where do I write this ? I am using Access 2000 and the only
option it give me via import/export are space,tab : and ;.

The .edi part I shall leave to last, prefer to get one peice at a time
working.

Thanks

KevinPD

 

[Guest]

sorry - to create the export spec, do the following manually.
1) select the table or query you wish to export
2) select file / export
3) set save as type to text, put in file name & location, press save
4) choose delimited, hit next,
5) click in other, change the space character to your | character
6) change or remove the text qualifier if appropriate
7) click next
DON'T CLICK FINISH - YET.
8) Click ADVANCED, then save as, then input your specification name.

That's what you put in the "my_spec" bit below.
Once you've saved the specification, you can refer to it as much as you
like, but I don't think you can create it programattically, I think you've
got to create it manually then refer to it programawhatsitly.

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", True

good luck!

 

[Guest]

Apologies, if you don't want column headers, do it like this instead !

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", False

 

[Guest]

Hello,

Having something of a nightmare with this one now, below is my code and all
I keep getting is "Run-time error "3001"; Invalid Argument."

DoCmd.TransferText acExportDelim,"new_delimited" [my export spec with "|"],
"tbl_pms_edi_output"[table to output],"C:\pms_edi_output.txt"[output
location], False

I cannot see any obvious problems above or am I being very dense ?????

a very confused KPD

 

[Guest]

After further testing the problem is definatley in the export spec, remove it
and the code works !

When I go to the import/export specs "new_delimited" is there, but obviously
something is wrong with it. Will see what else I can find on this site re
specs, see if I am doing something wrong.

Cheers

KPD

Hello,

Having something of a nightmare with this one now, below is my code and all
I keep getting is "Run-time error "3001"; Invalid Argument."

DoCmd.TransferText acExportDelim,"new_delimited" [my export spec with "|"],
"tbl_pms_edi_output"[table to output],"C:\pms_edi_output.txt"[output
location], False

I cannot see any obvious problems above or am I being very dense ?????

a very confused KPD

Apologies, if you don't want column headers, do it like this instead !

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", False

 

[Guest]

Swiched to a new Windows 2003 box and lo and behold, Export Spec now works
!!!!!

So I now get output with a "|" and no column headings. Only one problem,
although output is surposed to be delimited by the "|" I am getting large
amounts of blank space between the test fields and the "|" delimiter.

Any suggetions as how to remove this as the customer EDi app will not
process the file with this space ?

Thanks

Kevin PD

After further testing the problem is definatley in the export spec, remove it
and the code works !

When I go to the import/export specs "new_delimited" is there, but obviously
something is wrong with it. Will see what else I can find on this site re
specs, see if I am doing something wrong.

Cheers

KPD

Hello,

Having something of a nightmare with this one now, below is my code and all
I keep getting is "Run-time error "3001"; Invalid Argument."

DoCmd.TransferText acExportDelim,"new_delimited" [my export spec with "|"],
"tbl_pms_edi_output"[table to output],"C:\pms_edi_output.txt"[output
location], False

I cannot see any obvious problems above or am I being very dense ?????

a very confused KPD

Apologies, if you don't want column headers, do it like this instead !

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", False

 

[Guest]

I presume you're talking about trailing spaces at the end of fields?

Normally you wouldn't expect these to be input by the export spec.

Places to look :
1) check your export line to make sure you're using acExportDelim (obvious,
I know)
2) check that your actual fields don't have trailing spaces. If they do, use
Trim(FieldName) in the query you are using as your datasource before passing
it through the export spec.


--
A laugh, a smile, and a pint of beer.

Swiched to a new Windows 2003 box and lo and behold, Export Spec now works
!!!!!

So I now get output with a "|" and no column headings. Only one problem,
although output is surposed to be delimited by the "|" I am getting large
amounts of blank space between the test fields and the "|" delimiter.

Any suggetions as how to remove this as the customer EDi app will not
process the file with this space ?

Thanks

Kevin PD

After further testing the problem is definatley in the export spec, remove it
and the code works !

When I go to the import/export specs "new_delimited" is there, but obviously
something is wrong with it. Will see what else I can find on this site re
specs, see if I am doing something wrong.

Cheers

KPD

Hello,

Having something of a nightmare with this one now, below is my code and all
I keep getting is "Run-time error "3001"; Invalid Argument."

DoCmd.TransferText acExportDelim,"new_delimited" [my export spec with "|"],
"tbl_pms_edi_output"[table to output],"C:\pms_edi_output.txt"[output
location], False

I cannot see any obvious problems above or am I being very dense ?????

a very confused KPD

:

Apologies, if you don't want column headers, do it like this instead !

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", False

 

[Guest]

I certainly need the beer, tried using TRIM on one of the text fields, now
getting error message saying output does not match Schema.ini file !!!!!

Classic one step forward and one back..... usual with everything Microsoft.

Going to revisit my SQL view data extract and "trim" the data at source
rather than mess up this pesky Access app now that it is semi working.

Cheers for all the help and pointers so far.

Kevin PD

I presume you're talking about trailing spaces at the end of fields?

Normally you wouldn't expect these to be input by the export spec.

Places to look :
1) check your export line to make sure you're using acExportDelim (obvious,
I know)
2) check that your actual fields don't have trailing spaces. If they do, use
Trim(FieldName) in the query you are using as your datasource before passing
it through the export spec.


--
A laugh, a smile, and a pint of beer.

Swiched to a new Windows 2003 box and lo and behold, Export Spec now works
!!!!!

So I now get output with a "|" and no column headings. Only one problem,
although output is surposed to be delimited by the "|" I am getting large
amounts of blank space between the test fields and the "|" delimiter.

Any suggetions as how to remove this as the customer EDi app will not
process the file with this space ?

Thanks

Kevin PD

After further testing the problem is definatley in the export spec, remove it
and the code works !

When I go to the import/export specs "new_delimited" is there, but obviously
something is wrong with it. Will see what else I can find on this site re
specs, see if I am doing something wrong.

Cheers

KPD

:

Hello,

Having something of a nightmare with this one now, below is my code and all
I keep getting is "Run-time error "3001"; Invalid Argument."

DoCmd.TransferText acExportDelim,"new_delimited" [my export spec with "|"],
"tbl_pms_edi_output"[table to output],"C:\pms_edi_output.txt"[output
location], False

I cannot see any obvious problems above or am I being very dense ?????

a very confused KPD

:

Apologies, if you don't want column headers, do it like this instead !

DoCmd.TransferText acExportDelim, "My_Spec", "Table_1", "C:\Myfile.txt", False