Hi
I have a need to insert records to either a database and currently using Microsoft Access.
I have done some code, and roughly 10,000 records takes 12secs (using Access), I am wondering if I can improve my code in any way. I believe you cannot do a bulk insert to Microsoft Access, I am not sure if you can with SQL Server Express.
My code is below and I am looking for/to see if there are ways to speed up the existing code and/or another way of acheieving a faster insert rate.
The code is currently adding static test data and I am aware that i should be using parameters for the fields.
Any suggestions please, will SQL Express be any quicker? My other option is MySQL.
Thanks
Neil
What I have tried:
<pre>Dim connetionString As String
Dim connection As OleDbConnection
Dim oledbAdapter As New OleDbDataAdapter
Dim sql As String
connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Neil Cooley\Desktop\Database1.accdb"
connection = New OleDbConnection(connetionString)
Try
connection.Open()
Dim stopwatch_1 As New Stopwatch
stopwatch_1.Start()
Dim getDate As Date = Date.Now
For i As Integer = 1 To 10 Step 1
sql = "INSERT INTO Lines VALUES('TAG " & i.ToString & "','" & i.ToString & "','192','" & getDate & "')"
oledbAdapter.InsertCommand = New OleDbCommand(sql, connection)
oledbAdapter.InsertCommand.ExecuteNonQuery()
Next
stopwatch_1.Stop()
MsgBox("Row(s) Inserted !! - " & stopwatch_1.ElapsedMilliseconds.ToString)
Catch ex As Exception
MsgBox(ex.ToString)
End Try