ACC2000: Import with TransferText

[Vladislav Moltchanov]

I have discovered a couple of years ago, that import with
DoCMD.TransferText for CSV text file doesn’t work in Acc2000, while it
works perfectly in ACC97.
which has been discussed on this newsgroup forum, so this fact was
fixed.
Since I have to transfer files from VMS( SAS ) to WIN (Access), I still
keep using ACC97 as a simplest and reliable tool. However, final
products, such as distributable data entry system for multi central
stroke register, needs to be acc2000 version. So I need to keep running
2 versions of Access. I wonder, whether in 2 years time this problem in
acc2000 has got fixed. Any news?

Vlad

[Allen Browne]

If you are referring to the fact that Access 2000 cannot figure out how to
TransferTEXT if the file type is not registered with Windows, no the problem
has not been fixed. In fact, Microsoft broke Access 97 also, so that the
problem occurs there also after the service packs.

A workaround might be to rename the file (Name statement).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vladislav Moltchanov" <vl******************@ktl.fi> wrote in message
news:40***************@ktl.fi...

I have discovered a couple of years ago, that import with
DoCMD.TransferText for CSV text file doesn't work in Acc2000, while it
works perfectly in ACC97.
which has been discussed on this newsgroup forum, so this fact was
fixed.
Since I have to transfer files from VMS( SAS ) to WIN (Access), I still
keep using ACC97 as a simplest and reliable tool. However, final
products, such as distributable data entry system for multi central
stroke register, needs to be acc2000 version. So I need to keep running
2 versions of Access. I wonder, whether in 2 years time this problem in
acc2000 has got fixed. Any news?

Vlad

[Vladislav Moltchanov]

Allen Browne wrote:

If you are referring to the fact that Access 2000 cannot figure out how to
TransferTEXT if the file type is not registered with Windows, no the problem
has not been fixed. In fact, Microsoft broke Access 97 also, so that the
problem occurs there also after the service packs.

A workaround might be to rename the file (Name statement).

The file is a standard ASCII text file (<name>.txt), comma delimited, with text
fields quoted. By the way, with ACCESS 2000 it is possible to import it using
menu, however, not with code.It seems Access 2000 can't do automatic detection
of type (text or number) just by fact , field is quoted (text) or not ( number).
Besides, comma in text (though quoted) generates an error.
The same file is imported normal with Acces97 .

[Allen Browne]

I am unable to reproduce the problems you describe.

Just tried an export to text file with quotes as delimiters and commas
embedded inside quotes, and no problem in A2000 SP3. Commands were:
DoCmd.TransferText acExportDelim,,"qry4Export", "c:\test.txt", true
DoCmd.TransferText acImportDelim,,"aTestImport", "c:\test.txt", true

Certainly Access is not good at guessing the field type, but if you import
into a temp table you can then append the results to the real table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vladislav Moltchanov" <vl******************@ktl.fi> wrote in message
news:40***************@ktl.fi...

Allen Browne wrote:

If you are referring to the fact that Access 2000 cannot figure out how to TransferTEXT if the file type is not registered with Windows, no the problem has not been fixed. In fact, Microsoft broke Access 97 also, so that the
problem occurs there also after the service packs.

A workaround might be to rename the file (Name statement).

The file is a standard ASCII text file (<name>.txt), comma delimited,

with text fields quoted. By the way, with ACCESS 2000 it is possible to import it using menu, however, not with code.It seems Access 2000 can't do automatic detection of type (text or number) just by fact , field is quoted (text) or not ( number). Besides, comma in text (though quoted) generates an error.
The same file is imported normal with Acces97 .

[David W. Fenton]

Vladislav Moltchanov <vl******************@ktl.fi> wrote in
news:40***************@ktl.fi:

The file is a standard ASCII text file (<name>.txt), comma
delimited, with text fields quoted. By the way, with ACCESS 2000
it is possible to import it using menu, however, not with code.It
seems Access 2000 can't do automatic detection of type (text or
number) just by fact , field is quoted (text) or not ( number).
Besides, comma in text (though quoted) generates an error.
The same file is imported normal with Acces97 .

It often cannot do the automatic detection of the data types.

If it's going to be done often and the column layout is always the
same, then you can save an import spec and use that with your
TransferDatabase command.

To create an import spec, start the import manually, but go to the
ADVANCED dialog and lay out your fields appropriately, and then save
the spec. Then it can be used as the appropriate argument for the
TransferDatabase command.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Vladislav Moltchanov]

>

It often cannot do the automatic detection of the data types.

If it's going to be done often and the column layout is always the
same, then you can save an import spec and use that with your
TransferDatabase command.

To create an import spec, start the import manually, but go to the
ADVANCED dialog and lay out your fields appropriately, and then save
the spec. Then it can be used as the appropriate argument for the
TransferDatabase command.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

1. Yes, after all I start to use specification created with "advanced"
dialog box.
2. The imported files are being modified quite often, this is why this
way is not convinient.
3. The following text file (the first 3 line + some more ) is processed
correctly by Access97 and could be processed only with specification by
Access 2000:

"IR","IC","DBNAME","DBTYPE","DBLABEL","VALRULE","V ALTEXT","DEFAULT","FMT","INPMASK","DBLEN","ITEM"

, , "CODE_SUB", "Text", "CODE_SUB", " ", " ", " ", "$CHAR8.", " ", 8, 2
, , "SDD01", "Integer", "SDD01", " ", " ", " ", "2.", "##;0;#", 2, 5
more lines
1, , "NTF0601", "Byte", "NTF0601", "1 or 2 or 9", "1 or 2 or 9", " ",
"F1.", "0;0;#", 1, 22
2, , "NTF0602", "Byte", "NTF0602", "1 or 2 or 9", "1 or 2 or 9", " ",
"F1.", "0;0;#", 1, 22

4. In the case above the pattern ', ,' at the beginning seems to cause
the problem. At least, when doing specification with "advanced" Access
"thinks" these are text fields.

5. However, another file induces a fully correct specification (no
correction is needed to what Access suggests). Nevertheless, using
TransferText without import specification generates error.

I can send these 2 files to anyone who wants test them.

[David W. Fenton]

Vladislav Moltchanov <vl******************@ktl.fi> wrote in
news:40***************@ktl.fi:

I can send these 2 files to anyone who wants test them.

Looks to me like you'll need to process them manually, with basic
file I/O commands.

Dmitri's TextExport class does the job of exporting via those
methods, but is there a corresponding pre-built class for importing?
I'm not sure there could be, but it's the kind of thing I can never
wrap my head around, because the file I/O commands are so unlike
standard VB syntax that I just don't quite get them.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc