VB Forum / Database Access / June 2008

On Jun 27, 12:48 pm, argusy <arg...@slmember.on.net> wrote:
<snipped>

I can't think of anything specific, but ..., I would be uncomfortable with
creating a new Connection every 1-2 seconds if the above is a true
reflection of your code.

ADO uses connection pooling. Thus it is possible with a tight loop to
actually have more than one "connection". [The error "Operation must use an
updatable query" is to some extent a catch-all.] This 'new' connection will
not be reflective of any specific lock/cursor attributes you may have set
but the default authentication info.

One would have to know more about the specific code you are using to provide
more information. But in the meantime try creating a single ADODB Connection
object outside the 1-2 second loop, and then arrange your code to do
something like this ...

' on init of App ..
Dim adoConn As ADODB.Connection
Set adoConn = New ADODB.Connection
' set the connection string
' set the attributes
...
' loop
       ' create strQry
   adoConn.Open
   adoConn.Execute strQry
   adoConn.Close
   ...
' on termination of app
 Set adoConn = Nothing

and see if the problem doesn't go away.

Also, are you enumerating the ADODB.Connection.Errors collection to check
for additional information?

-ralph