Newbie question

[snorky]

I am writing a program to save data back to an Access database. I assumed that I should use:-

fred=1234
adoRecordset!newvalue = fred
adoRecordset.Update

this brings up a 3251 error . Please help

Thanks!

 

[tomschafer]

Try this...

fred=1234
adoRecordset.AddNew
adoRecordset!newvalue = fred
adoRecordset.Update

 

[snorky]

Will this add a NEW record or edit an existing one - which is what I am trying to do ????

 

[tomschafer]

Yes it adds a new record, to edit an existing record use...

fred=1234
adoRecordset.Edit
adoRecordset!newvalue = fred
adoRecordset.Update

 

[snorky]

Tried that - I get the same error message - Object or provider not capable of providing the requested operation 3251
???

 

[snorky]

When I try to use .Edit I get a Method or Data Member not found as If the reference to ADO is not there -but I've referenced everything I can find with ADO in the title to get rid of this !!? ?!?!?!

 

[tomschafer]

Straight Out Of Help...

"You were attempting to execute a method or assign a value to a property that is usually valid for the object, but isn't supported in this specific instance. For example, the Edit method is generally valid for Recordset objects, but not for a snapshot-type Recordset. This error could also occur in cases where the operation isn't permitted due to the type or status of the object ¾ as when trying to use the MovePrevious method on a forward-only – type Recordset. Some operations are also not supported, depending on if you are accessing a Microsoft Jet or an ODBC data source."

 

[snorky]

The .Edit is not in Intellisense - I have a EditMode option in Intellisense - but thats not what I need, Ho Hum!

 

[Ed2020]

Make sure you declare your recordset like this:

Dim MyRst as ADODB.Recordset

Ed Metcalfe.

 

[snorky]

Here try this - here's some of the code I'm using
Dim totaliser
Set adoConnection = New ADODB.Connection
'create new recordset
Set adoRecordset = New ADODB.Recordset
'build connection string
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=J:\Group\Production\New Shift Log\shiftlogsetup.mdb"

'open db
adoConnection.Open ConnectString
'open recordset
adoRecordset.Open "1ctu12vdu", adoConnection, adOpenDynamic, adLockBatchOptimistic
totaliser = 12345
adoRecordset.MoveFirst
adoRecordset.Update "shift1value", totaliser

adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing

This runs OK but doesn't write anything into the field ???
The code usually runs in a loop to update every record as the loop goes around , but I now get a "Number of rows with pending changes exceeded the limit" from the "provider" which I assume is the JET engine. If I am updating immeadiately - why are there "pending" changes waiting and where ? ? ? I think I'm just missing something fundamental here !??!

Regards - DimWit

 

[tomschafer]

I think it's all in the type of recordset you are using.
I don't do much with Access 2K. But I would try changing your lockedits option from adLockBatchOptimistic, adLockPessimistic.

 

[snorky]

I tried ( after looking on the MSDN website ) adding a adorecordset.UpdateBatch and that works ?????? Why - I don't know and am mistifed why the simple example didn't
Thanks to all who replied for your help - I'm going away now to lie down in a darkened room.......

Snorky