Database or object is read-only problem

[davesmith202]

I have a Form that when opened runs through a Recordset, using the code below. However, it looks like the Query is uneditable because I get the following error:

cannot update. database or object is read-only 3027

Is it not possible to update records using a Recordset if your Form is based upon a Query?

Dave

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

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryAdwordsAzoogledCleaned", dbOpenDynaset)

    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF

        rs.Edit

        Select Case Profit

        End Select

        rs.Update
        rs.MoveNext
    Loop

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

 

[namliam]

That would depend on what qryAdwordsAzoogledCleaned is...

if it is a distinct or Group by query, then yes it is read only and cannot be edited.

Edit:
Thank you for using the code tags !

 

[davesmith202]

Here is the Query...

SELECT tblAdwordsCleaned.GIDSearchEngine, tblAdwordsCleaned.GIDCampaign, tblAdwordsCleaned.GIDAdgroup, tblAdwordsCleaned.GIDSite, tblAdwordsCleaned.GID, tblAdwordsCleaned.Action, tblAdwordsCleaned.Clicks, tblAdwordsCleaned.AvgCPC, tblAdwordsCleaned.AvgPos, tblAdwordsCleaned.Amount AS Cost, tblAzoogleCleaned.Revenue, [Revenue]-[Cost] AS Profit, ([Revenue]/[tbladwordscleaned].[Clicks])*0.7 AS SuggestedBid
FROM tblAdwordsCleaned INNER JOIN tblAzoogleCleaned ON tblAdwordsCleaned.GID = tblAzoogleCleaned.Gid;

Since this is non-updatable, should I someone store the value using a dlookup or something?

 

[namliam]

*UGH* why do people incist on posting unreadable code??

SELECT 
  tblAdwordsCleaned.GIDSearchEngine
, tblAdwordsCleaned.GIDCampaign
, tblAdwordsCleaned.GIDAdgroup
, tblAdwordsCleaned.GIDSite
, tblAdwordsCleaned.GID
, tblAdwordsCleaned.Action
, tblAdwordsCleaned.Clicks
, tblAdwordsCleaned.AvgCPC
, tblAdwordsCleaned.AvgPos
, tblAdwordsCleaned.Amount AS Cost
, tblAzoogleCleaned.Revenue
, [Revenue]-[Cost] AS Profit
, ([Revenue]/[tbladwordscleaned].[Clicks])*0.7 AS SuggestedBid
FROM tblAdwordsCleaned 
INNER JOIN tblAzoogleCleaned ON tblAdwordsCleaned.GID = tblAzoogleCleaned.Gid;

Now that it is readable, I see no reason why this would not be updateable. Only your Profit and SuggestedBid fields will be uneditable as they are calcluations.

I presume GID is a primary key in one of these two tables?!!!

 

[davesmith202]

Gid is not a primary key but just a text field. Do I need to turn them into indexes or something for it to become updatable?

 

[namliam]

Do you have a N:M relationship then ? (Many to Many)

Usually (99.9999%) it is a key somewhere.....

Try opening the query by hand and changing something in the GIDCampaign or something.

Make sure your database is not in a read only state tho... that could be the problem as well.

 

[davesmith202]

There is a PrimaryKey field in both tables but I deleted that Join. Instead, I joined the Gid fields. The Join is "Only include rows where the joined fields from both tables are equal".

Could that have something to do with it?

By the way, I can't update any field in the Query.

 

[namliam]

There is a PrimaryKey field in both tables but I deleted that Join. Instead, I joined the Gid fields. The Join is "Only include rows where the joined fields from both tables are equal".

You are forsaking a PK join over another join?? Why not join on PK??

The join type has nothing to do with it.

 

[davesmith202]

The primary keys on both tables are not related. Only the Gids are! I have noticed the Gid fields on both tables are Index with duplicates Ok. Could it be that?

 

[namliam]

Is it a 1 - to - many relationship ?? Or many - to - many??

 

[davesmith202]

Think I have found a solultion. Change each Gid to a primary key!