I have a strange problem linking tab delimited text files in Access 2000
(I am running Windows XP), please try this and let me know if I am going
mad.
Step 1.
Create the tab-delimited text file below in WordPad and call the file
"HeaderYES. txt" (the space between the two fields is actually a tab):-
"Column1" "Column2"
1 "Line1"
2 "Line2"
3 "Line3"
4 "Line4"
Step 2.
Make a copy of "HeaderYES. txt" (called "HeaderNO.t xt") and remove (in
WordPad) the first line, you should be left with :-
1 "Line1"
2 "Line2"
3 "Line3"
4 "Line4"
Step 3.
Create a new Microsoft Access database and link in the file
"HeaderYES. txt" using the following parameters :-
Link Text Wizard page 1
- Delimited
Link Text Wizard page 2
- Tab
- First Row Contains Field Names **ON**
- Text Qualifier "
Link Text Wizard page 3
Link Text Wizard page 4
- press the Advance button and SaveAs your Import/Export
specification as "header"
- FINISH
If you open the linked text table HeaderYES you will see that everything
is OK
Step 4.
Now link in HeaderNO.txt using the following parameters :-
Link Text Wizard page 1
- Delimited
- press the Advance button and use the spec called "header"
Link Text Wizard page 2
- Tab
- First Row Contains Field Names **OFF**
- Text Qualifier "
Link Text Wizard page 3
Link Text Wizard page 4
- FINISH
Once again if you look at the table everything seems to work OK
Step 5.
Repeat step 4. BUT do not press the Advance button and select a spec
until page4 of the Wizard. You should see that there is a line missing
from the table.
Conclusion
To me it looks as the Wizard is sensitive as to at which step you
choose your spec file. The safest bet is therefore to always choose your
spec file at the FIRST step of the process, this way any additional
options can be utilized as some of the seem to be RESET when choosing a
spec file.
This is fine now that I know it will happen but what about VB? The two
lines of code produce different tables
DoCmd.TransferT ext acLinkDelim, "header", "yes", "HeaderYES.txt" ,
True
DoCmd.TransferT ext acLinkDelim, "header", "no", "HeaderNO.t xt",
False
Problem
What I don't understand is why the HasFieldNames parameter of
DoCmd.TransferT ext seems to be ignored, the option to have the first row
of a file as fieldnames is not part of the import/export specification.
I would be very grateful is someone could explain this to me. Maybe it
is normal or to be expected or even documented somewhere but I cannot
find it.
I'm sorry if this looks like a long-winded explanation, but it was the
only way I could explain the problem in detail.
Thanks in advance for any help
Also if anyone knows why the CodePage parameter is also ignored in
TransferText, that would also be helpful
------
Sean
"If at first you don't succeed, go to sleep"
*** Sent via Developersdex http://www.developersdex.com ***
(I am running Windows XP), please try this and let me know if I am going
mad.
Step 1.
Create the tab-delimited text file below in WordPad and call the file
"HeaderYES. txt" (the space between the two fields is actually a tab):-
"Column1" "Column2"
1 "Line1"
2 "Line2"
3 "Line3"
4 "Line4"
Step 2.
Make a copy of "HeaderYES. txt" (called "HeaderNO.t xt") and remove (in
WordPad) the first line, you should be left with :-
1 "Line1"
2 "Line2"
3 "Line3"
4 "Line4"
Step 3.
Create a new Microsoft Access database and link in the file
"HeaderYES. txt" using the following parameters :-
Link Text Wizard page 1
- Delimited
Link Text Wizard page 2
- Tab
- First Row Contains Field Names **ON**
- Text Qualifier "
Link Text Wizard page 3
Link Text Wizard page 4
- press the Advance button and SaveAs your Import/Export
specification as "header"
- FINISH
If you open the linked text table HeaderYES you will see that everything
is OK
Step 4.
Now link in HeaderNO.txt using the following parameters :-
Link Text Wizard page 1
- Delimited
- press the Advance button and use the spec called "header"
Link Text Wizard page 2
- Tab
- First Row Contains Field Names **OFF**
- Text Qualifier "
Link Text Wizard page 3
Link Text Wizard page 4
- FINISH
Once again if you look at the table everything seems to work OK
Step 5.
Repeat step 4. BUT do not press the Advance button and select a spec
until page4 of the Wizard. You should see that there is a line missing
from the table.
Conclusion
To me it looks as the Wizard is sensitive as to at which step you
choose your spec file. The safest bet is therefore to always choose your
spec file at the FIRST step of the process, this way any additional
options can be utilized as some of the seem to be RESET when choosing a
spec file.
This is fine now that I know it will happen but what about VB? The two
lines of code produce different tables
DoCmd.TransferT ext acLinkDelim, "header", "yes", "HeaderYES.txt" ,
True
DoCmd.TransferT ext acLinkDelim, "header", "no", "HeaderNO.t xt",
False
Problem
What I don't understand is why the HasFieldNames parameter of
DoCmd.TransferT ext seems to be ignored, the option to have the first row
of a file as fieldnames is not part of the import/export specification.
I would be very grateful is someone could explain this to me. Maybe it
is normal or to be expected or even documented somewhere but I cannot
find it.
I'm sorry if this looks like a long-winded explanation, but it was the
only way I could explain the problem in detail.
Thanks in advance for any help
Also if anyone knows why the CodePage parameter is also ignored in
TransferText, that would also be helpful
------
Sean
"If at first you don't succeed, go to sleep"
*** Sent via Developersdex http://www.developersdex.com ***