Error 3017 Exporting to Excel

Jim Kranz
Jim Kranz used Ask the Experts™
on
I am getting "Error 3017 Cannot update. Database or object is read only" when trying to export a table to Excel. I use the same method that I use in other places which always works as expected.
DoCmd.TransferSpreadsheet _
            TransferType:=acExport, _
            tableName:="table1", _
            FileName:="T:\MyFolder\Filename"
            HasFieldNames:=True

The only difference in my current Sub is that I am opening a record set to get a name for the file. This routine is in a loop because I will be creating multiple Excel files, each with a different name. This is what that looks like:

Dim sCompany As String
Dim sMyPath As String
Dim sReportName As String
       
sCompany = rsCompany(0)
sMyPath = "T:\MyFolder\"
sFileName = sCompany & "Filename"

DoCmd.TransferSpreadsheet _
            TransferType:=acExport, _
            tableName:="Table1", _
            FileName:= sMyPath & sFileName, _
            HasFieldNames:=True

If I hard code FileName:= then it works without getting Error 3027. but written as above (sMyPath & sFileName) or any variation of that and it fails.

What am I doing wrong?
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Use Debug>print to print out the values of your variable to see exactly what is happening

Dim sCompany              As String
    Dim sMyPath               As String
    Dim sReportName           As String

    sCompany = rsCompany(0)
    sMyPath = "T:\MyFolder\"
    sFileName = sCompany & "Filename"

    Debug.Print sCompany
    Debug.Print sMyPath
    Debug.Print sFileName

Open in new window


I also suspect sFileName = sCompany & "Filename" is incorrect and should be
sFileName = sCompany & sCompany

Open in new window

Author

Commented:
D Pineault, thanks for the quick response.

sFileName = sCompany & sCompany would result in the file name being (as example) ABCCorpABCCorp
It is supposed to be ABCCorpFilename (Insert and file name you want, that was for example purposes)

Even if I change it to sFileName = sCompany & sCompany, I still get the same error message.
NorieAnalyst Assistant

Commented:
Where's the code that's opening the recordset for the filename?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Sorry my bad.  I meant:

sFileName = sMyPath & sCompany

Open in new window


Regardless, you don't seem to be including a file extension anywhere.
I'm trying to understand what the point is of sFileName = sCompany & "Filename" exactly.  Could you explain?  What is the purpose of sCompany (is that the filename)?  If not, where is the filename supposed to be coming from exactly?

Please tell us what the variable return and what your desired path/filename should be and we'll help you make that.

Author

Commented:
I was trying not to bother you all with the whole routine but maybe that is best. These are my actual table and field names. I'm not going to sterilize it with table1, table2, field1, etc.
The reason for concatenating the file name is that each month there are 40 of these files produced. Each one goes to a separate hospital and each is unique to them. When this loop is done all of the files are in one folder. So the file names look like this:
General_DOT_Aug2018
Memorial_DOT_Aug2018
SaintMary_DOT_Aug2018

That is the reason for the string  sFileName.
Also, please keep in mind I really do this naming of files in other reports that get run at the same time. But all the others are Access Reports being printed as PDF files.  That looks like this:

 DoCmd.OpenReport "Practitioner_DoT_by_Practitioner", acViewPreview, , "facility_id = " & intHospital
 DoCmd.OutputTo acOutputReport, "Practitioner_DoT_by_Practitioner", acFormatPDF, strmyPath & strReportName, False
 DoCmd.Close acReport, "Practitioner_DoT_by_Practitioner"

I don't know if this is the right or best way to do it, but it works great.
It's only when I changed the output from PDF to Excel that I get the error message. And even then, I don't get it if I type the path and filename in quotes.


----------
Public Sub Export_Practitioner_DoT_Excel()

