How to Export a query to Excel if Access file is read only(located on SharePoint) (1 Viewer)

[kapaw]

Hi,

I have an access program that is stored on a SharePoint site. I want to be able to export report to the computer even if I just open the access program as read only. Here's my code:

Private Sub btnExport_Click()

On Error GoTo btnExport_Click_Err

Dim db As DAO.Database
Dim strSQL As String
Dim qdfTemp As DAO.QueryDef
Dim qryFilter As DAO.QueryDef

Set db = CurrentDb

With db
Set qdfTemp = .CreateQueryDef("", "SELECT QBF2.Vendor, QBF2.MPN, QBF2.[L(uH)], QBF2.[DCR Typical (Ohm)], QBF2.[DCR Max (Ohm)], QBF2.[Inductance Tolerance (%)], QBF2.[Isat Typical (mA)], QBF2.[Isat Max (mA)], QBF2.[Irated Typical (mA)], QBF2.[Irated Max (mA)], QBF2.[Self Resonant Frequency (MHz)], QBF2.X, QBF2.Y, QBF2.[Z(Max)], QBF2.MCN, QBF2.[Inductor Manufacture Technology], QBF2.Comments, QBF2.[ACR Frequency (MHz)], QBF2.[ACR Typical (Ohm)], QBF2.[Core Loss Coefficients], " & _
                    BuildRipple & " FROM QBF2 " & BuildFilter)
DoCmd.OutputTo acOutputQuery, qdfTemp.Name, "*.xlsx", strSQL, True, , , acExportQualityScreen
.Close
End With

Set qdfTemp = Nothing
Set db = Nothing

btnExport_Click_Exit:

    Exit Sub


btnExport_Click_Err:
    If Err.Number = 2501 Then
    Else
       MsgBox "Error " & Err.Number & " " & Err.Description
    End If
    Resume btnExport_Click_Exit
    
End Sub

I get this error:
---------------------------
Microsoft Access
---------------------------
Error 3011 The Microsoft Access database engine could not find the object '#Temporary QueryDef#'. Make sure the object exists and that you spell its name and the path name correctly. If '#Temporary QueryDef#' is not a local object, check your network connection or contact the server administrator.
---------------------------
OK
---------------------------

Is there a different way to code this function?

 

[Mihail]

..... #Temporary QueryDef#

If this is the exact message, then the troubles aren't in this code.
Is QBF2 running OK ?

 

[kapaw]

Yup, QBF2 is working fine. My only issue is exporting report since the access program should be in Read-only mode. I tried the code below(I added a title:Filtered Results) but I get the "Read Only Error".

With db
Set qdfTemp = .CreateQueryDef("Filtered Results", "SELECT QBF2.Vendor, QBF2.MPN, QBF2.[L(uH)], QBF2.[DCR Typical (Ohm)], QBF2.[DCR Max (Ohm)], QBF2.[Inductance Tolerance (%)], QBF2.[Isat Typical (mA)], QBF2.[Isat Max (mA)], QBF2.[Irated Typical (mA)], QBF2.[Irated Max (mA)], QBF2.[Self Resonant Frequency (MHz)], QBF2.X, QBF2.Y, QBF2.[Z(Max)], QBF2.MCN, QBF2.[Inductor Manufacture Technology], QBF2.Comments, QBF2.[ACR Frequency (MHz)], QBF2.[ACR Typical (Ohm)], QBF2.[Core Loss Coefficients], " & _
                    BuildRipple & " FROM QBF2 " & BuildFilter)
DoCmd.OutputTo acOutputQuery, qdfTemp.Name, "*.xlsx", strSQL, True, , , acExportQualityScreen
.Close
End With