Access - Link to a BASIC DOS file

[dicesare]

Hi all,

Access 2000 allows me to link an external text file generated with Basic
PDS 7 (DOS, aka Random file), using a fixed format.

Unfortunately, all numbers (stored in IEEE format) are unreadable. When Access
finds a CHR$(13) or CHR$(10) - they both may be into a number -, it interpretes
the char as a new line begin.

Any suggestion?

TIA

 

[MichaelRed]

If you read the file instead of attempting to LINK to it, you can get all of the info in a record directly. You will STILL need to find the correct conversion of the IEEE format for placement in a table as a numeric. But that's a seperate problem.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dicesare]

Reading and interpreting the file is not a problem. I can easily do that. But this way I will have 2 separate files. I need to LINK the .TXT file because it is continuously updated by another application. I will use the Access file only for reporting, and I cannot say to the user(s) to import the file (tenths or hundreds thousands records) each time.

 

[MichaelRed]

Well,

Then you will (aparenty) need to find an alternative way to do your reporting.

If you search these forums, you should be able to find some efficient methods for reading and parseing the text file. Once the whole file is in 'memory', parseing it into records should be a fast / efficient process, thus not really be a hinderance. If the only point to the Ms. A. processing is to generate reports, I see no real reason to not use this approach. Unless, of course, you are satisified with the fixed pitch manual coding process required by "DOS" basic.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jfischer]

This link may help you determine what it and is not possible with linking your .TXT file.

http://msdn.microsoft.com/library/d...ff2000/html/achowImportDelimitedTextFileS.asp

 

[dicesare]

thanks, jfischer

MSDN says:

"If all the records in a fixed-width text file are the same length, there can be an embedded row separator (such as a carriage return and linefeed) in the middle of a record. If the records aren't all the same length, embedded row separators should not be used because Microsoft Access will treat the embedded row separator as the end of the record."

Well, my records have ALL the same length. ALL the fields are fixed-lenght. According to MSDN, the link SHOULD work. But it doesn't. As ms-access encounters a CR or a LF, it thinks it is a new record.

 

[dicesare]

for MichaelRed.

thanks for your help. I currently use the method you suggest: I import the file into the mdb and then start the report, but it is a very slow process (due to high number of records). This is the reason why I'm trying to link the TXT file instead or parsing (importing) it.

BTW, I do not like "DOS" printing... ;-)

 

[MichaelRed]

Re-Read what I posted previously. MOST of the delay in your method is the line inut. Simply by reading the file in a single "Chunk" you can improve the performance by a factor of 2 or 3 or more. If you are not going to consider the advice given, why bother to post the question?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dicesare]

Sorry Michael, I simply didn't understand what you suggested me. Now I will try to use the "Chunk" method. Sorry again.