Access connection is failing because of the file .ldb

[ngarciag]

Hello, I'm having a problem when I'm updating information in an Access file. I have an MS Excel with macros that open many other excels to obtain information to construct a sql statement. I have to insert that information on a table in access. It worked really well at the beginning. Nevertheless, when I want to use it several times, to test the macros, I got errors.

This is my sub:

Sub writeData(reportYear As Integer, state As String, school As String, campus As String, dataLabel As String, dataValue As Long)

Dim cn As New ADODB.Connection
Dim sql As String

' open a connection to the university crime database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.path & "\universityCrime.mdb" & ";Mode=Share Deny None;Persist Security Info=False;"

sql = "insert into crimedata(reportYear, state, school, campus, dataLabel, dataValue)" & _
"values(" & reportYear & ",'" & state & "','<~UNIV~>',NULL,'" & dataLabel & "'," & dataValue & ")"
sql = Replace(sql, "<~UNIV~>", Replace(school, "'", "''"))
If campus <> "NULL" Then
sql = Replace(sql, "NULL", "'" & Replace(campus, "'", "''") & "'")
End If

cn.Execute sql

cn.Close

End Sub

At the beginning I was using the ADODB.Connection:

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.path & "\universityCrime.mdb" & ";"

It worked well. However, after some updates, I got the error:
Run-time error '-2147467259 (80004005)': Operation must use an updateable query

Reading on forums, I read that I need to force to avoid the read, so I changed the ADODB.Connection. Thus, my ADODB.Connection is as shown before.

Now, after many updates, I got the error:
Run-time error '-2147467259 (80004005)': The Microsoft Access database engine cannot open or write to the file 'C:\...\universityCrime.ldb'. It is already opened exclusively by another user, or ou need permission to view and write its data.

Thus, for my understanding, the code is correct. Nevertheless, the access connection stays open at some point, so it generates errors the next time that it is open. How can I solve this prolem? Should I force it to close? How can I do that?

Thanks in advance!

[dilettante]

Have you created this database within some protected filesystem location like Program Files?

[ngarciag]

The database is in a Dropbox file. All the file are in the same file system. So it is not protected. As I mentioned before, it worked many times, but then I got that error.

I just realized that I can put debug and then F5, and it will work without problems. I think that at some point my access connection doesn’t close as fast as the macro reopen again.
But I don’t want to do that, because the users won’t know how to do it or they can change the macro code.

[westconn1]

you could test with a sleep(), see how longer delay may be required, which may vary from computer to computer, or try looping while there is an error

[dilettante]

Read the old article: How to keep a Jet 4.0 database in top working condition.

Pay close attention to the section "Use a robust file server."

DropBox is not a robust file server. It is not safe to use it to host a Jet MDB unless it is carefully opened read-only. It is never really safe to update over a WAN. Even a wireless LAN can be a dicey proposition.

You need to move to another remote server where you can run a middle-tier, or else move to a client-server DBMS but even then you really don't want to expose it to direct connections from the Internet.

[gibra]

The database is in a Dropbox file.

Then it is absolutely wrong to open it directly into the Dropbox folder.

You should:
1. when application START,
- copy the database from C:\users\YOURUSER\Dropbox\universityCrime.mdb to a different folder (i.e. C:\MyDatabase\universityCrime.mdb)

2. when application END,
- compact database, each time (!)
- re-copy database to Dropbox folder

I suggest you to zip/unzip the database in Dropbox folder, because if the database becomes large it can be a problem transfer it to Dropbox cloud if the connection is slow.