Hello all,

In a split database run-time environment I am getting error messages when
using
set db = CurrentDB() before accessing a recordsource.

error is: "Object Invalid or No Longer Set"

I was wondering if it would help to explicitly state the name of the
database before accessing the records in the backend.

marc

There is no way you will get the message "object invalid or no longer set"
when execurting the line:
set db = currentdb()

TC


"Marc B" <(E-Mail Removed)> wrote in message
news:A%O9b.2223$(E-Mail Removed)...
> Hello all,
>
> In a split database run-time environment I am getting error messages when
> using
> set db = CurrentDB() before accessing a recordsource.
>
> error is: "Object Invalid or No Longer Set"
>
> I was wondering if it would help to explicitly state the name of the
> database before accessing the records in the backend.
>
> marc

It is not an ADP or ADE file, is it?

There is no CurrentDb in an Access Project, AFAIK.

--
HTH
Van T. Dinh
MVP (Access)



"Marc B" <(E-Mail Removed)> wrote in message
news:A%O9b.2223$(E-Mail Removed)...
> Hello all,
>
> In a split database run-time environment I am getting error messages when
> using
> set db = CurrentDB() before accessing a recordsource.
>
> error is: "Object Invalid or No Longer Set"
>
> I was wondering if it would help to explicitly state the name of the
> database before accessing the records in the backend.
>
> marc
>
>

TC,

Sorry for the confusion, what I was trying to get across is that I was told
not to use
simply "set db = currentDB()" but rather to be more explicit by entering the
name of the database such as:

Set db= DBEngine.Workspaces(0).OpenDatabase("DBName.mdb").

there is a database in a multi-user/ multi-platfom environment using
AccessRT 2000 that keeps getting corrupted and have tried multiples
suggestions from various sites.

I read on one the following site that its best to have a global recordset
open when an application is started:
http://www.granite.ab.ca/access/performancefaq.htm

that way the ldb file doesnt get corrupted when it can't be closed when
there are still users accessig it.

I have also tried replacing VBA recordset updates with update and append
queries but to no avail....

any suggestions would be greatly appreciated.

Marc

----- Original Message -----
From: "TC" <(E-Mail Removed)>
Newsgroups: microsoft.public.access.modulesdaovba
Sent: Tuesday, September 16, 2003 11:48 PM
Subject: Re: Access run-time and CurrentDB()


> There is no way you will get the message "object invalid or no longer set"
> when execurting the line:
> set db = currentdb()
>
> TC
>
>
> "Marc B" <(E-Mail Removed)> wrote in message
> news:A%O9b.2223$(E-Mail Removed)...
> > Hello all,
> >
> > In a split database run-time environment I am getting error messages
when
> > using
> > set db = CurrentDB() before accessing a recordsource.
> >
> > error is: "Object Invalid or No Longer Set"
> >
> > I was wondering if it would help to explicitly state the name of the
> > database before accessing the records in the backend.
> >
> > marc
>
>
>


"Marc B" <(E-Mail Removed)> wrote in message
news:A%O9b.2223$(E-Mail Removed)...
> Hello all,
>
> In a split database run-time environment I am getting error messages when
> using
> set db = CurrentDB() before accessing a recordsource.
>
> error is: "Object Invalid or No Longer Set"
>
> I was wondering if it would help to explicitly state the name of the
> database before accessing the records in the backend.
>
> marc
>
>

> Sorry for the confusion, what I was trying to get across is that I was told
> not to use
> simply "set db = currentDB()" but rather to be more explicit by entering the
> name of the database such as:
>
> Set db= DBEngine.Workspaces(0).OpenDatabase("DBName.mdb").

Aside from a performance penalty, which isn't really a concern unless you are
doing something inside a loop (for example), there is absolutely no problem with
using:

Set db = CurrentDb()

> there is a database in a multi-user/ multi-platfom environment using
> AccessRT 2000 that keeps getting corrupted and have tried multiples
> suggestions from various sites.

Always release your object variables' resources when you are done with them:

rs.Close
Set rs = Nothing
Set db = Nothing

> I read on one the following site that its best to have a global recordset
> open when an application is started:
> http://www.granite.ab.ca/access/performancefaq.htm

That is a good performance tip.

> that way the ldb file doesnt get corrupted when it can't be closed when
> there are still users accessig it.
>
> I have also tried replacing VBA recordset updates with update and append
> queries but to no avail....
>
> any suggestions would be greatly appreciated.

Have you split your application and placed copy of the front-end on each
workstation so that multiple users aren't opening the same application file? If
not, I suspect that is the root of much of your trouble.

