VB2005 Import Fixed Width text file into Access2007 table...almost?

I am trying to load a text file into an Access 2007 table. I know you can read the file line by line and then create a record out of each line. i was trying to see if this could be done with an INSERT INTO rather than cyclying through all lines of text. My text file is not character delimited but rather by fixed column width. For example:

Date  Speed    Weight  CarID    Fuel
1120  200      10000   T230     200
1112  215      11000   F3AE     160

The data in the example has spaces for readability but in reality the data are clumped together like so

112020010000T230200
111221511000F3AE160

Anyway i was attempting

    Dim sImportFolder As String = "C:\MyData"
    Dim sSource As String = "C:\data.accdb"
    Dim sImportFile As String = "week.txt"

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";Persist Security Info=True;Jet OLEDB:Database Password=blah")
    AccessConn.Open() 'open the connection to the database

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblData] ([PtDate], [PtSpeed], [PtWt], [PtCar], [PtFuel]) SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=" & sImportFolder & ";].[" & sImportFile & "]")
    AccessCommand.Connection = AccessConn
    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

I cant figure out how to tell the command how the data is structured. I k开发者_开发技巧now you can use a schema file but there's got to be a way to do this all through code.

AGP

---

There is a similar question on SO here:

Read fixed width record from text file

Basically, the answer is that there isn't something simple you can do in the code to specify the schema and have it broke up for you. What you would need to do is either loop through each row, pulling out the data using SubString and then doing one insert into Access per row (not terribly efficient) or you could build a DataTable in the loop and then do an insert into the Access database using the DataTable. To build the DataTable, you will still need to parse your data (either using SubString or a RegEx).