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

Paul:
Here is some code I've used. It's modified from some VB.net code but it
should work appropriately using ADO. 

Public sub ImportTextFile(strConnectionString as string, strFilePath as
string, strFileName as string, Optional strHeader as string = "NO")
	Dim cmdTestCommand As ADODB.Command
	Dim connTestConnection as ADODB.Connection
	Dim strHeader as string
	Dim strSQL as string
	set connTestConnection = new ADODB.Connection
	connTestConnection.ConnectionString = strConnectionString
	connTestConnection.Open
	set cmdTestCommand = new ADODB.command
	set cmdTestCommand.ActiveConnection = connTestConnection
        
      strSQL = "Insert into [" & strTableName & "] Select [BarCode],
[Price] FROM [Text;DATABASE=" & strFilePath & ";HDR=" & strHeader &
"].[" & strFileName & "]"
      cmdTestCommand.CommandText = strSQL
      cmdTestCommand.CommandType = adCmdText
	cmdTestCommand.Execute , , adExecuteNoRecords
	connTestConnection.Close
	set cmdTestCommand = nothing
	set connTestConnection = nothing
End Sub

This assumes a file with no header row. If you have a header row, you
need to change strHeader = "YES" and the field names in the header need
to match those in the table or you need to modify the Insert statement
to alias the files field names to match the table field names.
You need to supply a string for the Connection String to your mdb file,
a string for the Path of the text file and a string for the name of the
text file. 
I left out error checking but certainly you'd want to make sure the
files exist first. Also, you need to make sure the text file is properly
formatted and all the data matches your table definition.

Hope this helps.

James Barash

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Wednesday, January 28, 2004 4:28 AM
To: dba-vb
Cc: accessd
Subject: [AccessD] [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com