Link to home
Start Free TrialLog in
Avatar of Heartless91
Heartless91Flag for United States of America

asked on

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

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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
Avatar of Heartless91

ASKER

Thanks. I can't get to the db until tomorrow. I'll let you know.
...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
Should be:

qdf.SQL = strSQL
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
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.
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
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.
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?
Post the current code you're using. We need to see what changes you've made to this point.
It's on my work computer. I'll post it on Monday morning. Thanks.
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
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.
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
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?
ASKER CERTIFIED SOLUTION
Avatar of Heartless91
Heartless91
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I found the error myself. I followed the suggestion of the one expert who agreed that I had found the solution.