[dba-VB] Text File Into Access Using Visual Basic 6

Assuming you're text file looks like this:

12354324532,$3.15
53243242341,$4.25

(where there is a carriage return line feed between each record) you could
do this:

Dim f as Long
Dim strTemp as string
dim RecordArray() as String
dim FieldArray() as String
dim rs As ADODB.Recordset
dim cnn as ADODB.Connection
dim strSQL as String
dim i As Long
f=FreeFile
Open "C:\PathToFile\FileName.txt" for Binary Access Read As f
strTemp=space(lof(f))
get f,,strTemp
close f
RecordArray=Split(strTemp, vbcrlf)
Set cnn=new adodb.connection
'Connect to your mdb
Set rs=new ADODB.Recordset
strSQL="SELECT Barcode, Price FROM tblTableName"
rs.open strsql,cnn,adopenkeyset, adLockOptimistic
For i=0 to Ubound(RecordArray)
	FieldArray=Split(RecordArray(i),",")
	rs.AddNew
	rs.fields(0).value=fieldArray(0)
	rs.fields(1).value=FieldArray(1)
	rs.Update
next i
rs.close
set rs=nothing
cnn.close
set cnn=nothing

	

-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net]
Sent: Wednesday, January 28, 2004 3:28 AM
To: dba-vb
Cc: accessd
Subject: [dba-VB] Text File Into Access Using Visual Basic 6


To all,
 
Can anyone tell me (preferably with sample code, if possible), what is the
fastest way to import a .txt file into a predefined table in access via
Visual Basic 6.
 
My .txt file contains roughly 365,876 records with two fields - Barcode,
Price.  This has to be imported into an Access table also containing two
fields Barcode (text), Price (Number, Double, Fixed, 2 decimal places).  
 
The .txt file is comma delimited.  I'm using Access XP and Visual Basic 6.0
as the front-end.
 
Thanks in advance for any help as, this project needs to be ready for Monday
morning......
 
Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com