Error Closing MDB File When VBA CDO Email Sent

[szucker1]

I have a MS Access Database File that has VBA code to send emails using:

Call SendCDOMail(strEmail, "SCSOA-SD Account Balance Breakdown", strMessageText)

If while using the MS Access Database File the above code is run and an email sent, when I CLOSE the MS Access Database File I get the following Error Message:

'Could not use 'C:\Users\Administrator\Desktop\Test.mdb'; file already in use.'

I then click 'Ok' on the Error Message and MS Access Database closes, but the corresponding LDB file is still there and cannot be deleted; and also there is a Microsoft Access Process running in Windows Task Manager. If I 'End Task' on the Microsoft Access Process I can then delete the corresponding LDB file.

If I use the MS Access Database File but DO NOT send an email none of the above problems occur.



I am on Windows 10 using MS Access 365. Here is the code for the SendCDOMail Function

Function SendCDOMail(sTo As String, sSubject As String, sBody As String, _
                     Optional sBCC As Variant, Optional AttachmentPath As Variant)
    On Error GoTo Error_Handler
    Dim objCDOMsg       As Object
    Dim i               As Long
 
    Set objCDOMsg = CreateObject("CDO.Message")
 
    'CDO Configuration
    With objCDOMsg.Configuration.Fields
        '
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
        'Server port (typically 25, 465, 587) '***The next line is commented out because of the use of SSL encryption***
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'Commonly -> 465 SSL, 587 TLS
        'SMTP server IP or Name
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        'Type of authentication, NONE, Basic (Base64 encoded), NTLM
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
        'SMTP Account User ID
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "scsoasdtreasurer@gmail.com"
        'SMTP Account Password
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "********"
        'Number of seconds to wait for a response from the server before aborting
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        'Use SSL for the connection (False or True) -> If using SSL, do not specify the Port above
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True '!!!!Always use some form of encryption!!!!
        'Use TLS for the connection (False or True)
        .Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True 'Optional, but recommended if available
        .Update
    End With
 
    'CDO Message
    objCDOMsg.Subject = sSubject
    objCDOMsg.From = "scsoasdtreasurer@gmail.com"
    objCDOMsg.To = sTo
    objCDOMsg.TextBody = sBody 'This would be for plain text e-mails
'    objCDOMsg.HTMLBody = sBody 'This would be for HTML formatted e-mails using HTML tags
    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
            For i = LBound(AttachmentPath) To UBound(AttachmentPath)
                If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                    objCDOMsg.AddAttachment AttachmentPath(i)
                End If
            Next i
        Else
            If AttachmentPath <> "" Then
                objCDOMsg.AddAttachment AttachmentPath
            End If
        End If
    End If
    objCDOMsg.Send
 
Error_Handler_Exit:
    On Error Resume Next
    Set objCDOMsg = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SendCDOMail" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

[pbaldy]

Sounds like:

https://www.devhut.net/access-lock-file-issues/

[szucker1]

Thank you for the link. I looked at that page but it seems mostly about problems with sharing database files and multi-user access. My database doesn't have any multi-user access. It just won't close properly if an email is sent.

[pbaldy]

This happened to me on all my apps, but the first one was an automated app that isn't shared, it just does imports on my computer. You're exhibiting the same symptoms, and I'm willing to bet you have the offending version. I had to revert, some people seem to beat it by putting the db in a trusted location.

[szucker1]

Specifying the folder containing the database as a trusted location fixed the problem.

Thank you!

But get this, I then removed the folder from trusted locations and the problem didn't come back. Go figure.

[pbaldy]

Happy to help! That is odd, I have no idea what's going on in the background. My apps were all in trusted locations already so I had to revert. Pain in the you-know-what.