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

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

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