Run-Time Error 3027

[loydlr]

Getting error 3027 and can't figure out why.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT tblBOM.PolyUOMcost, tblPartsTEMP.CurrentCost " & _
"FROM tblBOM " & _
"INNER JOIN tblPartsTEMP ON tblBOM.PolyID = tblPartsTEMP.Numbered " & _
"Where Not IsNull(tblBOM.PolyID)"

Set rs = db.OpenRecordset(strSQL)


With rs
Do Until .EOF = True
rs.Edit
rs("PolyUOMcost") = rs("CurrentCost")
rs.Update

.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
Set db = Nothing



tblBOM is a local table
tblPartsTEMP is a linked table

Any help would be greatly appreciated

 

[dhookom]

I'm not sure why don't use and update query. You shouldn't need to use the where clause.

I don't know the description for the error 3027.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya loydlr . . .

[blue]Cannot update. Database or object is read-only.[/blue]

By chance did you copy your backend from a CD?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[loydlr]

It's a relatively small database created to generate Bill of Materials. The database has not been split. All the data is in a single .accdb file except for a single table [tblPartsTemp] on a SQL server.

 

[dhookom]

Are both of these fields primary keys tblBOM.PolyID and tblPartsTEMP.Numbered?

I would expect Numbered would have to be.

Duane
Hook'D on Access
MS Access MVP

 

[loydlr]

neither one are primary keys.

 

[dhookom]

My recent reply implied you should attempt to make Numbered a primary key and then try the code. However, as I stated earlier, I think an update query would be more effective.

Duane
Hook'D on Access
MS Access MVP