database or object read only???? Help!!!!

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

I am writing a small module in A97.
the sql has select fields from table group by fields having count(*) =1

I want to loop thru all the records the above query pulls and update a field
but it is giving me error "Can't update. Database or object is read-only"

Set Temprec = mydb.OpenRecordset(sqlquery)
Temprec.Sort = "field1,field2"

Temprec.MoveFirst
Do Until Temprec.EOF

Temprec.Edit
Temprec.Fields("field3") = "O"
Temprec.Update
Loop

Any help is appreciated.
 
Set Temprec = mydb.OpenRecordset(sqlquery)
Temprec.Sort = "field1,field2"

Temprec.MoveFirst
Do Until Temprec.EOF

Temprec.Edit
Temprec.Fields("field3") =
Temprec.Update
Loop
Click to expand...

It kind of depends what the sqlquery querydef does -- if it is not
updateable, or only partly updateable, then you may have to restructure it
so that it is properly so.

On the other hand, you kay find it simpler just to do something like

UPDATE MyTable
SET Field3 = "O"
WHERE Something = True

which will run faster by aeons and be easier to debug.

HTH


Tim F
 
If you're using the database OK (eg to save data)
then this won't apply. Anyway, I think you'd have
noticed this anyway on database startup. But as
an outside chance / addition to Tim's reply...

As the error message you're getting says:
"Database ... is read only"
is it possible the database file itself is read-only?

For example, if you copied the database from a
cdrom, it may now be read-only.

To check, start Windows Explorer, right-click the
database file (eg mdb file), and click properties
on the right-click menu. If checked, uncheck the
read-only box.

Regards
Geoff
 
Update statement is not allowing group by and the database is not read-only.
I have tried different ways but to no avail.

Thank you for your help
 
Did you create an SQL to find non-duplicated records?

Can you post the SQL?

Tim says an update SQL will run much faster than
editing a recordset. So that'd be the preferred method.

However, it may help to note that the following DAO
recordsets are not updateable:

snapshot recordsets
recordsets based on crosstab query
recordsets based on union query
user does not have permission to update table.

DAO recordsets have an updateable property,
which can be tested (for true or false):

If Temprec.updateable then
' code to update...
end if

Glad to help further if you can post more detail.
Regards
Geoff
 
Update statement is not allowing group by
Click to expand...

Group By queries are never updateable: the rows returned don't exist in the
tables because they are calculated. That is what a Grouping query is for.

Please explain a bit more about what you are trying to acheive: I think
there is a better way.

All the best


Tim F
 
Thank you Tim and Geoff for your help. Seems like I need to take a longer
route to solve the problem.
The sql I am using is
sqlquery ="select field1, field2, status from table
group by filed1,field2,status
having count(*)=1"

set recordset = mydb.openrecordset (sqlquery)
do until recordset.eof
rs.edit
rs.fields("statuts") ="A"
rs.update
rs.movenext
loop
 
select field1, field2, status
from table
group by filed1,field2,status
having count(*)=1
Click to expand...

I am trying to work out what query does (allowing for the typo)... it seems
to be omitting records where these three fields are repeated, and returning
the rest.

The brute force solution would probably be to turn the whole thing round,
and turn it into a WHERE NOT IN () subselect, but I can't think of anything
very obvious.

The group on m.p.a.adpsqlserver are really good on obscure SQL -- they may
be able to help work this round into an updateable query.

Best of luck

Tim F
 
There seems to be a problem with your SQL statement,
which I tested as follows. (I'm explaining in detail in
the hopes you find this helpful.)

I created a database with a table which had the fields
Field1, Field2, Field3, and Status and added some data
to the table.

I created a new query in design view and hit the close
button when prompted to add a table to the design grid.

In query design, I opened the View menu and selected
SQL view and pasted in your SQL statement, that is -

select field1, field2, status from table1
group by filed1,field2,status
having count(*)=1

I opened the View menu again and selected
Design view, saved the query and then tried to run it.

I received the error "You tried to execute a query that
does not include the specified expression 'Field1' as
part of an aggregate function."

So there's a problem with the SQL.

I think the best starting point is to design a new query,
make sure it works, and then turn the query into an
SQL statement for use in your code.

You may find it easier if you create the query using the
"Find duplicates" wizard.

Then, open the resulting query in design view and
change the greater-than-one expression (>1) in the
criterion row to an equals expression (=1). As I said
in my earlier post, it looks like you want non-duplicated
records.

When the query works OK, you can turn it into an
SQL statement as follows:

open the query in design view
open the View menu and select SQL view
copy the SQL statement and paste it into your vba code.

As your SQL statement isn't working at present, it's
difficult to know what you were trying to achieve or
whether the above advice will help.

I don't know whether you'd be able to turn your
"Select" query into an "Update" query. If you can,
you won't need to edit the recordset in vba - you could
run the Update SQL from vba instead, which will be
quicker.

Good luck.
Geoff
 
select field1, field2, status from table1
group by filed1,field2,status
having count(*)=1
....

I received the error "You tried to execute a query that
does not include the specified expression 'Field1' as
part of an aggregate function."

So there's a problem with the SQL.
Click to expand...
Yes: it's the misspelled 'filed1' in the second line.


Tim F
 
Tim

Yes - I noticed the typo too and then pasted it in anyway.
But even with the typo corrected, the select query returns
all records on my limited test sample (ie seemingly
doesn't filter out any records - which is at odds with KP's
original post). So I remain baffled as to the original
SQL's intention.

I hope this discussion has been helpful to KP and that a
method for solving the problem is now available.
Perhaps KP already has clues about that "longer route."

Regards
Geoff
 
But even with the typo corrected, the select query returns
all records on my limited test sample (ie seemingly
doesn't filter out any records - which is at odds with KP's
original post).
Click to expand...

It will remove records in which the three field are non-unique, but I find
it hard to know in what situation that would be helpful. It's kind of the
opposite of a "find duplicates" query. One has to assume that there is a PK
in the table that is not included in the fields mentioned, and so it should
be possible to use that to get an updateable recordset (or even an update
query, which is what he seems to want). Without more information, though,
it's hard to say.

The best resource I have found for obscure SQL queries is the
m.p.a.adpsqlserver group.

All the best


Tim F
 
Tim,
It's kind of the opposite of a "find duplicates" query.
Click to expand...

Yes, that's what I thought - as is obvious from my previous
posts when (a) I asked if the SQL was intended to find
non-duplicated records and (b) when I said that the 'Find
Duplicates' query wizard could be used to create a working
query that finds duplicates, but then could be reversed into
a 'Find Non-Duplicates' query by changing the criterion
expression from 'greater-than-one' to 'equals-one'.

As you say, it's difficult to see how to proceed with the
information to hand and, indeed, as you said before, there
may well be a better way forward. It'll be interesting to see
if more info is forthcoming. I think KP may already have
ideas on this score.
The best resource I have found for obscure SQL
queries is the m.p.a.adpsqlserver group.
Click to expand...

I noted your saying this before. An interesting tip I've
made a note of.

Regards
Geoff
 
Back
Top