473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

ACC2000: Import with TransferText



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

Nov 12 '05 #1
6 2979
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

Nov 12 '05 #2


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 .

Nov 12 '05 #3
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 .

Nov 12 '05 #4
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
Nov 12 '05 #5
>
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.

Nov 12 '05 #6
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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
2
by: ms | last post by:
In Access 2000, I am using... 'Import Raw text to stageBMIDLog table. DoCmd.TransferText acImportFixed, "BMIDLog Link Specification", "stageBMIDLog", file ....to import raw records into a...
6
by: Vladislav Moltchanov | last post by:
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...
4
by: khutch | last post by:
Not that up on MS Access. I understand that the TransferText command can be used to import csv files into a database. Question: Does the text file have to mirror the alignment of the table columns...
3
by: Typehigh | last post by:
I am a pretty saavy user, but I am having a real problem with the DoCmd.TransferText operation. I manually used the File>Get External Data> Import routine to set up an import specification. It...
3
by: holdemfoldem | last post by:
Hi. I'm new to this board and have a few questions about using the method referred to in the topic of this message. I have manually transferred over 1/2 million records from a text file into my...
3
by: Henrootje | last post by:
Hello Access-guru's, wizards and helpful people! I am trying to import several textfiles into several tables. Now I thought the following code up which works swell: Dim Importspecification,...
4
by: WillMiller | last post by:
Hi, I'm currently attempting to write a small Access Database which carries out the following tasks : 1. Imports Submission files (of a text variety) to a table using TransferText on a daily...
3
by: derfer | last post by:
I am trying to import a .inf file into access (the file comes from the output of some 3rd party software). I have managed to convert the file by opeing it in notepad and re-saving then a seperate...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.