Attachmate updating Access table

[scottian]

Someone please help.

Firstly, im sorry for cross posting.

Im trying to connect attachmate to a database table, id like it to write to one of the fields in the table in the database. But i cant work it out. This is what ive got so far.

Sub Main()
   Dim Sys As Object, Sess As Object
   Dim conn As Object, rs As Object, db As String, sql As String

   Set Sys = CreateObject("Extra.System")
   Set Sess = Sys.ActiveSession
   Set conn = CreateObject("ADODB.Connection")
   Set rs = CreateObject("ADODB.Recordset")
   
   db = "R:\E_G_Compact.mdb"
   sql = "Select FBRef, CustName from tblMain;"
   
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db & ";User Id=admin;Password=;"
   rs.Open sql, conn

   Do Until rs.EOF
      MsgBox rs.Fields("FBRef") & " " & rs.Fields("CustName")
      rs.Fields("CustName") = "Dave"
      rs.MoveNext
   Loop
   rs.Close
   conn.Close
   Set rs = Nothing
   Set conn = Nothing
   Set Sess = Nothing
   Set Sys = Nothing
End Sub

I get an error when Attachmate tries to write to the table, "ADODB Recordset: Current recordset does not support updating This may be a limitation of the provider or of the selected locktype".

Im thinking that <Set rs = CreateObject("ADODB.Recordset")> is the problem and that i should be linked directly to the database.

Thanks for any help.

Ambition..........If you dont use it, you wont lose it

 

[CautionMP]

scottian,
You have a forward only, read only recordset (the defaults for [tt]Recordset.Open[/tt]), you need to open up a recordset you can edit ([tt]adOpenDynamic[/tt], [tt]adLockOptimistic[/tt]).

rs.Open sql, conn[b], 2, 3[/b]

Then you will need to update the changes:

   Do Until rs.EOF
      MsgBox rs.Fields("FBRef") & " " & rs.Fields("CustName")
      rs.Fields("CustName") = "Dave"
      rs.Update
      rs.MoveNext
   Loop

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[scottian]

CautionMP,

Thanks for the reply, I didnt know about the rs.Update

Ambition..........If you dont use it, you wont lose it