Updating Recordset

[kclarico]

I am trying to loop through a recordset and update a single field by taking out the dashes in the phone number in that field. This is the code I'm using. I am getting the error "Cannot Update. Database or Object is Read Only". I thought the OpenKeyset cursortype and adLockOptimistic lock type would allow me to update the recordset? Am I wrong?

rst.Open ("select * from [T-Mobile] where [SUBSCRIBER #]not in ('311641764','Totals')"), Cnxn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
Do While rst.EOF = False
rst![Subscriber #] = CStr(Replace(rst("SUBSCRIBER #"), "-", ""))
rst.MoveNext
Loop
rst.Update

 

[PHV]

Why not simply an update query ?

UPDATE [T-Mobile]
SET [Subscriber #] = Replace([Subscriber #], '-', '')
WHERE [Subscriber #] Like '*-*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kclarico]

This did not work either.

The table I'm trying to update is a linked Microsoft Excel table. Are there limitations to read/write access to a linked Microsoft Excel table wint using a connection string? How else can I update this spreadsheet?

Thanks for your help!

 

[jibberski]

You have to edit each record. Think of a recordset as a long hallway of doors. So if you want to edit the "door" you have to tell the machine to do so at each doorway.

rst.MoveFirst
Do While rst.EOF = False
rst.Edit
rst("[Subscriber #]") = CStr(Replace(rst("SUBSCRIBER #"), "-", ""))
rst.Update
rst.MoveNext
Loop