I am running a database with about 200,000 records, so not too big. It runs fine except when I try to update one of the tables. Here is the code:
Private Sub Add_History()
Dim cb5 As New OleDb.OleDbCommandBuilder(da5)
Dim dsNewRow5 As DataRow
Dim sql5 As String
sql5 = "SELECT * FROM tblHistory order by ID"
dsNewRow5 = ds5.Tables("History").NewRow()
ds5.Tables("History").Rows.Add(dsNewRow5)
da5.Update(ds5, "History")
da5.Dispose()
ds5.Clear()
da5 = New OleDb.OleDbDataAdapter(sql5, con)
da5.Fill(ds5, "History")
da5.Update(ds5, "History")
New_hist()
errr:
End Sub
Private Sub New_hist()
Dim cb5 As New OleDb.OleDbCommandBuilder(da5)
Dim x
On Error GoTo errr
ds5.Tables("History").Rows(Hist_Rows - 1).Item(6) = contactID
ds5.Tables("History").Rows(Hist_Rows - 1).Item(1) = DateTime.Now
ds5.Tables("History").Rows(Hist_Rows - 1).Item(3) = username
da5.Update(ds5, "History")
End Sub
Its the da5.Fill(ds5, "History") that takes about 10 seconds to fill. If there are no new updates, ie I dont use
dsNewRow5 = ds5.Tables("History").NewRow()
ds5.Tables("History").Rows.Add(dsNewRow5)
da5.Update(ds5, "History")
then it fills instantly. It seems that when you want to add a record the fill takes forever.
Anyone got any ideas how I can speed this process up.