update MDB from datatable

[griffitd]

Hi i have the following code, but it is very slow.

If there a better (Quicker) way?

Thanks

cDB_SpecDB.BuildSPCall("SelectBarCodeDetailsForFactoryAll")
cDB_SpecDB.AddSQLParam("@FactoryDescription", SqlDbType.VarChar, ParameterDirection.Input, cmbCMIFactory.Text)
cDB_SpecDB.AddSQLParam("@IsCMT", SqlDbType.Bit, ParameterDirection.Input, False)
dtBarCodes = cDB_SpecDB.Execute_to_DataTable(True)

iCount = 1
If dtBarCodes.Rows.Count > 0 Then
'Delete Current BarCodes from local table
cdb_MDB.ExecuteQuery("DELETE * FROM BarCodeDetails2")
For Each dr As DataRow In dtBarCodes.Rows
SetStatusBar("Updating Barcode " & iCount & " of " & dtBarCodes.Rows.Count)
cdb_MDB.ExecuteQuery("INSERT INTO BarCodeDetails2 " & _
"(UPC, StyleName, SeriesNo, PackColourDesc, SizeDesc, DeptName, MsColCode, " & _
"LabelColour, PackSize, BarCode, StyleCode, PackCode, CustCode)" & _
" VALUES " & _
"('" & dr("UPC") & "','" & dr("StyleName") & "','" & dr("SeriesNo") & "','" & _
dr("PackColourDesc") & "','" & dr("SizeDesc") & "','" & dr("DeptName") & "','" & dr("MsColCode") & "','" & _
dr(8) & "'," & dr("PackSize") & ",'" & dr("BarCode") & "'," & dr("StyleCode") & "," & _
dr("PackCode") & "," & dr("CustCode") & " )")
iCount += 1

Next
End If

 

[ralphtrent]

not sure if this would work, but maybe you can execute multiple inserts at once.

Meaning build a string of like 10 inserts and when your counter hits 10, execute all at once, then do the next ten and so on.

I am not sure if the ExecuteQuery method has to connect to the db every time it runs or if it has a constant connection.

Again, this is just an idea, I have not clue if you an send multiple statements to the method. I think you would have to separate each with a semicolon ( though.