run time error 3027 Cannot update: database is read only (1 Viewer)

[peskywinnets]

So I'm trying to do this....

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim PresentShippingAddress1  As String
Dim PreviousShippingAddress1 As String

strSELECT = "SELECT DISTINCT EbayOrders.Processed, EbayOrders.OurRef, EbayOrders.ShippingAddress1,EbayOrders.HasOtherOrders "
strFROM = "FROM EbayOrders"
strWHERE = " WHERE (EbayOrders.Processed=No) AND ((EbayOrders.OrderStatus=""paid"") Or (EbayOrders.OrderStatus=""shipped""))"
strOrderBy = "Order by EbayOrders.ShippingAddress1;"
strSql = strSELECT & strFROM & strWHERE & strOrderBy

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

when I come to do this a bit later in my code...

rst!Edit

run time error 3027
Cannot update: database is read only

How do I work around this (the few solutions I read via Google left me glazed!).

 

[sneuberg]

Maybe it doesn't have anything to do with your problem but isn't the syntax

rst.Edit

rather than

rst!Edit

or do you have a field named Edit?

 

[CJ_London]

I'm about to go offline, but two thoughts

if EbayOrders is a linked table to excel, it is not editable. The workaround is to either import the table or you will need to use the excel library in vba to manipulate excel.

The other and more likely reason is that by using distinct, you are effectively grouping the data which makes it uneditable. Solution is to remove the distinct, or try using distinctrow (not sure that will do it what you want tho'), or use an update query instead.

 

[peskywinnets]

Maybe it doesn't have anything to do with your problem but isn't the syntax

rst.Edit

rather than

rst!Edit

or do you have a field named Edit?

Apologies, that was a typo on my part (it was about 1.30am UK time when I posted!) ...I was using rst.Edit (not the rst!Edit I posted)

I woke up this morning afresh & have approached the update another way (which won't mean a whole lot out of context, but you should still get the picture from my approach)...

            strSql = "UPDATE EbayOrders SET EbayOrders.HasOtherUnshippedOrders = Yes WHERE (((EbayOrders.ShippingAddress1) ='" & PresentShippingAddress1 & "'));"
            DoCmd.RunSQL strSql

...i think my original problem has something to do with the Distinct query I've used (incidentally, the tables aren't linked tables...they're local)

So having implemented this other way, the problem is sorted...thanks for your input