Cant update table

I'm having trouble updating a table in a SQL 2000 database usinb VB6.0. I have no problem opening the table and reading data from a field however when I go to update it I get the following error.

Runtimer error '-217418113 (8000ffff):
Query cannot be updated because the FROM clause is not a single simple table name.

I've tried to help myself with this one but any information which I found referred to someone trying to write to two tables simultaneously. I am simply trying to write to one.

Here's my code

Dim cnn1 As ADODB.Connection
Dim rstRailSet As ADODB.Recordset

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=" & ServerName & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DBName & ";"
cnn1.Open
Set rstRailSet = New ADODB.Recordset

rstRailSet.LockType = adLockOptimistic

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, , , adCmdText
rstRailSet!SLN = RailSLN
rstRailSet.Update

The above message appears when the update command is executed.

Any help is greatly appreciated.maybe i am not an ADO expert but I do not see an update statement here. I see a recordset and a select statement.|||The update is done in the last line of code "rstRailSet.Update". I assume this is all that's required.|||This might be better posted in the VB forum.

gotcha. I had forgtten the rs object had an update command. I just always use either a command object or a con.execute(sql) where sql is an update sql command.

I might try this below.

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, , , adCmdText
rstRailSet("SLN") = RailSLN
rstRailSet.Update

But in your code snippt I do not see where RailSLN is defined or populated. Also according to some yellowing ADO 2.6 documentation on my bookshelf your datasource has to allow bookmarks and and keyset or dynamic cursors.|||Is [Rail Set] a table, or could it be a view?|||Thrasymachus

Tried your suggestion without luck. RailSLN is an argument passed to the sub containing this code. (I excluded it to keep it simple, or so I thought.)

Not sure of the bookmark/dynamic cursor implication. The source is a table within a SQL server 2000 database. I thought that the source of this issue may lie on the SQL server end hence I posted it here.

Blindmand

[Rail Set] is a table in a SQL server 2000 database.

Bare in mind here that I have no problem reading data from the source. The message seems to indicate that it some how can't work its way back. As these things can go I'm not sure whether the message is legit or whether its being triggered by an unrelated issue. (you know how this can go). It occurs when rstRailSet.Update is executed.|||SUCCESS

I set the cursor type to adOpenDynamic and I'm off to the races

rstRailSet.Open "Select SLN From [Rail Set] where Rail_set_ID = '" & RailID & "'", cnn1, adOpenDynamic, , adCmdText

At his point I do not understand enough about cursor type to know why it works but I'll bone up on it to satisfy my curiosity.

Thanks for your suggestions!