Access run-time and CurrentDB()

  • Thread starter Thread starter Marc B
  • Start date Start date
M

Marc B

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
 
It is not an ADP or ADE file, is it?

There is no CurrentDb in an Access Project, AFAIK.
 
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" <[email protected]>
Newsgroups: microsoft.public.access.modulesdaovba
Sent: Tuesday, September 16, 2003 11:48 PM
Subject: Re: Access run-time and CurrentDB()
 
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").
Click to expand...

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.
Click to expand...

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
Click to expand...

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.
Click to expand...

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.
 
Have you split your application ...

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


And see Van's reply, again.
 
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
 
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.
Click to expand...

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.)
Click to expand...

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.aspx?scid=kb;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/DatabasePerformanceTips.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.
Click to expand...

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
Click to expand...

Not enough information at this end to comment.
 
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
 
Back
Top