Ado Recordset updatable in Code but not via Form

[Steffen]

Hi there.

I got a strange problem here. I'am using a view with view_metadata option
and an Instead Of Update Trigger. I open this view in an ADO recordset and
bind it to my form. The form is not updateable. But when updating the
recordset in code
(adors.fields("myfield").value = 5 and adors.update) it works fine and also
uses the triggers.

Any suggestions?

Thanks in advance,
Steffen.

 

[Steffen]

No one?

Here is some more information.

I'm using an Access 2003 mdb file and an SQL Server 2005 as backend.
The view needs to update more than one table, that's why I use Instead Of
Update Triggers.

Here is a code-example:
Dim adors As New ADODB.Recordset
Dim conn As New ADODB.Connection
conn.ConnectionString = db.getoledbconnstring("fms_kunden", "dlfms01") /*
function that returns the connection string */
conn.CursorLocation = adUseClient
conn.CommandTimeout = 120
conn.Open

adors.ActiveConnection = conn
adors.CursorType = adOpenDynamic
adors.CursorLocation = adUseClient
adors.LockType = adLockOptimistic

adors.Open "select fields from fms.vw_tageskontrolle2 where criteria =
value", , , , adAsyncFetch

Set Me.Recordset = adors

Thanks, Steffen.

 

[Banana]

1) Not related to the problem, but you may want to be aware that setting
CursorType to dynamic cursor is invalid. It doesn't return an error but
will silently be coerced to static cursor type, which is the only cursor
type available for client side.

2) I'd see if removing the adRunAsync makes any difference.

3) Failing this, I'd see if suppressing the triggers allows the form to
be updatable. I've heard of problems with triggers, especially if it
modify the same row in question.

See what you find out from #2 and #3 and we'll work out a solution.

 

[Steffen]

Hi Banana.

Thanks for your reply.

1) I already tried each CursorTypes without success
2) I added adFetchAsync later because it sounded useful
3) It's not possible in my situation to remove the trigger.
The only real problem is, that I can't edit the view in the form itself,
but I can update the recordset without problem. No matter if there are
triggers defined or not.

I believe when creating the view with view_metadata, it doesn't show
it's primary keys to access.

When I link the view in Access and bind the form to it it works as expected.
But this is so slow, that it is not acceptable.

Is there anyone who successfully opened a recordset with a view created with
view_metadata and bound that to a form?

Steffen.