Heartless91
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(strAngel Class, 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(strTest Query, strTestSQL)
Set rst = dbs.OpenRecordset(strTestQ uery, 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
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(strAngel
' 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
strCurrentPath = Application.CurrentProject
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(strTest
Set rst = dbs.OpenRecordset(strTestQ
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
ASKER
Thanks. I can't get to the db until tomorrow. I'll let you know.
ASKER
...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
'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
qdf.SQL = strSQL
ASKER
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
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 filenameThere 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.
ASKER
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
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.
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.
ASKER
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?
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.
ASKER
It's on my work computer. I'll post it on Monday morning. Thanks.
ASKER
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(strAngel Class, 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(strTest Query, strTestSQL)
Set qdf = dbs.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Set rst = dbs.OpenRecordset(strTestQ uery, 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
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(strAngel
' 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
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(strTest
Set qdf = dbs.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Set rst = dbs.OpenRecordset(strTestQ
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.
ASKER
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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
I found the error myself. I followed the suggestion of the one expert who agreed that I had found the solution.
'Create new query
Set qdf = dbs.QueryDefs("YourQueryNa
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.