--
Bruce M. Thompson, Microsoft Access MVP
(E-Mail Removed) (See the Access FAQ at http://www.mvps.org/access)
>> NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<

> Have you split your application ...

Doh! I now remember that you said it was split. <blush>


And see Van's reply, again.

--
Bruce M. Thompson, Microsoft Access MVP
(E-Mail Removed) (See the Access FAQ at http://www.mvps.org/access)
>> NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<

Bruce, thanks for taking the time to help out.

I AM using loops to update records. For example, I have a subform that
allows users to change the position of records by changing the sequence
numbers and then clicking a button that renumbers the records in increments
of 10.

But I don't see why using only "set db = currentDB()" before opening a
recordset should be leading to problems.
I am getting messages I have never seen before (in 7 yrs of Access) and
started wondering if it was a problem with the server.
(the client has an IBM i-series that they use as their AS400 and to host 18
outlook accounts.)

To give you an example of some freaky things going on. A user printed a
record and its details displayed in a form/subform(clicked a print button)
and then left the database in a minimized state to run some other task. Upon
maximizing it to carry on, her subform records were littered with "#delete"
spread over all the fields in multiple records.

I am thinking it may be better to just import records locally, allow a user
to manipulate data and then use delete and append queries to send the data
back to the back end tables



"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message
news:(E-Mail Removed)...
> > Sorry for the confusion, what I was trying to get across is that I was
told
> > not to use
> > simply "set db = currentDB()" but rather to be more explicit by entering
the
> > name of the database such as:
> >
> > Set db= DBEngine.Workspaces(0).OpenDatabase("DBName.mdb").
>
> Aside from a performance penalty, which isn't really a concern unless you
are
> doing something inside a loop (for example), there is absolutely no
problem with
> using:
>
> Set db = CurrentDb()
>
> > there is a database in a multi-user/ multi-platfom environment using
> > AccessRT 2000 that keeps getting corrupted and have tried multiples
> > suggestions from various sites.
>
> Always release your object variables' resources when you are done with
them:
>
> rs.Close
> Set rs = Nothing
> Set db = Nothing
>
> > I read on one the following site that its best to have a global
recordset
> > open when an application is started:
> > http://www.granite.ab.ca/access/performancefaq.htm
>
> That is a good performance tip.
>
> > that way the ldb file doesnt get corrupted when it can't be closed when
> > there are still users accessig it.
> >
> > I have also tried replacing VBA recordset updates with update and append
> > queries but to no avail....
> >
> > any suggestions would be greatly appreciated.
>
> Have you split your application and placed copy of the front-end on each
> workstation so that multiple users aren't opening the same application
file? If
> not, I suspect that is the root of much of your trouble.
>
> --
> Bruce M. Thompson, Microsoft Access MVP
> (E-Mail Removed) (See the Access FAQ at http://www.mvps.org/access)
> >> NO Email Please. Keep all communications
> within the newsgroups so that all might benefit.<<
>
>

> I AM using loops to update records. For example, I have a subform that
> allows users to change the position of records by changing the sequence
> numbers and then clicking a button that renumbers the records in increments
> of 10.

Unless you are setting a reference to CurrentDb() each time you pass through the
loop, the performance issue doesn't arise.

> But I don't see why using only "set db = currentDB()" before opening a
> recordset should be leading to problems.
> I am getting messages I have never seen before (in 7 yrs of Access) and
> started wondering if it was a problem with the server.
> (the client has an IBM i-series that they use as their AS400 and to host 18
> outlook accounts.)

It's hard to say where you problem originates as you haven't posted any code for
us to look through. There may be another issue with your code (and I'm not
immune to those after my 8 years of Access). Take a look at the following MS
Knowledge Base article for some information that might help:

ACC2000: "Object Invalid or No Longer Set" Error with CurrentDb
http://support.microsoft.com/default...b;en-us;200592

Then, again, it may be that your VBA contains some corruption and you need to
"decompile" the MDB (there seems to be a rash of corruption in the last couple
of weeks!!). For information on that, see the following:

http://www.databasecreations.com/Dat...rmanceTips.pdf
http://www.granite.ab.ca/access/decompile.htm
http://www.trigeminal.com/usenet/usenet004.asp?1033

The procedure I use when implementing this is as follows:

1) BACK UP YOUR MDB FILE!
1) BACK UP YOUR MDB FILE! (I meant it the first time <g>)
2) Compact the MDB.
3) Implement the "/decompile" as described in the articles I referenced.
(Access 2000, and later, don't provide the confirmation dialog that
existed in Access 97, but the decompile will still take place.)
4) Open Access normally and compact the MDB again to clean up.
5) Compile and save.
6) Compact again before testing/using.

> To give you an example of some freaky things going on. A user printed a
> record and its details displayed in a form/subform(clicked a print button)
> and then left the database in a minimized state to run some other task. Upon
> maximizing it to carry on, her subform records were littered with "#delete"
> spread over all the fields in multiple records.

Is your back-end file an MDB or of another type?

> I am thinking it may be better to just import records locally, allow a user
> to manipulate data and then use delete and append queries to send the data
> back to the back end tables

Not enough information at this end to comment.

--
Bruce M. Thompson, Microsoft Access MVP
(E-Mail Removed) (See the Access FAQ at http://www.mvps.org/access)
>> NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<

Bruce has given you some good information. If you are still getting errors
in your code, you need to post the actual lines of code, say what line the
error occurs on, & give the actual error number & message.

HTH,
TC


Marc B <(E-Mail Removed)> wrote in message
news:A%O9b.2223$(E-Mail Removed)...
> Hello all,
>
> In a split database run-time environment I am getting error messages when
> using
> set db = CurrentDB() before accessing a recordsource.
>
> error is: "Object Invalid or No Longer Set"
>
> I was wondering if it would help to explicitly state the name of the
> database before accessing the records in the backend.
>
> marc
>
>