Update or addnew using ado

Has anybody ever heard of the error message "insufficient base table information for update or refresh". I get this when trying to call a recordset.update command. the code looks like this:

strSQL = "SELECT tblSubmitterID.Notes From tblSubmitterID WHERE (((tblSubmitterID.Index)=" & IntIndex & "));"
rsDetail.Open strSQL, DBConnection, adOpenKeyset, adLockOptimistic
rsDetail![Notes] = TxtNote.Text
rsDetail.Update <========= error!
rsDetail.Close
Set rsDetail = Nothing

any help would be appreciated. i also get this same error when trying to use an Addnew with an update.

I would recommend opening your recordset with adOpenDynamic instead of adOpenKeyset (mostly because I haven't used adOpenKeyset much and adOpenDynamic seems to be very flexible).

As an alternative to manipulating a recordset you could use SQL via the Execute method of your connection object. I usually prefer this for adds, updates, and deletes as it's less code and objects to mess with (although the concatenation within the SQL can get kinda ugly).

For example, to update a record:
<PRE> DBConnection.Execute "UPDATE tblSubmitterID SET Notes = '" & TxtNote.Text & "' WHERE Index=" & intIndex</PRE>To insert:
<PRE> DBConnection.Execute "INSERT INTO tblSubmitter (Field1, Field2, Field3) VALUES ('Value1', 'Value2', Value3)"
' This example assumes that your PK field is an autoincrement/identity field.
DBConnection.Execute "INSERT INTO tblSubmitter (Notes) VALUES ('" & TxtNote.Text & "')</PRE>To delete:
<PRE> DBConnection.Execute "DELETE FROM tblSubmitter WHERE Index=" & intIndex</PRE>With executes you don't have to worry about recordsets at all.

Good luck,
Paul

The SQL looks funny to me. Try this.

strSQL = "SELECT Notes From tblSubmitterID WHERE Index = " & IntIndex


d. paulson

Thanks Paul, the execute method works like a charm!