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).

[June7]

Did you confirm that the query is actually changed and has the data? Not sure why you have to rebuild the query.

Might be easier to populate a temp table in the frontend and use that table as source for the export.

[riaarora]

Thanks

I have managed to solve the issue using below code

.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, sWorkbookPath, True, sWorksheetName