Ace oledb connection close file still in use error RRS feed

  • Question

  • Hi

    Recently I face an issue on Excel 2016. After i close the connection string on excel 2016 environment the system still lock the excel from editing (say that file in use). but the code is working perfectly on excel 2007 and 2010 environment (after connection close, the file is ready for my next function to open and edit).

    on excel 2016 environment  after system use ace oledb connection to read the excel file to dataset or datatable, after the connection close it seem like excel file is to locked for 75 second after that it automatically unlocked, but this won't happen on excel 2007 and 2010 environment

    After i uninstall and reinstall the AccessDatabaseEngine (Microsoft Access Database Engine 2016 Redistributable), the error didn't appear. 

    This error only appear after Microsoft recent update on Microsoft Office 2016. Every time got live update on Microsoft Office I need to reinstall the Microsoft access database engine 2016

    Did anyone face the same issue as I face 

    What your play around

    Append below with my code

    Thank you

    Public Function gf_ExcelConnection(ByRef strLocation As String, ByRef sWorksheetName As String, _ ByRef lExcelDataset As DataSet, ByRef iLastRow As Integer, _ ByRef sColumn As String) As Boolean Dim strExcelConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strLocation & _ "; Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""" Dim ExcelConn As New System.Data.OleDb.OleDbConnection(strExcelConn) Dim ExcelCommand As OleDbCommand = New OleDbCommand Dim ExcelDataAdapter As New OleDbDataAdapter Try gf_ExcelConnection = True ExcelCommand.CommandText = "SELECT * FROM [" + sWorksheetName + "A1:" & sColumn & iLastRow & "] " ExcelDataAdapter.SelectCommand = ExcelCommand ExcelDataAdapter.SelectCommand.Connection = ExcelConn MsgBox(103) ExcelConn.Open() ExcelDataAdapter.Fill(lExcelDataset, "ImportTable") ExcelConn.Close() ExcelConn.Dispose() MsgBox(104) Catch ex As Exception gf_ExcelConnection = False gclsError.gf_ErrorHandler("InvalidIncorrectFile", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error, False) ExcelConn.Close() Finally ExcelConn.Close() ExcelConn.Dispose() ExcelConn = Nothing MsgBox(105) End Try End Function

    Friday, February 21, 2020 5:53 AM