ACC2000: Import with TransferText

**Allen Browne** · Nov 12 '05, 10:11 PM

Re: ACC2000: Import with TransferText

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" <vladislav.molt chanov@ktl.fi> wrote in message
news:407258E7.2 6EE6F8E@ktl.fi. ..[color=blue]
>
>
> I have discovered a couple of years ago, that import with
> DoCMD.TransferT ext 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
>[/color]

**Vladislav Moltchanov** · Nov 12 '05, 10:11 PM

Re: ACC2000: Import with TransferText

Allen Browne wrote:
[color=blue]
> 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).
>[/color]

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** · Nov 12 '05, 10:11 PM

Re: ACC2000: Import with TransferText

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.TransferT ext acExportDelim,, "qry4Export ", "c:\test.tx t", true
DoCmd.TransferT ext acImportDelim,, "aTestImpor t", "c:\test.tx t", 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" <vladislav.molt chanov@ktl.fi> wrote in message
news:4072A10F.B F64B995@ktl.fi. ..[color=blue]
>
>
> Allen Browne wrote:
>[color=green]
> > If you are referring to the fact that Access 2000 cannot figure out how[/color][/color]
to[color=blue][color=green]
> > TransferTEXT if the file type is not registered with Windows, no the[/color][/color]
problem[color=blue][color=green]
> > 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).
> >[/color]
>
> The file is a standard ASCII text file (<name>.txt), comma delimited,[/color]
with text[color=blue]
> fields quoted. By the way, with ACCESS 2000 it is possible to import it[/color]
using[color=blue]
> menu, however, not with code.It seems Access 2000 can't do automatic[/color]
detection[color=blue]
> of type (text or number) just by fact , field is quoted (text) or not ([/color]
number).[color=blue]
> Besides, comma in text (though quoted) generates an error.
> The same file is imported normal with Acces97 .[/color]

**David W. Fenton** · Nov 12 '05, 10:11 PM

Re: ACC2000: Import with TransferText

Vladislav Moltchanov <vladislav.molt chanov@ktl.fi> wrote in
news:4072A10F.B F64B995@ktl.fi:
[color=blue]
> 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 .[/color]

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
TransferDatabas e 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
TransferDatabas e command.

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

**Vladislav Moltchanov** · Nov 12 '05, 10:12 PM

Re: ACC2000: Import with TransferText

>[color=blue]
> 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
> TransferDatabas e 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
> TransferDatabas e command.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

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","DBNA ME","DBTYPE","D BLABEL","VALRUL E","VALTEXT","D EFAULT","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** · Nov 12 '05, 10:12 PM

Re: ACC2000: Import with TransferText

Vladislav Moltchanov <vladislav.molt chanov@ktl.fi> wrote in
news:4073AA5A.E CEB65B7@ktl.fi:
[color=blue]
> I can send these 2 files to anyone who wants test them.[/color]

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

ACC2000: Import with TransferText

ACC2000: Import with TransferText

Comment

Comment

Comment

Comment

Comment

Comment