Very new to VB and would like some advice on how to improve performance of this utility.

Basically it reads a text file, which has 120k lines, and then separates out fields within the text line into an Access database for processing later.

I have tried two approaches, one using a StreamReader and the other using ADO.net to create a data set. The format of the file doesn't lead itself to ADO.Net but it still loads.

In both cases it takes about 20minutes to create the database file.

Here is an extract from one section of the code....

VB Code:
  1. Dim strVTVreport As String = "Z:\xxx\xxx.txt"

  2.         Dim sr As IO.StreamReader = New IO.StreamReader(strVTVreport)

  3.         Dim Var1-Var10As String

  4.  
  5.         Dim dbConnection As OleDbConnection

  6.         Dim dbCommand As OleDbCommand = New OleDbCommand

  7.  
  8.         dbConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=Z:\xxx\DataBases\xxx.mdb")

  9.         dbCommand.Connection = dbConnection

  10.         dbConnection.Open()

  11.  
  12.         dbCommand.CommandText = "Delete * From [xxx Table]"

  13.         dbCommand.ExecuteNonQuery()

  14.  
  15.         strLineIn = sr.ReadLine

  16.  
  17.         Debug.WriteLine(DateTime.Now)

  18.  
  19.         While Not strLineIn = Nothing

  20.  
  21.             ' Debug.WriteLine(strLineIn.Length & "    " & strLineIn)

  22.  
  23.             If strLineIn.Length = 118 Then

  24.  
  25.                 If strLineIn.Substring(51, 1).ToString = ":" Then

  26.  
  27.                     strLineLen = strLineIn.Length

  28.                     strVar1 = strLineIn.Substring(1, 6).ToString

  29.                     .

  30.                     .

  31.                     .

  32.                     .

  33.                     strVar10= strLineIn.Substring(113, (strLineIn.Length - 113)).ToString

  34.  
  35.                     '

  36.                     '   Build a SQL statement

  37.                     '

  38.  
  39.                     Dim strSQL As String = "INSERT INTO [xxx]( var1....var10)" & _

  40.                                         " VALUES(@Var1....@var10)

  41.  
  42.                     '

  43.                     '   Now that we have defined the SQL statement and some positional parms assign some values to them

  44.                     '

  45.  
  46.                     dbCommand.Parameters.Add("@Var1", OleDb.OleDbType.VarChar, 50).Value = strVar1

  47.                     .... more lines

  48.                     

  49.                     dbCommand.Parameters.Add("@Var10, OleDb.OleDbType.VarChar, 50).Value = strVar10

  50.  
  51.                     '

  52.                     ' Define and SQL statement and execute it. Once were done clear all the Parameter.Add values for the next iterations

  53.                     '

  54.                     Try

  55.  
  56.                         dbCommand.CommandText = strSQL

  57.                         dbCommand.ExecuteNonQuery()

  58.                         dbCommand.Parameters.Clear()

Thanks in advance

RibTime