'Create an Excel file of practitioner DOT running 18 months (for only those that have asked)
    Dim dYM1 As Date 'current month of report
    Dim dYM18 As Date 'oldest month for report
    Dim sYM1 As String
    Dim sYM18 As String
    Dim sReportDate As String

    'Get all required dates
    dYM1 = Forms![Main].txtStartDate
    dYM18 = DateAdd("m", -17, dYM1)
    sYM1 = "#" & dYM1 & "#"
    sYM18 = "#" & dYM18 & "#"
    sFileDate = Format(dYM1, "mmmyyyy")
       
    Dim rsHospital As DAO.Recordset
    Set rsHospital = CurrentDb.OpenRecordset("SELECT hospital.facility_id, hospital.file_name, x_setup.imported " & _
                                            "FROM hospital INNER JOIN x_setup ON hospital.facility_id = x_setup.facility_id " & _
                                            "WHERE x_setup.imported=Yes AND x_setup.excel_export=Yes")
   
    If Not (rsHospital.EOF And rsHospital.BOF) Then
    rsHospital.MoveFirst
    Do Until rsHospital.EOF = True
   
        Dim iHospital As Long
        Dim sHospital As String
        Dim sMyPath As String
        Dim sReportName As String
       
        iHospital = rsHospital(0)
        sHospital = rsHospital(1)
        sReportName = sHospital & "_DOT_" & sFileDate & ".pdf"
        sMyPath = "T:\WVHA Data Services\Staging\Antibiotic\"
       
        'Get the practitioners for this hospital that have data in the reporting month
        CurrentDb.Execute "INSERT INTO practitioner_temp ( practitioner_id ) " & _
                            "SELECT practitioner_data.practitioner_id " & _
                            "FROM practitioner INNER JOIN practitioner_data ON practitioner.practitioner_id = practitioner_data.practitioner_id " & _
                            "WHERE practitioner_data.Period = " & sYM1 & " And practitioner.facility_id = " & iHospital & " " & _
                            "GROUP BY practitioner_data.practitioner_id"
                           
        CurrentDb.Execute "INSERT INTO practitioner_export ( practitioner, period, DOT, Patient_Count ) " & _
                            "SELECT practitioner.practitioner, practitioner_data.period, Sum(practitioner_data.DoT) AS SumOfDoT, Sum(practitioner_data.pt_count) AS SumOfpt_count " & _
                            "FROM practitioner_temp INNER JOIN ((practitioner INNER JOIN practitioner_data ON practitioner.practitioner_id = practitioner_data.practitioner_id) INNER JOIN antibiotics ON practitioner_data.antibiotic_id = antibiotics.antibiotic_id) ON practitioner_temp.practitioner_id = practitioner.practitioner_id " & _
                            "GROUP BY practitioner.practitioner, practitioner_data.period, practitioner.facility_id " & _
                            "HAVING practitioner_data.period Between " & sYM18 & " And " & sYM1 & " AND practitioner.facility_id= " & iHospital & " " & _
                            "ORDER BY practitioner.practitioner, practitioner_data.period"
       
        CurrentDb.Execute "UPDATE practitioner_export SET practitioner_export.Avg_DOT = [DOT]/[Patient_Count]"
       
        DoCmd.TransferSpreadsheet _
            TransferType:=acExport, _
            tableName:="practitioner_export", _
            FileName:="T:\WVHA Data Services\Staging\Antibiotic\" & sReportName, _
            HasFieldNames:=True
                     
        'Clear practitioner_temp
        CurrentDb.Execute "DELETE practitioner_temp.* FROM practitioner_temp"
       
        'Clear practitioner_export
        CurrentDb.Execute "DELETE practitioner_export.* FROM practitioner_export"
             
        rsHospital.MoveNext
   
    Loop

    End If

    rsHospital.Close
    Set rsHospital = Nothing

End Sub

----------------
Thanks for taking a look.
Jim
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
TransferSpreadsheet  cannot work with PDF files.
TransferSpreadsheet is used to create a new Excel workbook/worksheet with data from your database.

