Access 2010  "Database or object is read-only" error

Heartless91
Heartless91 used Ask the Experts™
on
I have cobbled this code from another db. I believe I have everything working except for the output that gives me "Error 3027 - Cannot update. Database or object is read-only."

Option Compare Database
Option Explicit
Private dbs As DAO.Database
Private qdf As DAO.QueryDef
Private rst As DAO.Recordset

'Created by Helen Feddema 10-Jan-2010
'Last modified by Eric Harman 27-Mar-2012

Public Function AngelRosters()

On Error GoTo ErrorHandler

   Dim rstClass As DAO.Recordset
   Dim rpt As Access.Reports
   Dim strQuery As String
   Dim strSQL As String
   Dim strFileName As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strAngelClass As String
   Dim strTestQuery As String
   Dim strTestSQL As String
   Dim strAngel As String
     
   strAngelClass = "qryAngel_StaffClasses"   'Identifies the classes a teacher has scheduled
   strRecordSource = "qryAngel_Classes"      'Identifies the classes a student has scheduled
   strQuery = "qryAngel_SingleClass"         'Identifies the class roster for a specific class
   strAngel = "qryAngel_Roster"              'Identifies the class roster for a specific class
   Set dbs = CurrentDb
   Set rstClass = dbs.OpenRecordset(strAngelClass, dbOpenDynaset, dbSeeChanges)

   
'   Create report of rostered students per class
    With rstClass
        Do While Not .EOF
            strAngelClass = ![Class_DBID]
            strFileName = ![RptName]
            strFileName = Replace(strFileName, "/", "_")    'some of the data can contain a slash
'            strCurrentPath = Application.CurrentProject.Path & "\" & ![Quarter] & "\"
            strCurrentPath = Application.CurrentProject.Path & "\"
            strFileNameAndPath = strCurrentPath & strFileName
         
'        Create Filtered Query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[Class_DBID] = " & strAngelClass & ";"
            strTestQuery = strQuery
            strTestSQL = strSQL

   'Delete old query
    Set dbs = CurrentDb
    dbs.QueryDefs.Delete strTestQuery

   'Create new query
    Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)

    Set rst = dbs.OpenRecordset(strTestQuery, dbOpenDynaset, dbSeeChanges)

Debug.Print strQuery
Debug.Print strFileNameAndPath

'   Ouptut file to Angel Rosters as a text file

DoCmd.TransferText acExportDelim, , strAngel, strFileNameAndPath, False

NextIntTchr:
    .MoveNext
    Loop
    End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in Angel Rosters  procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
You don't really need to delete and recreate the Query. Just modify the SQL:

'Create new query
Set qdf = dbs.QueryDefs("YourQueryName")

qdf.SQL = strTestQuery
qdf.Close
Set qdf = Nothing

'/ Not sure why you're opening the recordset - all it does is SELECT records, so unless you
'/ have some reason to do this, don't include the OpenRecordset line

'   Ouptut file to Angel Rosters as a text file

DoCmd.TransferText acExportDelim, , strAngel, strFileNameAndPath, False

Also, perhaps I'm just thick, but you are exporting from a query named "qryAngel_Roster", but you don't seem to be modifying that query. It might be modified/derived through other queries, of course, but that's not evident from your question.

Author

Commented:
Thanks. I can't get to the db until tomorrow. I'll let you know.

Author

Commented:
...You don't really need to delete and recreate the Query. Just modify the SQL:

'Create new query
Set qdf = dbs.QueryDefs(strQuery)

qdf.SQL = strTestQuery                          ' Error 3129 here
qdf.Close
Set qdf = Nothing

I get an error message for invalid SQL, expected 'DELETE', 'INSERT'.... I've noted above where the error occurs
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Should be:

qdf.SQL = strSQL

Author

Commented:
Made the change. Still get the same error. In researching EE, I found others running into a 64 character limit for path and filename. I thought Win7 and Access2010 had bypassed that limitation.

Given the nature of the db, you can tell I'm in education. This week is "scramble mode" for me as I am being asked to assist with other duties not normally mine. As such, I'll get to EE and the db as often as I can.

Thanks
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I found others running into a 64 character limit for path and filename
There is no such limitation, unless I misunderstand you.

You could have issues with (a) a corrupt database, (b) permissions issues on the folder hosting the database or (c) a hanging lockfile.

If it's corrupt, then build a new, blank db and import everything into that new database. This will typically take care of corruption.

If it's permissions, you'll have to review the permissions for the folder hosting your database. You must have Full permissions on that folder in order to save the changes to that database.

If it's a hanging lock file, then close the database down completely and check the folder for a file with the same name as the db, but ending in "ldb". Delete that file and restart the db. If you can't delete, then you may have to restart the machine.

