I've got a command button that on rare occasions
fails with "Object invalid or no longer set"

I've got such a case now and
in the debug window, if I type the following
Me.RecordSource = ""
?me.RecordSource

Me.RecordSource = strSql
?me.RecordSource
SELECT * FROM qryTask

?me.RecordsetClone.eof
Object invalid or no longer set



because the recordsource changed, I presume the ME object is valid
so it must be the recordsetClone object that isn't valid

the same instance of the application worked 15 mins
I wonder what could have caused the error ?
the recordsource only returns 142 records

On Tue, 7 Jun 2011 14:48:04 -0700 (PDT), Roger
<lesperancer (AT) natpro (DOT) com> wrote:

I cannot reproduce this.
Perhaps you have some form of corruption. You could export your form
to text using the SaveAsText method (search for it online; it is
officially undocumented), followed by its cousin LoadFromText.
You have already compacted the db, right?
Can you repro this with a new minimal form created by the form wizard?

-Tom.
Microsoft Access MVP

On Jun 7, 8:42*pm, Tom van Stiphout <tom7744.no.s... (AT) cox (DOT) net> wrote:
the problem happens very rarely, 100 users using this form (individual
MDBs)
new copy of MDBs used daily
MDB contents updated weekly

yes, decompiles and compacts are done weekly

in yesterday's case, the MDB had been opened for many hours and I'd
clicked the command button a few dozen times

there is obviously corruption, I'd just like to understand where/how

I run into this also, on one system that is pretty complex.
When it does happen, it is mostly under similar conditions - where I am
changing a subform's recordsource via code.
However, it also sometimes happens to subforms with fixed recordsource and
linked to parent.

The current application was recently upgraded from Access 2000 FE/BE to
Access 2007 FE with SQL Server back end.
The problem happens in both cases.
In some cases I can confirm a loss of connection to the BE, but can not
determine why.

Related, I also find that when debugging code in 2007, I also get similar
errors that do not occur when not debugging. There is often not way out,
other than killing Access via task manager.

AG
Email: npATadhdataDOTcom


"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Jun 9, 6:36*am, "agiamb" <NOSPAMagi... (AT) newsgroup (DOT) nospam> wrote:
how do you 'confirm' that the connection to the BE is lost ?
I'm connecting to sql server 2005, and the application's first form
(hidden) always open a recordset to a sql server table
I suppose network problems are possible, and it would be great if I
had a tool to detect that condition

Hi Roger,

My error handler collects a great deal of infomation, including my own
procedure stack.
Included in that is checking the connection to the back end, as sometimes
users connect to the wrong back end.
When I receive the error file, I can spot something like that quickly.

Sometimes the error generated includes 'ODBC Communication Link Failure'.

With an mdb/accdb back end, I record the connect string and call RefreshLink
on the tabledef.

With a sql server back end, if you call RefreshLink and the connection is
bad, you will get the Access login dialog.
So, I extract the connect string from a table or passthrough query and
rebuild it into an ADO conncet string, then open an ADO connection and
recordset, where I can trap any errors that occur.
If that succeeds, then I call RefreshLink on the tabledef.
Sometimes, the ADO connection succeeds and the ODBC connection fails.
I haven't found an explanation, but closing and reopening the front end
fixes it.
At one point I thought it also had to do with the amount of time the front
end was open, so I started recording that also.
While it does seem related, the same problem happens when the front end is
only open for a short time.

While I wonder if it is a bug in the ODBC driver, I can't say that it is.
Once I saw an error in the system log, related to the network card, but on
other machines, nothing.
The network is in good condition and has agents monitoring for problems, so
I am still searching for a solution.

Again, like you, the problem does not occur often and is not reproducable,
so I can't even open an incident with Microsoft.

--

AG
Email: npATadhdataDOTcom


"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Jun 9, 6:36 am, "agiamb" <NOSPAMagi... (AT) newsgroup (DOT) nospam> wrote:
how do you 'confirm' that the connection to the BE is lost ?
I'm connecting to sql server 2005, and the application's first form
(hidden) always open a recordset to a sql server table
I suppose network problems are possible, and it would be great if I
had a tool to detect that condition