Error while exporting the data

[riaarora]

Hi All,

I have fronend and backend database. I have all data stored in another database (backend) and that database is password protected. I need to export some selected data into Excel which is created in the same process. Below is the code:

Private Sub ExportLeaversList(strWorkbook As String)
    On Error GoTo ERR_HANDLER
    
    Dim objApp As Object
    Dim strExcelFileName As String
    Dim varStatus As String
    Dim strTempQueryName As String
    Dim strSelectSQL As String

    Dim strPnPDatabaseName As String
    Dim strPnPDatabasePassword As String
    
    strTempQueryName = "BankersLeavers"
    
    DoCmd.SetWarnings False

    strSelectSQL = "SELECT * FROM tbl_Bankers WHERE [Exclude] = True"

    Set objApp = New Access.Application
    strPnPDatabaseName = ExtractDatabaseDetails.GetDatabasePath(gsPnPDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(gsPnPDatabaseID)
    strPnPDatabasePassword = GetDatabasePassword(gsPnPDatabaseID)

    objApp.OpenCurrentDatabase strPnPDatabaseName, , strPnPDatabasePassword

    With objApp
        If .DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = '" & strTempQueryName & "' ") <> 0 Then
            .DoCmd.DeleteObject acQuery, strTempQueryName
            .CurrentDb.QueryDefs.Refresh
        End If
        
        'Extract Secucash Detail Data
        .CurrentDb.CreateQueryDef strTempQueryName, strSelectSQL
        .CurrentDb.QueryDefs.Refresh

        .DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, strWorkbook, True '--> Error
        
        .DoCmd.DeleteObject acQuery, strTempQueryName
        .CloseCurrentDatabase
    End With

    Set objApp = Nothing
    
    DoCmd.SetWarnings True
    
Exit_Err_Handler:
    Exit Sub
ERR_HANDLER:
    MsgBox Err.Description
    DoCmd.Hourglass (False)
    varStatus = SysCmd(acSysCmdClearStatus)
    DoCmd.Hourglass (False)
    Resume Exit_Err_Handler
End Sub

Value of strWorkbook is C:\MyDoc\Taiwan_Week 23_20120809_2356.xlsx

I'm getting error at below line

.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, strWorkbook, True

Run-time error '3275':

Unexpected error from external database driver (1309).

[riaarora]

Thanks. I have managed to resolve the issue