Error while exporting the data

[riaarora]

Dear All,
I have created Access database as a frontend application and another database as a Backend Database (all the databases are password protected). All tables and queries are stored in Backend Database.
I have created Excel Sheet and writing data in this excel file and performing the formatting. And after formatting the sheet I need to export another query's result in different sheet.
While exporting I'm getting Error "Runtime error 3275" - "Unexpected error from external database driver (1309)"

    Set objXLApp = CreateObject("Excel.Application")
    wb = Me!Output_Dir & "\" & sTeamRegionName & "_" & gsReportingWeekNm & "_" & Format(Now, "YYYYMMDD_HHMM") & ".xlsx"
    Set objXLWorkbook = objXLApp.Workbooks.Add 'Will Create a new workbook
    Set objXLSheet = objXLWorkbook.Worksheets(1) 'Will create a new worksheet
    objXLApp.Visible = True 'Add this line immediate right after creating your Excel object. 
................
................
'Doing fomatting etc.
................
................
    objXLWorkbook.SaveAs fileName:=wb
    strSelectSQL = "SELECT [Staff Number], [GMIS Revenue Producer], [Private Banker], [Team] FROM tbl_Bankers_Mapping WHERE [Exclude] = True " & _
                    "AND " & strWhereCondition & "= '" & sTeamRegionName & "' "
    Call ExportDataToExcel(wb, "ManagementHolding", strSelectSQL)
    Worksheets(sWorksheetName).Activate
    
    objXLApp.Visible = True 'or oExcel.Quit
    objXLWorkbook.Close
    objXLApp.Quit
    
    Set objXLSheet = Nothing
    Set objXLWorkbook = Nothing
    Set objXLApp = Nothing

Private Sub ExportDataToExcel(sWorkbookPath As String, sWorksheetName As String, sSelectSQL 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 = "TempQuery"
    
    DoCmd.SetWarnings False
    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
        
        'Create Query Definition
        .CurrentDb.CreateQueryDef strTempQueryName, sSelectSQL
        .CurrentDb.QueryDefs.Refresh
        .DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, sWorkbookPath, True, sWorksheetName 'ERROR HERE
        .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

I'm getting this error "Runtime error 3275" - "Unexpected error from external database driver (1309)" at below line
.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, sWorkbookPath, True, sWorksheetName 'ERROR HERE
If I export the data after closing the file then there is no issue but if file is not closed then I'm getting error.

[June7]

First time I've seen a split db with queries in the backend. Review http://office.microsoft.com/en-us/ac...010342026.aspx

Is this code in the frontend? If so, how does TransferSpreadsheet code know to find query in the backend?

Export after closing which file is not issue - the Excel workbook?