Fastest Method for importing & writing a table

[BAWC01]

Hi

I have to read a string from a file and then insert it into a table.

I have the following code :

Function SMP_ULD_ENTRY(TxtString As String)
Dim dbs_Current As Database
Dim rst_SMPBB As DAO.Recordset
Dim Temp As Date
Dim sFields() As String
Dim TmpFields As String
Dim count As Integer
Dim SMP_Date As Date

Set dbs_Current = CurrentDb
Set rst_SMPBB = dbs_Current.OpenRecordset( _
      "SELECT SMP_ULD_ENTRY1, SMP_ULD_ENTRY2, SMP_ULD_ENTRY3, SMP_ULD_ENTRY4, SMP_ULD_ENTRY5, SMP_ULD_ENTRY6, SMP_ULD_ENTRY7, SMP_ULD_ENTRY8, SMP_ULD_ENTRY9, SMP_Date, SMP_Time From SMP_ULD_ENTRY", dbOpenDynaset)

sFields = Split(TxtString, "'")
 With rst_SMPBB
 .AddNew
   !SMP_Date = Mid(TxtString, 1, 2) & "/" & Mid(TxtString, 4, 2) & "/" & Mid(TxtString, 7, 2)
   !SMP_Time = Mid(TxtString, 10, 8)
   For count = 1 To UBound(sFields)
    Debug.Print Trim(sFields(count))
      rst_SMPBB.Fields(count) = sFields(count)
   Next count
.Update
End With
End Function

Is this the fastest method for importing and writing to a table as I have to do this on a very large file

Thanks

Phil

 

[JerryKlmns]

A dbForwardOnly type recordset would speed up things for a start.
But I use a schema.ini file describing recordlayout of the file, linking it as a table and execute a bulk insert transaction returning no records on an opened connection. All this using ADODB connection and recordsets plus ADOX to link the table. No validation on data though...

 

[BAWC01]

Jerry

Thanks for that, could you give me an example of how I could use this in my example.

Thanks

Phil

 

[JerryKlmns]

Do a search for schema.ini, there are about 70 matches.
Or take a look here thread181-1085235