0

I have problem with application Excel via Access. Few years ago I made application where Excel connecting with Access as database. But last time, aftes some windows update I have problem. When I use this string: Public Const strconnectread = "Provider=Microsoft.ACE.OLEDB.16.0; Mode=Read; Data Source= S:\xxxxx\ARR_CUST2.accdb;" I have error 8004005. When I delete command Mode=Read all works good except... .laccdb file which locked access database and operation like insert or delete for other users. In my application everywhere I close connect and recordsets but .laccdb pernament exist, and when I delete manually that file - it showing again and not closed after close of connect. How I can fix it?

Below my code:

Option Explicit
Public Const strconnectread = "Provider=Microsoft.ACE.OLEDB.16.0; Mode=Read; Data Source= \\xxxx\ARR_CUST2.accdb;"

Private Sub UserForm_Initialize()
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqldown As String
Dim y As Integer, i As Integer

Set Conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")
Conn.Open strconnectread

sqldown = "SELECT distinct [REFERENCE_DATE] FROM ARR_RATA"

rs.Open sqldown, Conn

Do While Not rs.EOF
    Me.CB_REFER_DATE.AddItem Format(rs(0), "yyyy-mm-dd")
rs.MoveNext
Loop

rs.Close
Conn.Close

If Not Conn Is Nothing Then
 If Conn.State = adStateOpen Then
 Conn.Close
 Set Conn = Nothing
 End If
End If  

Set rs = Nothing
Set Conn = Nothing

End Sub
DarkousPl
  • 85
  • 1
  • 10
  • Please post code so we can verify *how* you close connections and recordsets. Are you [releasing objects](https://stackoverflow.com/q/517006/1422451)? Running proper [error handling](https://stackoverflow.com/q/1038006/1422451) to always close and release ADO objects? – Parfait Jan 04 '22 at 17:26
  • @Parfait thank you for answer. I added my code which open connect and not close. With ```Mode=Read;``` system show error, without that string - will connect, but always pernament blocking access database. – DarkousPl Jan 05 '22 at 08:52
  • So just running this subroutine alone (nothing else from other users), the Access database maintains `.laccdb` file? Before running this sub, do you see this locked file? As info, this locked file will appear if *anyone* is currently using the database. By default, Access is a multiple user application. This file should not inhibit action queries like `INSERT` and `DELETE` from running. – Parfait Jan 05 '22 at 16:43

0 Answers0