We have this VB.net code that connects to an MS Access Database and tries to insert a new entry:
Dim conn As New OleDbConnection
conn.ConnectionString = "Provider="Microsoft.ACE.OLEDB.16.0; Data Source=" & DATABASE_PATH & ";Jet OLEDB:Database Password=pass;"
conn.Open()
Dim SqlString As String = "INSERT INTO tblNotes" &
" ([NotesNumber" &
"], [NotesTitle" &
"], [HasAdditionalLogic" &
"], [TypeId]) Values (?,?,?,?)"
Dim cmd As New OleDbCommand(SqlString, conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("NotesNumber", 1234)
cmd.Parameters.AddWithValue("NotesTitle", "the title")
cmd.Parameters.AddWithValue("HasAdditionalLogic", False)
cmd.Parameters.AddWithValue("TypeId", 14)
cmd.ExecuteNonQuery()
conn.close()
Nothing too fancy, right? This code worked fine with Access 2016 installed.
Now with the recent upgrade to Office 365 the line
cmd.ExecuteNonQuery()
causes this error:
'Microsoft.ACE.OLEDB.16.0' failed with no error message available, result code: E_OUTOFMEMORY(0x8007000E)
Googling for that error message lead to several ideas like using Integer instead of Long Integer in the database, but that did not help either.
And personally, I doubt that the root cause is a lack of memory because the machine has 32GB RAM installed and is set to 32GB of Virtual Memory. The process itself never uses more than 100MB, Windows Process Explorer tells us that the whole RAM uses about 5GB total. So I just cannot believe we are actually running out of memory here.
Any idea?
Update:
Okay, we seem to have found the underlying issue here.
You see this line:
cmd.Parameters.AddWithValue("TypeId", 14)
In the Access database, the field "TypeId" has been defined as a Primary Key of Data Type "AutoNumber" and Field Size "Long Integer".
Now, if we write the code like this:
cmd.Parameters.AddWithValue("TypeId", 14I)
it runs without an error, but as soon as we change it to:
cmd.Parameters.AddWithValue("TypeId", 14L)
we get the crash.
Let me state again that the code with a Long
works fine with Access 2016, it crashes with the Access from Office 365.
I may be mistaken, but this seems like a bug in Access.
Of course we can now change all the app code from Long
to Integer
(or UInteger
), but this seems like treating the symptoms instead of the root cause.
Can somebody confirm this? Or tell me why exactly this happens? Using a Long
seems to be correct to me, using an Integer
instead seems pretty wrong to me.
To anybody who might face the same problem: we "fixed" the issue by installing "Microsoft Access Database Engine 2010 Redistributable" https://www.microsoft.com/en-US/download/details.aspx?id=13255 and then using
Microsoft.ACE.OLEDB.12.0
instead of
Microsoft.ACE.OLEDB.16.0
That did the trick.
The hint with .add
instead of .addWithValue
did not make any difference.
User contributions licensed under CC BY-SA 3.0