SELECT RECORDS FROM .TXT AND IMPORT INTO ACCESS 2

[jane30]

Hi, I have a monthly routine automation job: importing data from .txt file into Access97 database, processing the data and FTPing to remote users. It works perfectly fine now. However, the target .txt file is growing larger and larger. I'm sure one day it'll get to the point that Access97 database can't handle. So, I'm wondering is it possible to only import the two-years worth of data (what I need), instead of the whole. txt, into my Access97 database by setting a criteria in my import job. Is it feasible in Access97, using VBA code? Any suggestions are welcome.

Thanks a bunch in advance.

 

[Malchik]

hi,
its possible if you have something like a Date in your text file. Working with big flat text file its always a bit slow because you have to browse the file from the top to the bottom... Here is what you can do


'let assume that each line start wit the date (yyyy-mm-dd)
public sub Import(sDate as string)
dim stmp as string
open "RawData.txt" for input as #1
while not eof(1)
line input #1, stmp
if left(stmp,10)=sdate then
'update the database
endif
wend
close #1

hope it would help.
Mal'chik

 

[jane30]

Cool, Mal'Chik. thanks alot.

 

[mflancour]

Hey, thanks this'll help me too.

 

[bandi200]

Hi Jane,

I am working on a similar project wherein I ftp '|' delimited file from Mainframe. I need to write an automatic job that loads/imports this data into ACCESS.

( The final objective is to generate Crystal reports with this data. )

Could you pls advice me as how to do this and if possible, could you send me the code.

warm rgds,
Bandi.