Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
READ ONLY RECORD SETS
I wrote the following code to read an ACCESS database but I keep getting a
READ ONLY ERROR. I am pulling data off of a company dbase and putting it on my smaller DBASE so it will not take so long to run. I have had no problem up until now with .ADDNEW, but it will not allow my to .EDIT Sub write_Actuals() Dim myDBS As Database Dim myTBLE As Recordset, myACTUALS As Recordset, rstDUMMY As Recordset Dim mySQL As String Dim myMOD As Integer Dim mySUB As Integer, myLOT As Integer, myLINE As String, mySKU As String, mySTORE As Integer Dim skuROW As Integer, storeROW As Integer Set myDBS = OpenDatabase("C:\BAT_DCM_SKU_DATA.mdb", , False) rstDUMMY.Close '(from code prior to this point, thought I had to close) Set rstDUMMY = myDBS.OpenRecordset("TEMP_PULL") With rstDUMMY .MoveFirst Do sqlEOH = "SELECT SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "FROM SKU_STORE_DATA " & _ "GROUP BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "HAVING (((SKU_STORE_DATA.mySUB)=" & !SUB & ") AND ((SKU_STORE_DATA.myLOT)=" & !LOT & ") AND ((SKU_STORE_DATA.myLINE)=" & Chr(34) & Format(!Line, "0000") & Chr(34) & ") AND ((SKU_STORE_DATA.FISCAL_WEEK)=" & !FISCAL_YEAR * 100 + !FISCAL_WEEK & ") AND ((SKU_STORE_DATA.mySKU)=" & Chr(34) & Format(!SKU, "00") & Chr(34) & ") AND ((SKU_STORE_DATA.mySTORE)=" & !LOCATION_ID & ")) " & _ "ORDER BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT" 'Debug.Print sqlEOH Set myTBLE = myDBS.OpenRecordset(sqlEOH) Debug.Print sqlEOH While Not myTBLE.EOF theEOH = rstDUMMY!END_INV_QTY theBOH = rstDUMMY!BEG_INV_QTY myTBLE!EOH = theEOH myTBLE!BOH = theBOH myTBLE.Update Wend... (NOT ALL OF THE CODE) I get the error on the line myTBLE!EOH = theEOH RUNTIME ERROR '3027' Cannot update. Database or object is read-only. I have not specified anything to be read only. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
READ ONLY RECORD SETS
I believe you need
myTBL.Edit ' set the values myTBL.Update -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "James McDowell" wrote in message ... I wrote the following code to read an ACCESS database but I keep getting a READ ONLY ERROR. I am pulling data off of a company dbase and putting it on my smaller DBASE so it will not take so long to run. I have had no problem up until now with .ADDNEW, but it will not allow my to .EDIT Sub write_Actuals() Dim myDBS As Database Dim myTBLE As Recordset, myACTUALS As Recordset, rstDUMMY As Recordset Dim mySQL As String Dim myMOD As Integer Dim mySUB As Integer, myLOT As Integer, myLINE As String, mySKU As String, mySTORE As Integer Dim skuROW As Integer, storeROW As Integer Set myDBS = OpenDatabase("C:\BAT_DCM_SKU_DATA.mdb", , False) rstDUMMY.Close '(from code prior to this point, thought I had to close) Set rstDUMMY = myDBS.OpenRecordset("TEMP_PULL") With rstDUMMY .MoveFirst Do sqlEOH = "SELECT SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "FROM SKU_STORE_DATA " & _ "GROUP BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "HAVING (((SKU_STORE_DATA.mySUB)=" & !SUB & ") AND ((SKU_STORE_DATA.myLOT)=" & !LOT & ") AND ((SKU_STORE_DATA.myLINE)=" & Chr(34) & Format(!Line, "0000") & Chr(34) & ") AND ((SKU_STORE_DATA.FISCAL_WEEK)=" & !FISCAL_YEAR * 100 + !FISCAL_WEEK & ") AND ((SKU_STORE_DATA.mySKU)=" & Chr(34) & Format(!SKU, "00") & Chr(34) & ") AND ((SKU_STORE_DATA.mySTORE)=" & !LOCATION_ID & ")) " & _ "ORDER BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT" 'Debug.Print sqlEOH Set myTBLE = myDBS.OpenRecordset(sqlEOH) Debug.Print sqlEOH While Not myTBLE.EOF theEOH = rstDUMMY!END_INV_QTY theBOH = rstDUMMY!BEG_INV_QTY myTBLE!EOH = theEOH myTBLE!BOH = theBOH myTBLE.Update Wend... (NOT ALL OF THE CODE) I get the error on the line myTBLE!EOH = theEOH RUNTIME ERROR '3027' Cannot update. Database or object is read-only. I have not specified anything to be read only. Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
READ ONLY RECORD SETS
I had the myTBLE.EDIT line in the code at first, but it would error on that
line. When I took it out, it started to error on the lines where I tried to assign values. "Chip Pearson" wrote: I believe you need myTBL.Edit ' set the values myTBL.Update -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "James McDowell" wrote in message ... I wrote the following code to read an ACCESS database but I keep getting a READ ONLY ERROR. I am pulling data off of a company dbase and putting it on my smaller DBASE so it will not take so long to run. I have had no problem up until now with .ADDNEW, but it will not allow my to .EDIT Sub write_Actuals() Dim myDBS As Database Dim myTBLE As Recordset, myACTUALS As Recordset, rstDUMMY As Recordset Dim mySQL As String Dim myMOD As Integer Dim mySUB As Integer, myLOT As Integer, myLINE As String, mySKU As String, mySTORE As Integer Dim skuROW As Integer, storeROW As Integer Set myDBS = OpenDatabase("C:\BAT_DCM_SKU_DATA.mdb", , False) rstDUMMY.Close '(from code prior to this point, thought I had to close) Set rstDUMMY = myDBS.OpenRecordset("TEMP_PULL") With rstDUMMY .MoveFirst Do sqlEOH = "SELECT SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "FROM SKU_STORE_DATA " & _ "GROUP BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT, SKU_STORE_DATA.myLINE, SKU_STORE_DATA.mySKU, SKU_STORE_DATA.mySTORE, SKU_STORE_DATA.FISCAL_WEEK, SKU_STORE_DATA.EOH, SKU_STORE_DATA.BOH " & _ "HAVING (((SKU_STORE_DATA.mySUB)=" & !SUB & ") AND ((SKU_STORE_DATA.myLOT)=" & !LOT & ") AND ((SKU_STORE_DATA.myLINE)=" & Chr(34) & Format(!Line, "0000") & Chr(34) & ") AND ((SKU_STORE_DATA.FISCAL_WEEK)=" & !FISCAL_YEAR * 100 + !FISCAL_WEEK & ") AND ((SKU_STORE_DATA.mySKU)=" & Chr(34) & Format(!SKU, "00") & Chr(34) & ") AND ((SKU_STORE_DATA.mySTORE)=" & !LOCATION_ID & ")) " & _ "ORDER BY SKU_STORE_DATA.mySUB, SKU_STORE_DATA.myLOT" 'Debug.Print sqlEOH Set myTBLE = myDBS.OpenRecordset(sqlEOH) Debug.Print sqlEOH While Not myTBLE.EOF theEOH = rstDUMMY!END_INV_QTY theBOH = rstDUMMY!BEG_INV_QTY myTBLE!EOH = theEOH myTBLE!BOH = theBOH myTBLE.Update Wend... (NOT ALL OF THE CODE) I get the error on the line myTBLE!EOH = theEOH RUNTIME ERROR '3027' Cannot update. Database or object is read-only. I have not specified anything to be read only. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open new record with selected fields from previous record | Excel Discussion (Misc queries) | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
Record sets? | Excel Programming | |||
Record Macro - Record custom user actions | Excel Programming | |||
A question on read the record from excel | Excel Programming |