Note too that you cannot make design changes while others are in the database. You must have exclusive access to the file in order to do this. Access will allow you to attempt those changes, but it will not save them for you.

Author

Commented:
I recreated the db to resolve possible corruption. I created the folder (on the network) so I know I have permissions - as does all the staff for whom I'm creating this db. There was no locked file.

Additional info:
The data is through an ODBC link to a SQL server.
My autoexec pulls the data and creates local tables for the purpose of speed (the external tables are huge). My queries to generate the output are pointed to the local tables.

Does this shed any light?
Thanks
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Okay ... the next step would be to pinpoint the ine where this is failing. To do that, place a Breakpoint on the first exectutable line in your code (the "strAngelClass = "qryAngel_StaffClasses" line), and then run the code. Access will throw you into the VBA Editor at the line above, and you can then use the Debug menu to "step through" the code. Indicate which ine fails.

Note: To set a Breakpoint, place your cursor in that line and press the F9 button. The line should be highlighted (normally in Red), indicating that you've successfully set the breakpoint.

Author

Commented:
Everything seems to be working until:

DoCmd.TransferText acExportDelim, , strAngel, strFileNameAndPath, False

Just checking. This will create a comma delimited text file - that includes headings?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Post the current code you're using. We need to see what changes you've made to this point.

Author

Commented:
It's on my work computer. I'll post it on Monday morning. Thanks.

Author

Commented:
Below is the current code:

Option Compare Database
Option Explicit
Private dbs As DAO.Database
Private qdf As DAO.QueryDef
Private rst As DAO.Recordset

'Created by Helen Feddema 10-Jan-2010
'Last modified by Eric Harman 27-Mar-2012

Public Function AngelRosters()

On Error GoTo ErrorHandler

   Dim rstClass As DAO.Recordset
   Dim rpt As Access.Reports
   Dim strQuery As String
   Dim strSQL As String
   Dim strFileName As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strAngelClass As String
   Dim strTestQuery As String
   Dim strTestSQL As String
   Dim strAngel As String
     
   strAngelClass = "qryAngel_StaffClasses"   'Identifies the classes a teacher has scheduled
   strRecordSource = "qryAngel_Classes"      'Identifies the classes a student has scheduled
   strQuery = "qryAngel_SingleClass"         'Identifies the class roster for a specific class
   strAngel = "qryAngel_Roster"              'Identifies the class roster for a specific class
   Set dbs = CurrentDb
   Set rstClass = dbs.OpenRecordset(strAngelClass, dbOpenDynaset, dbSeeChanges)

   
'   Create report of rostered students per class
    With rstClass
        Do While Not .EOF
            strAngelClass = ![Class_DBID]
            strFileName = ![RptName]
            strFileName = Replace(strFileName, "/", "_")    'some of the data can contain a slash
            strCurrentPath = Application.CurrentProject.Path & "\" & ![Quarter] & "\"
            strFileNameAndPath = strCurrentPath & strFileName


'        Create Filtered Query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[Class_DBID] = " & strAngelClass & ";"
            strTestQuery = strQuery
            strTestSQL = strSQL

   'Delete old query
    'Set dbs = CurrentDb
    'dbs.QueryDefs.Delete strTestQuery

   'Create new query
    'Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
    Set qdf = dbs.QueryDefs(strQuery)

qdf.SQL = strSQL
qdf.Close

Set qdf = Nothing

'    Set rst = dbs.OpenRecordset(strTestQuery, dbOpenDynaset, dbSeeChanges)

Debug.Print strQuery
Debug.Print strFileNameAndPath

'   Ouptut file to Angel Rosters as a text file

DoCmd.TransferText acExportDelim, , strQuery, strFileNameAndPath, False

NextIntTchr:
    .MoveNext
    Loop
    End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in Angel Rosters  procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
What happens at that line? Does it error out, or does it run, but produces nothing, or produces inaccurate data, etc etc ... we can't see your machine, so we don't really know what "doesn't work" means.

Author

Commented:
Everything works until it tries to output.

DoCmd.TransferText acExportDelim, , strQuery, strFileNameAndPath, False

The queries show the correct students. Below is the strFileNameAndPath.

I:\!High School\Angel Rosters\Quarter 4\Q4 HEDGES  Pd 1   CONSTRUCTION CORE RELATED

Author

Commented:
I'm reading other forums. I am wanting to CREATE these files. Some of the comments deal with having problems until the file exists. Does this help?
I believe I've solved the problem. As is usual, a dumb oversight where I left off the ".txt" extension in naming the file.

Now, I need help distributing points to those who have helped.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Looks like you resolved it yourself, with no real help from our end. In cases like that, you should accept your own comment (the last one), since that was what seemed to be the solution.

Author

Commented:
I found the error myself. I followed the suggestion of the one expert who agreed that I had found the solution.

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