CODEWITHSUNDEEP
vbams-accesssharepoint
tsdn
tsdn

Reputation: 427

Querying Access database in SharePoint folder, creates a ".laccdb" file in Recycle bin

As in the subject line, running a SELECT statement on this database from an Excel macro puts a ".laccdb" file in the recycle bin - is there any way to avoid this?

VBA:

Sub QueryDbOnSP()
    Dim cn As Object
    Dim rs As Object
    Dim cnStr As String
    Dim qryStr As String
    Dim WebDavStr As String
    
    WebDavStr = "\\....\myDbName.accdb"
    Set cn = CreateObject("ADODB.Connection")
    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WebDavStr
    qryStr = "SELECT * FROM [Table1];"
    cn.Open cnStr
    Set rs = cn.Execute(qryStr)
    'rest of code

End Sub

Upvotes: 0

Views: 597

Answers (1)

Erik A
Erik A

Reputation: 32682

Depending on exactly how you're querying Access, you can query in exclusive mode.

Exclusive mode locks the file and thus doesn't create and delete a .laccdb file since the entire file is locked.

For an OLEDB connection, you connect in exclusive mode by adding Mode=Share Exclusive; to the connection string.

However, SharePoint auto-deletes these files anyway (depending on configuration, but usually). It auto-mails you about these files if you delete many, but a simple mail rule blocks these or puts them into a low-priority mailbox.

Upvotes: 1

Related Questions