set db = nothing

[Darth Vodka]

hi all

just wondering... i know everyone always says to set the object variables to nothing at the end...

but what sort of error could this cause? or is it just good house keeping?

 

[Atomic Shrimp]

It releases the system resources that were allocated to that object. In theory, omitting it could adversely affect system resources - I guess if you set a large number of objects (or a collection of them that happened to consume a lot of resources), it could provoke a crash.

 

[Darth Vodka]

there's a database at work and it sets the variable db to CurrentDB about 100 times...

db is declared in every module and the code is one big rant

db is never set to nothing...

at one point there is a db.execute "qry_someappendthing"

and if it runs it totality, it appends 400 records. if the line is run in isolation, it appends 3000

having gone through and set the variable to nothing all the time...it works

WEIRD

 

[LPurvis]

You're saying that a DAO database object variable, that is never set, executes statements?
It can't. :-s
Can you post this example?

FWIW an entire thread about using a db object and setting to nothing...
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1720104

 

[LPurvis]

Re-reading I'm not sure what you're saying actually. lol
That if an insert query is executed by itself it inserts 3000 rows - but as part of a series of statement just 400?

Could that not depend upon the other statements altering criteria etc?
Impossible to speculate really without knowing what's going on under the bonnet.

 

[Darth Vodka]

Re-reading I'm not sure what you're saying actually. lol
That if an insert query is executed by itself it inserts 3000 rows - but as part of a series of statement just 400?

Could that not depend upon the other statements altering criteria etc?
Impossible to speculate really without knowing what's going on under the bonnet.

it's never set to nothing after being used and it's declared many many times

and for some reason, setting it to nothing within every procedure works...and when it doesn't work, it still runs but append less queries...or maybe it's deleting more there's two lines

when the function is called on it's own...it works

Function AddThem()
Set db = CurrentDb
db.Execute (" DELETE FROM [sometable] WHERE Left([somefield],2)='AB' Or Left([somefield],2)='CD' ")
DoCmd.OpenQuery "qrySomeAppendThingy"
End Function

 

[Atomic Shrimp]

Setting the same variable to the same object many times probably isn't going to impact the system any more than setting it once - where you'd be likely to run into problems would be if it was Set db1 = CurrentDB in one bit of code, set db2 = CurrentDB in the next, and so on, for 400 instances - without a cleanup in the form of setting them to nothing, it could possibly consume a lot of resources and/or cause a crash.

Although some of that might depend on how Access handles private variables - I'm not sure if it does an automatic cleanup at the end of a private function or sub...

 

[Rabbie]

Darth

just as a matter of curiosity do you have Option Explicit at the start of your modules?

Just wondered because in the code you posted I didn't see any DIM db as Database statement.

 

[LPurvis]

In an iterative loop (i.e. executed lots of times ;-) constantly reassigning a database object variable to CurrentDb does consume extra overhead - as CurrentDb isn't an object but a method of the Application object which returns an object of DAO Database type - but performs the overhead of refreshing the child collections first.

Harmful? Probably not. Wasteful? Somewhat.
As to the problems with your code, again it's hard to speculate without knowing what the queries actually are and are doing - however the fact that you're employing two different methods of executing statements isn't helpful.
If you already have the database object created - why not use it for both?

db.Execute " DELETE FROM [sometable] WHERE Left([somefield],2)='AB' Or Left([somefield],2)='CD' "
db.Execute "qrySomeAppendThingy"

Bearing in mind the potential for using the same db object throughout all iterations / procedures.

Cheers.

 

[Darth Vodka]

Darth

just as a matter of curiosity do you have Option Explicit at the start of your modules?

yes...first thing i looked for. i shortened the code, there's tonnes of it

 

[Darth Vodka]

Harmful? Probably not

that's what i thought, but i've actually got an issue of not setting to nothing (and maybe an issue with variable scope) actually losing me records from an append, which is harmful...

As to the problems with your code, again it's hard to speculate without knowing what the queries actually are and are doing

i can't post the db in totality i'm afraid

however the fact that you're employing two different methods of executing statements isn't helpful.
If you already have the database object created - why not use it for both?

agreed, i didn't write it, i was only looking at it for the team after they wanted an answer on how to fix it

thanks

 

[LPurvis]

>> actually losing me records from an append, which is harmful...
I'm not convinced of this as the source of this problem though.

>> i can't post the db in totality i'm afraid
Would need just the structure of the relevant tables with a few scambled data records in (which fail the append as you mentioned) and the code you run to execute the append. If it fails for you still then it doesn't need to be live data of the whole app. (Without it I can't offer anything more than I have above Re changing the execution methods to be better aligned).

Cheers.