I have a vb.net program that updates an access DB. We have previously used Access 2007 and Access 2003. When we use 2010, the vb.net program slows to a crawl. To remove any external factors my real program may cause, I created a simple test program to update rows in a finite loop. I created a simple DB (an access 2007-10 type .accdb and a access 2003 .mdb) with TABLE1 and FIELD1 with 5000 text rows in it. The VB program is as follows
Dim cmdStr As String = "", tmpCounter As Integer = 0
Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\db.accdb;")
'Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db2003.mdb;")
Dim cmd As New OleDbCommand(cmdStr, conn)
Try
conn.Open()
Catch ex As Exception
MsgBox("Could not open database > " & ex.Message)
Exit Sub
End Try
ListBox1.Items.Add("Start Time" & Now)
Application.DoEvents()
For tmpCounter = 0 To 1000
Try
cmd.CommandText = "update table1 set field1 = 'hello'"
cmd.ExecuteNonQuery()
Catch ex As Exception
ListBox1.Items.Add("Error: " & ex.Message)
End Try
Next
conn.Close()
conn = Nothing
ListBox1.Items.Add("End Time" & Now)
MsgBox("done")
IN all cases, Access is installed on the local machine I am testing
If I switch the connection string to use jet and the db2003.mdb, to update 5000 rows 1000 times takes about 8 seconds, If I use the 12.0 con str on a Access 2007 machine, it takes about 9 seconds. On a Access 2010 machine, it takes 34 seconds.
This makes no sense, I have tried this on multiple machines with similar results. ON the machines with 2003 or 2007 the vb program screams, 2010 machines become totally unusable. I have tried various updates and service packs on the access 2010 machines... doesn't make any diff.. Using a New OleDbCommand(cmdStr, conn) instead of using the same instance makes no diff either. I have tried using transactions, no luck. This is easy to reproduce. I have tried VS2010 and VS2012, same results. Where is the bug, vb (or interface) or access??
Thanks for your time,
BUC
Dim cmdStr As String = "", tmpCounter As Integer = 0
Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\db.accdb;")
'Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db2003.mdb;")
Dim cmd As New OleDbCommand(cmdStr, conn)
Try
conn.Open()
Catch ex As Exception
MsgBox("Could not open database > " & ex.Message)
Exit Sub
End Try
ListBox1.Items.Add("Start Time" & Now)
Application.DoEvents()
For tmpCounter = 0 To 1000
Try
cmd.CommandText = "update table1 set field1 = 'hello'"
cmd.ExecuteNonQuery()
Catch ex As Exception
ListBox1.Items.Add("Error: " & ex.Message)
End Try
Next
conn.Close()
conn = Nothing
ListBox1.Items.Add("End Time" & Now)
MsgBox("done")
IN all cases, Access is installed on the local machine I am testing
If I switch the connection string to use jet and the db2003.mdb, to update 5000 rows 1000 times takes about 8 seconds, If I use the 12.0 con str on a Access 2007 machine, it takes about 9 seconds. On a Access 2010 machine, it takes 34 seconds.
This makes no sense, I have tried this on multiple machines with similar results. ON the machines with 2003 or 2007 the vb program screams, 2010 machines become totally unusable. I have tried various updates and service packs on the access 2010 machines... doesn't make any diff.. Using a New OleDbCommand(cmdStr, conn) instead of using the same instance makes no diff either. I have tried using transactions, no luck. This is easy to reproduce. I have tried VS2010 and VS2012, same results. Where is the bug, vb (or interface) or access??
Thanks for your time,
BUC
