none
OleDB Connection reopen failed after mdb file compacted(System.Data.OleDb.OleDbException (0x80004005): unspecified error) RRS feed

  • Question

  • It is winform program, using access 2003 format database for recording state of equipment , the program will automatically compact the database to improve performance by referencing "Microsoft Jet and Replication Objects 2.6 library" COM.

    the program starts, it is normal operation for some time, the running for some time program compacted database normally, program throws an error some times that is "System.Data.OleDb.OleDbException (0x80004005): Unspecified error. " when the program reopen connection to database, then my program can not access database unless the re-start the program. the program can run normally for some time after restarting, then the problems repeated mentioned above.

    I have no idea for cause of the problem, the program run on different machines(with winXP or win7) to get the same phenomenon. I also checked on the Internet, but the solutions are all for asp.net, and not addressed windform's. About the temporary folder and startup directory permissions issue I have tried, it's useless.

    Kindly advise,thanks.

    Here is my code after simplification:

    Imports System.Data.OleDb
    Imports System.Data.Common
    Imports System.Data
    Imports System.IO

    Public Class Form1
        Public WithEvents m_Timer As New System.Windows.Forms.Timer
        Public isDoingFlag As Boolean

        Private m_conn As OleDbConnection = Nothing
        Private m_cmd As OleDbCommand = Nothing

        Private filename As String = Application.StartupPath.TrimEnd("\".ToCharArray()) & "\PSD Monitor.mdb"
        Private strTmpFile As String = Application.StartupPath.TrimEnd("\".ToCharArray()) & "\tmpPSD Monitor.mdb"

        Private ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Jet OLEDB:Database Password=mysql" ';OLE DB Services = -4"

        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            m_conn = New OleDbConnection(ConnectionString)
            m_cmd = m_conn.CreateCommand()
            m_cmd.CommandType = CommandType.Text

            m_Timer.Interval = 10
            m_Timer.Start()
        End Sub

        Private Sub Form1_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
            If m_Timer IsNot Nothing Then
                m_Timer.Stop()
                m_Timer.Dispose()
                m_Timer = Nothing
            End If
        End Sub

        Private Sub m_Timer_Tick(sender As Object, e As System.EventArgs) Handles m_Timer.Tick
            m_Timer.Stop()
            If isDoingFlag Then Exit Sub
            isDoingFlag = True
            Dim InsertCount As Integer = 0
            Try
                Debug.WriteLine("begin to insert 50 times...")
                m_conn.Open()
                Do
                    InsertCount += 1
                    If InsertCount >= 50 Then Exit Do
                    If m_conn IsNot Nothing Then
                        m_cmd.CommandText = "insert into t_StateLog ([DeviceCode],[StateID],[StateData],[TriggerTime]) values ('ST',0,True,'2013-08-17 11:17:23.901')"
                        m_cmd.ExecuteNonQuery()
                        Application.DoEvents()
                    End If
                Loop
                Debug.WriteLine("50 times inserted operation finished ")

                m_conn.Close()

                Compact_DB(filename)

                Debug.WriteLine("begin to insert 100 times...")
                InsertCount = 0

                m_conn.Open()   '<----Error raised some times
                Do
                    InsertCount += 1
                    If InsertCount >= 100 Then Exit Do
                    If m_conn IsNot Nothing Then
                        m_cmd.CommandText = "insert into t_StateLog ([DeviceCode],[StateID],[StateData],[TriggerTime]) values ('ST',0,False,'2013-08-16 09:50:31.894')"
                        m_cmd.ExecuteNonQuery()
                        Application.DoEvents()
                    End If
                Loop
                Debug.WriteLine("50 times inserted operation finished" & vbCrLf)
            Catch ex As Exception
                Debug.WriteLine(ex.ToString())
            Finally
                If m_conn IsNot Nothing Then m_conn.Close()
                isDoingFlag = False
                If m_Timer IsNot Nothing Then m_Timer.Start()
            End Try
        End Sub

        Private Function Compact_DB(ByVal filename As String) As Boolean
            Try
                If System.IO.File.Exists(strTmpFile) Then System.IO.File.Delete(strTmpFile)
                Debug.WriteLine("Compacting...")
                Dim Jet_Engine As JRO.JetEngine = New JRO.JetEngine()
                Jet_Engine.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Jet OLEDB:Database Password=mysql", _
                                           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFile + ";Jet OLEDB:Database Password=mysql;Jet OLEDB:Engine Type=5")
                Jet_Engine = Nothing
                System.IO.File.Delete(filename)
                System.IO.File.Move(strTmpFile, filename)
                Debug.WriteLine("Compacted")
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function
    End Class

    Following is error information:

    System.Data.OleDb.OleDbException occurred
      ErrorCode=-2147467259
      Message=unspecified error
      Source=Microsoft JET Database Engine
      StackTrace:
           at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
           at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.OleDb.OleDbConnection.Open()
           at WindowsApplication1.Form1.m_Timer_Tick(Object sender, EventArgs e) in C:\WindowsApplication1\WindowsApplication1\Form1.vb:line 68
      InnerException:

    Monday, August 19, 2013 8:59 AM

Answers

  • I reinstall OS(winXP or win7) and VS on last weekend, but the problem was not addressed still.

    I installed SQL SERVER EXPRESS as database on this monday, my program connected to the database have been testing normally for more than 2 days.

    It looks like it is only one way to solve the problem that i have to give up ACCESS database.

    Wednesday, September 4, 2013 5:21 AM
  • Sorry for you to have give up the Access.

    But Glad for that the problem have a solution at last.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by xiaoxugang Wednesday, September 4, 2013 6:18 AM
    Wednesday, September 4, 2013 5:37 AM
    Moderator

All replies

  • Your connection string looks like it's a little messed up. I would remove the OLE DB Services argument:

    Private ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Jet OLEDB:Database Password=mysql"


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 19, 2013 12:29 PM
  • Hello,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is that it sometimes throw an exception "System.Data.OleDb.OleDbException (0x80004005): Unspecified error." When the program is running.

    If I have misunderstood, please let me know.

    As far as I know this error mostly belongs to permission error.

    System.Data.OleDb.OleDbException: Unspecified error while calling the Connection.Open method on an Access or Excel file. This is an issue you could face while opening Excel or Access files as data sources using OleDb Jet.

    Cause Number 1

    Impersonated account doesn’t have enough privileges to create the ldb file.

    Resolution: Give the user account create/write privileges to the folder where the excel/access file is placed. If you are using impersonation, give the impersonated account the said privileges.

    Cause Number 2

    Impersonated account doesn’t have enough privileges to create temporary files.

    Description: When you open excel or access files using Jet, it requires creation of temporary files. By default, the user account is given full access to this folder. Now, when you are using impersonation, the impersonated account naturally wouldn’t have access to this folder. Consequently, the Jet engine wouldn’t be able to create the temporarily files while opening connections to excel or access files. Due to this, the OleDb provider throws an exception with no specific message - System.Data.OleDb.OleDbException: Unspecified error.

    Resolution: Give the impersonated account full access to the above mentioned folder.

    Check out the Microsoft support article here:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;825738

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Tuesday, August 20, 2013 8:49 AM
    Moderator
  • Thank you very much for your reply.

    The connection string should be right.

    First, It must be highlight that my program is winform program, rather than web application. the error was raised after the program run normally some time.
     
    Therefore, the case of the link "support.microsoft.com/default.aspx?scid=kb;EN-US;825738"  may not apply to my application.

    I tried to give the user account create/write privileges to the folder where the access file is placed,and give the user account full access privileges to the folder "%USERPROFILE%\AppData\Local\Temp", but the problem still occurs.

    I found a strange phenomenon, I have give the user account full controlling privileges for mdb file, and then run the program, the program compacted the database normally, but the new database file lost full controlling privileges to the user account, I do not know whether or not  it is the cause of the problem.

    Wednesday, August 21, 2013 2:47 AM
  • Hello,

    As your description, you can try to give the user account full controlling privileges for the new mdb file.

    Wednesday, August 21, 2013 10:34 AM
  • I don't know what folder the database is located in, but as Fred mentioned the user must have full permissions to the folder where the database is located. This is required in order to create/update/delete the corresponding .LDB file that is created when the Access database is opened.

    Also, if compacting the database fixes the problem then it sounds like you may be experiencing some database corruption. The is more likely to occur when the database is located on the network.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, August 21, 2013 1:23 PM
  • The database(mdb file) is located in application startup path that is "C:\visual studio 2010\WindowsApplication1\WindowsApplication1\bin\Debug\".

    According to your advice, I added some codes to set "full control" privileges for every user or group of  folder the database is located in.

    These codes were added behind "Compact_DB(filename)" statement, Here is my code:

    '______________________________________________

    'original codes

    Compact_DB(filename)

    Dim sec As DirectorySecurity = System.IO.Directory.GetAccessControl(Application.StartupPath, AccessControlSections.All)
    For Each objRule As FileSystemAccessRule In sec.GetAccessRules(True, True, GetType(System.Security.Principal.NTAccount))
        setDirectorySecurity(Application.StartupPath, objRule.IdentityReference.Value, FileSystemRights.FullControl, AccessControlType.Allow)
    Next

    'original codes

    '______________________________________________

    Private Sub setDirectorySecurity(ByVal fileName As String, ByVal account As String, ByVal rights As FileSystemRights, ByVal controlType As AccessControlType)
        Dim dInfo As System.IO.DirectoryInfo = New System.IO.DirectoryInfo(fileName)
        Dim dSecurity As System.Security.AccessControl.DirectorySecurity = dInfo.GetAccessControl()
        Dim iFlags As InheritanceFlags = New InheritanceFlags()
        iFlags = InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit
        Dim NewAccessRule As FileSystemAccessRule = New FileSystemAccessRule(account, rights, iFlags, PropagationFlags.None, AccessControlType.Allow)
        dSecurity.ModifyAccessRule(AccessControlModification.Add, NewAccessRule, True)
        dInfo.SetAccessControl(dSecurity)
    End Sub

     The program runs a few minutes normally, then the problem occurs again, I almost mad!

    Would you like to leave an email address? In order to find out cause of the problem, I will send my program and database to you for testing. Thanks

     

     

     

     

     




    • Edited by xiaoxugang Thursday, August 22, 2013 3:25 PM
    Thursday, August 22, 2013 2:39 PM
  • Hello,

    You can upload your program to:

    https://login.live.com/

    And you can just give us the generated link.

    We will download it and test it.

    Thanks for your understanding.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, August 23, 2013 1:24 AM
    Moderator
  • I have uploaded my program to Skydrive, and got a download link, but i can not submit here.

    I got a message that is "Body text cannot contain images or links until we are able to verify your account" when i submit my reply . so I also need to study how through the account verification.

    Friday, August 23, 2013 8:05 AM
  • Hi,

    I have uploaded my program to skydrvie,you can download it from following link.

    "skydrive.live.com/embed?cid=9E97A9C16404EA21&resid=9E97A9C16404EA21%21107&authkey=ALb7qvqZk4ZSlMA"

    Please copy this link string and paste into URL of internet explorer to begin download.

    Notes:Some tables of the database may include chinese character, it may appear garbled if your windows system is not Chinese version.


    • Edited by xiaoxugang Friday, August 23, 2013 8:29 AM
    Friday, August 23, 2013 8:17 AM
  • Hi xiaoxugang,

    With the link, I download it.

    I did some changes with it to make it fix my PC environment.

    I run the program about 30 minutes. I checked the authority of the mdb file and found it only have read&execute, but it run ok.

    Below is the code that I changed:

    Imports System.Data.OleDb
    Imports System.Data.Common
    Imports System.Data
    Imports System.IO
    Imports System.Security.AccessControl
    Public Class Form1
        Public WithEvents m_Timer As New System.Windows.Forms.Timer
        Public isDoingFlag As Boolean
        Private m_conn As OleDbConnection = Nothing
        Private m_cmd As OleDbCommand = Nothing
        Private filename As String = Application.StartupPath.TrimEnd("\".ToCharArray()) & "\PSD Monitor.mdb"
        Private strTmpFile As String = Application.StartupPath.TrimEnd("\".ToCharArray()) & "\tmpPSD Monitor.mdb"
        Private ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Jet OLEDB:Database Password=mysql" ';OLE DB Services = -4"
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            m_conn = New OleDbConnection(ConnectionString)
            m_cmd = m_conn.CreateCommand()
            m_cmd.CommandType = CommandType.Text
            m_Timer.Interval = 1 * 1000
            m_Timer.Start()
        End Sub
        Private Sub Form1_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
            If m_Timer IsNot Nothing Then
                m_Timer.Stop()
                m_Timer.Dispose()
                m_Timer = Nothing
            End If
        End Sub
        Private Sub m_Timer_Tick(sender As Object, e As System.EventArgs) Handles m_Timer.Tick
            m_Timer.Stop()
            If isDoingFlag Then Exit Sub
            isDoingFlag = True
            Dim InsertCount As Integer = 0
            Try
                Debug.WriteLine("begin to insert 50 times...")
                m_conn.Open()
                Do
                    InsertCount += 1
                    If InsertCount >= 500 Then Exit Do
                    If m_conn IsNot Nothing Then
                        m_cmd.CommandText = "insert into t_StateLog ([DeviceCode],[StateID],[StateData],[TriggerTime]) values ('ST',0,False,'2013-08-17 11:17:23.901')"
                        m_cmd.ExecuteNonQuery()
                        Application.DoEvents()
                    End If
                Loop
                Debug.WriteLine("50 times inserted operation finished ")
                m_conn.Close()
                Compact_DB(filename)
                'add control privileges for every user or group of directory that the mdb file was located in
                'Dim sec As DirectorySecurity = System.IO.Directory.GetAccessControl(Application.StartupPath, AccessControlSections.All)
                ' Each objRule As FileSystemAccessRule In sec.GetAccessRules(True, True, GetType(System.Security.Principal.NTAccount))
                'setDirectorySecurity(Application.StartupPath, objRule.IdentityReference.Value, FileSystemRights.FullControl, AccessControlType.Allow)
                'Next
                Debug.WriteLine("begin to insert 100 times...")
                InsertCount = 0
                'reconstruct m_conn,it's useless!
                'If m_conn IsNot Nothing Then
                '    m_conn.Dispose()
                '    m_conn = Nothing
                '    m_conn = New OleDbConnection(ConnectionString)
                '    m_cmd = m_conn.CreateCommand()
                'End If
                m_conn.Open()   '<----Error raised some times。
                Do
                    InsertCount += 1
                    If InsertCount >= 1000 Then Exit Do
                    If m_conn IsNot Nothing Then
                        m_cmd.CommandText = "insert into t_StateLog ([DeviceCode],[StateID],[StateData],[TriggerTime]) values ('ST',0,False,'2013-08-16 09:50:31.894')"
                        m_cmd.ExecuteNonQuery()
                        Application.DoEvents()
                    End If
                Loop
                Debug.WriteLine("50 times inserted operation finished" & vbCrLf)
            Catch ex As Exception
                Debug.WriteLine(ex.ToString())
            End Try
            If m_conn IsNot Nothing Then m_conn.Close()
            isDoingFlag = False
            If m_Timer IsNot Nothing Then m_Timer.Start()
        End Sub
        Private Function Compact_DB(ByVal filename As String) As Boolean
            Try
                If System.IO.File.Exists(strTmpFile) Then System.IO.File.Delete(strTmpFile)
                Debug.WriteLine("Compacting...")
                Dim Jet_Engine As JRO.JetEngine = New JRO.JetEngine()
                Jet_Engine.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Jet OLEDB:Database Password=mysql", _
                                           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFile + ";Jet OLEDB:Database Password=mysql;Jet OLEDB:Engine Type=5")
                Jet_Engine = Nothing
                System.IO.File.Delete(filename)
                System.IO.File.Move(strTmpFile, filename)
                Debug.WriteLine("Compacted")
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function
        Private Sub setDirectorySecurity(ByVal fileName As String, ByVal account As String, ByVal rights As FileSystemRights, ByVal controlType As AccessControlType)
            Dim dInfo As System.IO.DirectoryInfo = New System.IO.DirectoryInfo(fileName)
            Dim dSecurity As System.Security.AccessControl.DirectorySecurity = dInfo.GetAccessControl()
            Dim iFlags As InheritanceFlags = New InheritanceFlags()
            iFlags = InheritanceFlags.ContainerInherit Or InheritanceFlags.ObjectInherit
            Dim NewAccessRule As FileSystemAccessRule = New FileSystemAccessRule(account, rights, iFlags, PropagationFlags.None, AccessControlType.Allow)
            dSecurity.ModifyAccessRule(AccessControlModification.Add, NewAccessRule, True)
            dInfo.SetAccessControl(dSecurity)
        End Sub
    End Class

    I just removed the code for adding control privileges and use Microsoft.Office.Core and

    Microsoft.Office.Interop.Access based on .NET Framework 4.5.

    My machine<//span> environment:

    1. VS2012 (64-bit)

    2..NET Framework 4.5

    3.Windows 64-bit.

    So you can have a try to test your program in anther PC.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, August 23, 2013 10:03 AM
    Moderator
  • Hi, Fred Bao
    Thank you for your help.

    As your suggests, I run my progrom in another PC, My machine environment:
    1. VS2012 (32-bit)
    2..NET Framework 4.5
    3.Windows 64-bit.

    It is basically same as your environment. but the problem still exists.

    I have recorded full process vedio the error raised and uploaded to internet, the link string is "kuai.xunlei.com/d/0VKZDWgkgI0ZUgQA878", Please copy this link string and paste into URL of internet explorer, and press Enter key to open the download page, you can download it by click "Error video.swf". Please open it with player or IE.

    According to the informations you provide, I think the program itself is no problem, It is possible that the problem related to the running environment.
    Can you show me the difference between my machine environment and yours. for exampl the settings of references or other's.

    a nice weekend.
    • Edited by xiaoxugang Sunday, August 25, 2013 5:30 AM
    Sunday, August 25, 2013 5:20 AM
  • Hi,

    Below is my PC environment:

    1. Windows 64-Bit.

    2. VS2012 64-Bit

    3. Solution Target .Net Framework 4.5

    4. Microsoft.Office.Core:Microsoft Office 15.0 Object Library

    5.Microsoft.Office.Interop.Access: Version 14.0.0.0

    Below is my program detail config:


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, August 26, 2013 6:18 AM
    Moderator
  • Hi,

    I changed my settings which are same with your environment, but have not any effect.

    I rebuild my mdb database, new database run normally almost 1 hours ,then my program break down!

    Cause of the problem has not been found.

    Can anybody help me?






    • Edited by xiaoxugang Wednesday, August 28, 2013 3:01 AM
    Tuesday, August 27, 2013 9:01 AM
  • Oh, I think whoever get the problem like that is so crazy.

    Maybe you can try reinstall your VS or you can use anther database to see if is it that the problem will repeat.

    Friday, August 30, 2013 1:23 AM
  • Thank you for your replay.

    I have run my program in another PC with windows server 2003 since yesterday morning, so far, It is running Fine.

    However, same program alway fails to run in both windows xp and windows 7, I do not know why there is this difference.

    Friday, August 30, 2013 4:18 AM
  • It is that the two OS are different.

    Here is a blog that show details regarding it:

    http://mywindowsblog.com/?p=866

    Hope to be helpful for you.

    Friday, August 30, 2013 6:26 AM
  • I reinstall OS(winXP or win7) and VS on last weekend, but the problem was not addressed still.

    I installed SQL SERVER EXPRESS as database on this monday, my program connected to the database have been testing normally for more than 2 days.

    It looks like it is only one way to solve the problem that i have to give up ACCESS database.

    Wednesday, September 4, 2013 5:21 AM
  • Sorry for you to have give up the Access.

    But Glad for that the problem have a solution at last.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by xiaoxugang Wednesday, September 4, 2013 6:18 AM
    Wednesday, September 4, 2013 5:37 AM
    Moderator
  • Thanks to all who helped me
    Wednesday, September 4, 2013 6:22 AM
  • Hi,

    All I did was modify the web config identity impersonate tag.

     <identity impersonate="false" />
      </system.web>

    I had the same issue, but it seemed to be a simple fix after hours and hours of endless chasing the cryptic error stack. I am running 64 bit, windows 2008, IIS7 and access.mdb and this was the fix that worked for my situation.

    Hope this helps others with the same error.

    Wednesday, April 2, 2014 7:05 PM