What exactly is your ultimate goal here? Excel or PDF?  If it is Excel then change the extension and we can work out anything else that might be wrong.  If it is PDF then create a proper Access report and then output it as a PDF.

Author

Commented:
D Pineault, I really sorry, you have missed my issue and I clearly have not done a good job explaining it. The only reason I mention PDF is to let you know in other routines I used this naming convention works fine. The routine I just posted has NOTHING to do with PDF files.

All I want is the run that loop (The loop works fine, it's not broke) and each loop to product an Excel file. That's it. My point was, that this WORKS:
DoCmd.TransferSpreadsheet _
           FileName:="T:\Staging\Antibiotic\Memorial_DOT_Aug2018" _

And this DOES NOT work:
FileName:=sMyPath & sReportName, _
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
So what does sMyPath  & sReportName if you debug.print them or add a BreakPoint and check when you run the code?
Could you be missing a \ ?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
As Daniel wrote, you have specified a PDF extension:

sReportName = sHospital & "_DOT_" & sFileDate & ".pdf"

Open in new window

This will run with TransferSpreadsheet:

sReportName = sHospital & "_DOT_" & sFileDate & ""

Open in new window

The extension will be applied automatically: .XLSX

Author

Commented:
Gustav Brock sorry about the .pdf in the code sample, That was not really present.

D Pineault
Debug.Print sMyPath & sReportName
T:\Staging\Antibiotic\Raleigh_DOT_Aug2018

Tested the output and it is as expected. That is the correct path and file name. Hard coded it is DoCmd.TransferSpreadsheet as
Filename:="T:\Staging\Antibiotic\Raleigh_DOT_Aug2018" and that works as well.

Does getting the Error3017 have something to do with the recordset being open?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Any chance you could post a sample of the db (removing any truly sensitive information) so we can take a closer look at what is going?  We don't need the entire db, just the objects relative to this issue.

Author

Commented:
No sorry. This db is full of sensitive PHI and I'd not be able to clean it enough or I would.
Since I'm convinced the error message has something to do with the recordset I open to get the hospitals name (sHospital = rsHospital(1) that is causing the issue, I just thought someone knew the quick answer.
I appreciate you offering to help.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Does getting the Error3017 have something to do with the recordset being open?

No. What is the error - in text? Wrong table name or filename will raise other errors.
However, if the file already exists and is write protected, or you don't have write access to the folder, you will see error 3027.

Your exact code runs fine here (as noted above):

DoCmd.TransferSpreadsheet acExport, , "TableName", "d:\path\filename"

Open in new window

Author

Commented:
"Error 3017 Cannot update. Database or object is read only" is how it reads.
Table name, file name, folder access all are clear.
As you saw above I took the debug.print results and pasted them in and it works.

Debug.Print sMyPath & sReportName
T:\Staging\Antibiotic\Raleigh_DOT_Aug2018

The file does not exist, so in my case it is never being overwritten. However, I tested an overwrite and it works then done as

DoCmd.TransferSpreadsheet acExport, , "TableName", "d:\path\filename"
doesn't work write as:
DoCmd.TransferSpreadsheet acExport, , "TableName", sMyPath & sFileName
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It doesn't make sense. Changing the path written to cannot make the database or table read-only. Try:

So something else is going on.

Author

Commented:
I with ya Gustav, but I can figure out what that is.

I decided to import the entire db into a new blank db. It works!
I have no idea what was happening, but once someone said that it should work just as I had it written (I always blame myself first) then I knew this was one of those MS Access things.

I appreciate all the help and would also appreciate the proper way to close this question.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Do you compact your db from time to time?
Do you compile your code regularly?
Do you decompile your db every once and a while?

Author

Commented:
Do you compact your db from time to time? Yes, fairly frequent
Do you compile your code regularly? Yes, not as frequent but yes
Do you decompile your db every once and a while? No, I do not do this. I have in the past with another db but not this one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial