none
Refresh cache using Ado.net and jet RRS feed

  • Question

  • I'm using Ado.net to communicate with a Microsoft Access Database (using the odbc driver).

    Because of the asynchronous write/read behaviour of Jet, sometimes, if you use different connections, (for instance one for writing and another for reading), a small delay between writes on one connection being read on a second connection could happen.

    As explained here => https://support.microsoft.com/en-us/kb/200300

    Unfortunately, the solution proposed is only valid for Ado (not Ado.net).

    Using transactions seems to reduce the issue but not to solve it.

    Any ideas to solve this problem in Ado.net?
    • Moved by Chenchen Li Monday, October 31, 2016 2:47 AM ADO.NET
    Friday, October 28, 2016 9:19 AM

All replies

  • Hi,

    Since your issue is more related to ADO.NET, I would move this thread into the following forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=adodotnetdataproviders

    Thanks for your understanding,

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 31, 2016 2:46 AM
  • Perhaps you could post the code where you are experiencing this issue, since you are using ADO.NET and not ADO. It's difficult to resolve such issues without proper context.

    You may also want to try using ACE OLEDB instead of Jet to see if you encounter the same issue.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, October 31, 2016 12:56 PM
  • Hi xerox7,

    Based on your description, I create a demo by using c# and reproduce your issue on side. 

    Then I try to use transaction, it works well and the following code snippet for your reference.

     const string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\v-wedi\Desktop\db1.mdb";
    
            private void button1_Click(object sender, EventArgs e)
    
            {
    
                OleDbConnection oleCon1 = new OleDbConnection(strConn);    
    
                OleDbConnection oleCon2 = new OleDbConnection(strConn);
    
                oleCon1.Open();
    
                oleCon2.Open();
    
                for (int i = 0; i < 5; i++)
    
                {
    
                    string sql1 = String.Format("insert into test(id) values({0})", i);
    
                    OleDbCommand oleCmd1 = new OleDbCommand(sql1, oleCon1);
    
                    oleCmd1.ExecuteNonQuery();
    
                }
    
                string sql2 = "select * from test";
    
                OleDbCommand oleCmd2 = new OleDbCommand(sql2, oleCon2);
    
                OleDbDataReader dr = oleCmd2.ExecuteReader();
    
                while (dr.Read())
    
                {
    
                    for (int i = 0; i < dr.FieldCount; i++)
    
                        Console.Write(dr.GetValue(i) + "  ");                
    
                }
    
                Console.Write("\n");
    
                dr.Close();
    
                oleCon1.Close();
    
                oleCon2.Close();
    
            }
    
            private void button2_Click(object sender, EventArgs e)
    
            {
    
                OleDbConnection oleCon1 = new OleDbConnection(strConn);
    
                OleDbConnection oleCon2 = new OleDbConnection(strConn);
    
                oleCon1.Open();
    
                oleCon2.Open();
    
                OleDbTransaction myTrans = null;
    
                try
    
                {
    
                    myTrans = oleCon1.BeginTransaction(IsolationLevel.ReadCommitted);
    
                  
    
                    for (int i = 0; i < 5; i++)
    
                    {
    
                        string sql1 = String.Format("insert into test(id) values({0})", i);
    
                        OleDbCommand oleCmd1 = new OleDbCommand(sql1, oleCon1);
    
                        oleCmd1.Transaction = myTrans;
    
                        oleCmd1.ExecuteNonQuery();
    
                    }
    
                    myTrans.Commit();
    
                }
    
                catch(Exception ex)
    
                {
    
                    Console.WriteLine(ex.Message);
    
                    try
    
                    {
    
                        myTrans.Rollback();
    
                    }
    
                    catch
    
                    {
    
                    }  
    
                }
    
                string sql2 = "select * from test";
    
                OleDbCommand oleCmd2 = new OleDbCommand(sql2, oleCon2);
    
                OleDbDataReader dr = oleCmd2.ExecuteReader();
    
                while (dr.Read())
    
                {
    
                    for (int i = 0; i < dr.FieldCount; i++)
    
                        Console.Write(dr.GetValue(i) + "  ");
    
                }
    
                Console.Write("\n");
    
                dr.Close();
    
                oleCon1.Close();
    
                oleCon2.Close();
    
            }
    

    >>Using transactions seems to reduce the issue but not to solve it.

    Could you please provide a simple demo via OneDrive, we'll reproduce your issue on our side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 1, 2016 1:11 AM
    Moderator
  • Hi, here you have an example. Your database should have a table called 'test' with a field of type short text named 'test_guid'.

         class Program
        {
            static string oledbConn = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB Services=-1;Data Source =DATABASE_PATH.mdb;User Id=admin;Password =";

            static void Main(string[] args)
            {
                while (true)
                {
                    string guid = Guid.NewGuid().ToString();
                    insertValue(oledbConn, guid);
                }
            }

            private static void insertValue(string oledbConn, string guid)
            {
                using (OleDbConnection conn = new OleDbConnection(oledbConn))
                {
                    conn.Open();
                    OleDbTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

                    string query = string.Format(@"insert into test (test_guid) values ('{0}')", guid);

                    using (OleDbCommand command = new OleDbCommand(query, conn, tran))
                    {
                        try
                        {
                            if (command.ExecuteNonQuery() == 0)
                                throw new Exception("Error.");

                            tran.Commit();

                            while (!ValueExists(oledbConn, guid))
                            {
                                Console.WriteLine("Guid: {0} not found!!.", guid);
                                Thread.Sleep(1000);
                            }

                            Console.WriteLine("Guid: {0} found.", guid);
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e);
                            tran.Rollback();
                        }
                    }
                }
            }

            private static bool ValueExists(string oledbConn, string guid)
            {
                using (OleDbConnection conn = new OleDbConnection(oledbConn))
                {
                    conn.Open();

                    string query = string.Format(@"select * from test where test_guid='{0}'", guid);

                    using (OleDbCommand command = new OleDbCommand(query, conn))
                    {
                        return (command.ExecuteReader().Read());
                    }
                }
            }
        }
    • Edited by xerox7 Wednesday, November 2, 2016 9:07 AM
    Wednesday, November 2, 2016 9:03 AM
  • Try closing your connection after calling ExecuteNonQuery (e.g. conn.Close()). There is no reason to maintain a persistent connection, especially with an Access database.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 2, 2016 12:27 PM
  • Closing the connection reduce the issue (like using transactions) but still happening.
    Wednesday, November 2, 2016 12:46 PM
  • Closing the connection reduce the issue (like using transactions) but still happening.

    The below link discusses a configuration alternative:

    http://vbcity.com/forums/t/93357.aspx?PageIndex=2

    If you are running 64-bit Windows you will want:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node

    Unfortunately there is no way to set these OLEDB Provider properties in ADO.NET.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 2, 2016 2:42 PM