I've been perusing some hep forums and some help books but cant seem to get my head wrapped around this. My task is to read data from two text files and then load that data into an existing MS Access 2007 database. So here is what i'm trying to do:
- Read data from first text file and for every line of data add data to a DataTable using CarID as my unique field.
- Read data from second text file and look for existing CarID in DataTable if exists update that row. If it doesnt exist add a new row.
- once im done push the contents of the DataTable to the database.
What i have so far:
Dim sSQL As String = "SELECT * FROM tblCars"
Dim da As New OleDb.OleDbDataAdapter(sSQL, conn)
Dim ds As New DataSet
da.Fill(ds, "CarData")
Dim cb As New OleDb.OleDbCommandBuilder(da)
'loop read a line of text and parse it out. gets dd, dc, and carId
'create a new empty row
Dim dsNewRow As DataRow = ds.Tables("CarData").NewRow()
'update the new row with fresh data
dsNewRow.Item("DriveDate") = dd
dsNewRow.Item("DCode") = dc
dsNewRow.Item("CarNum") = carID
'about 15 more fields
'add the filled row to the DataSet table
ds.Tables("CarData").Rows.Add(dsNewRow)
'end loop
'update the database with the new rows
da.Update(ds, "CarData")
Questions:
In constructing my table i use "SELECT * FROM tblCars" but what if that table has millions of records already. Is that not a waste of resources? Should i be trying something different if i want to update with new records?
Once Im done with the first text file i then go to my next text file. Whats the best approach here: To First look for an existing record based on CarNum or to create a second table and then merge the two at the end?
Finally when the DataTable is done being populated and im pushing it to the database i want to make sure that if records already exist with three primary fields (DriveDate, DCode, and CarNum) that they get updated with new fields and if it doesn't exist then those records get appended. Is that possible with my process?
tia AGP
