[MS_AccessPros] Runtime Error 3011 "Cannot find the object..."

 

Hello Pros,

I am trying to export a query to a shared network folder. My procedure errors with error 3011 "The Microsoft database engine could not find the object \\RSIPRFP001\Accounting\Accnting\2016 Month End\2016 H August\LTL Rebrand Trailers.xlsx which is the file name I'm trying to save. The procedure errors on the line that says "DoCmd.TransferSpreadsheet.. The file path is correct. Here is the procedure:

Private Sub btnLTLRebrandExport_Click()
'Exports Rebranded trailer info to Excel
   
    Dim Spath As String
    Dim SMonth As String
    Dim StrMonthValue As String
    Dim strFileName As String
    Dim strSQL As String
    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.QueryDefs("qryRebrandExport")
   
    strSQL = "SELECT ReconPRIMARYTABLE.CUSTID, ReconPRIMARYTABLE.UNITID, ReconPRIMARYTABLE.Door, ReconPRIMARYTABLE.CompletionDate, IIf([CUSTID]=""CTS"",334,378) AS Price, IIf([CUSTID]=""CTS"",125,0) AS Delivery, IIf([Door]=True,275,0) AS DoorPrice, [price]+[Delivery]+[DoorPrice] AS TotalPrice, Round([TotalPrice]*0.095,2) AS SalesTax, [TotalPrice]+[SalesTax] AS Total " & vbCrLf & _
             "FROM ReconPRIMARYTABLE " & vbCrLf & _
             "WHERE (((ReconPRIMARYTABLE.CUSTID)=""CTS"") AND ((ReconPRIMARYTABLE.CompletionDate) Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#));"
    qd.SQL = strSQL
   
    ' Creates LTLRebrandSMonth.xlsx in n:\Accnting\month end folder
    'Check for EOM Year folder if not there, create it.
    If Len(Dir("\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End", vbDirectory)) = 0 Then
        'Make directory
        MkDir "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End"

    End If
    'Check for EOM Month folder, if not there, create it.
    'First create SMonth which is the variable name of the monthly folder

   

    'Calculate SMonth
    StrMonthValue = DatePart("m", Me.Start_Date)
    Select Case StrMonthValue

    Case 1
        SMonth = "A January"
    Case 2
        SMonth = "B February"
    Case 3
        SMonth = "C March"
    Case 4
        SMonth = "D April"
    Case 5
        SMonth = "E May"
    Case 6
        SMonth = "F June"
    Case 7
        SMonth = "G July"
    Case 8
        SMonth = "H August"
    Case 9
        SMonth = "I September"
    Case 10
        SMonth = "J October"
    Case 11
        SMonth = "K November"
    Case Else
        SMonth = "L December"

    End Select

    'check for monthly folder, if not there, create it.
    If Len(Dir("\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth, vbDirectory)) = 0 Then
        'Make directory
        MkDir "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth
    End If

    Spath = "\\rsiprfp001\Accounting\ACCNTING\" & DatePart("yyyy", Date) & " Month End\" & DatePart("yyyy", Date) & " " & SMonth & "\"
    'strFileName = Spath & "LTL_Rebrand_" & Mid(SMonth, 3, 3) & DatePart("yyyy", Me.Start_Date) & ".xlsx"
    strFileName = Spath & "LTL Rebrand Trailers.xlsx"
    'Debug.Print strSQL
   
    'DoCmd.TransferSpreadsheet , acExport, 10, strSQL, strFileName
    Debug.Print Spath
    Debug.Print strFileName
    If FileExists(strFileName) = True Then Kill strFileName
    'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", strFileName
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", "\\rsiprfp001\Accounting\ACCNTING\2016 Month End\2016 H August\LTL Rebrand Trailers.xlsx"
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "qryRebrandExport", "C:\databases\LTL Rebrand Trailers.xlsx"
    Set qd = Nothing
    
   

End Sub

The part that checks for the path and creates the folder if needed works fine. I've tested it and used it in other procedures. I just can't get the query to transfer.

Doyce

__._,_.___

---

Posted by: winberry.doyce@con-way.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___