Code Performance... Set rs=CurrentDb.Open...

So, you prefer method B.  Why?

Just what I have used, no actual hard facts behind it, sorry :-(
Dave

Method A is much easier to code if method B has no clear advantages.

AGood part of the question here has to do with comparison of these two method.  I don't want keep wondering which is a better method.

I hope this question will be reviewed by some other experts.

Thanks

Mike

No worries Mike, I was really only poping in to fix your code :P

I hope others chime in too
Dave

forgot to mention ... say you have code that creates Table1, make sure you refresh that particular collection before using DBEngine(0)(0) either as part of your creation code directly or just before you open a recordset or create any other kind of reference to the new object.

either
... code that create new tabledef object
DBEngine(0)(0).TableDefs.Append tdf
DBEngine(0)(0).TableDefs.Refresh


or just before using it ...
DBEngine(0)(0).TableDefs.Refresh
Set rs = DBEngine(0)(0).OpenRecordset("Select Name From Table1")

Steve

Steve,

Doesn't that reference the current DB and not some other mdb?

Dave

Although I do tend to miss out the explicit workspace object - unless I want a transaction of course.

Set db = DBEngine.OpenDatabase("C:\myFile.mdb")

<Doesn't that reference the current DB and not some other mdb?>

it does not reference the CurrentDB object but it does point directly into the structure of the loaded database.

calling Set dbs = CurrentDB causes internal refreshes to get fired whereas Set dbs = DBEngine(0)(0) does not.

<recordsets opened directly with currentdb can go out of scope.>
I could see problems in trying to use CurrentDB in a loop ... you get a new object each iteration so a reference to an object in the previous iteration no longer makes sense ... is this what yiou are talking about going out of scope?

I have not seen this as the only time I use CurrentDB without setting a variable to it is to grab a scalar value and not set objects.

'set an object reference
Set dbs = CurrentDB
Set rst = dbs.OpenRecordset ....

'grab a string value
strSQL = CurrentDB.QueryDefs("MuQuery").SQL


Steve

That's how I always use it too Steve (and Dave too seemingly).

As an example - I've had the following type of code work and fail (but never used live ;-)

Dim rst as DAO.Recordset

Set rst = CurrentDb.OpenRecordSet("Whatever")
Debug.Print rst.Fields(0).Value   '<----Boom right there - Object Variable no longer set

AFAIK it's a known occasional issue.
(But remembering back a good few years now - it might just be a discussion I had with myself back then lol).

Was one of the reasons I was initially reluctant to use
With CurrentDb.OpenRecordSet("Whatever")
    'Do stuff...
End With

But returning the recordset object in a with block seems to better keep it valid.

using a With statement causes VBA to create an internal reference object ... the equivalent of Set dbs = CurrentDB :-)
That is why beyond prettying up your code, using a WITH block actually is a performance optimizer, the more references you do not need to traverse the faster the code.


Steve

Hi Steve

Yeah I know - but I'm suprised that the recordset (an explicit object in it's own right that is created without error) goes out of scope as in the example I gave.
It's technically no different to the With statement.

Of course, if I had

With CurrentDb
    .OpenRecordset(...)
    'Blah blah

Then fair enough... there'd be a reference to the database then held and everything thereafter should be peachy.
But an explicit recordset object has actually proven more flakey than an internal one.

Know what I mean?

I get what you are saying but can't say I have ever experienced it myself. I wonder if that could be a byproduct of a dropped connection? My guess here is that Access is more forgiving (meaning it will try to make a connection again if it has been dropped) that a recordset object.

I rarely use my own recordset objects (I do use Form.Recordset from time to time) because most of my data manipulation can be done with SQL. I just looked up a few examples and I am using Set rst = CurrentDB ... with no problems ... ack ... did I jusy jinx myself ?!?!?!?

lol I doubt it
As I said it usually works.

One day if it does fail for me I'll chase it next time.  (That said, I never use it :-)

>it does not reference the CurrentDB object but it does point directly into the structure of the loaded database.
Doh.. I had a bad choice of words there didn't I :P

Interesting reading, thanks..

thinking on issues with CurrentDB ... want to see this go horribly wrong ... (this may be the issue Leigh described)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset("SELECT * FROM Table1")

'cleanup
dbs.Close    '<--- you just hosed your world by closing the connection of your current database but you don't know it yet
Set dbs = Nothing

'gets better
Debug.Print rst.Fields(0).Value '<--- boom!!!

Steve

Yeah - nothing in this world could persuade me to go close to closing a currentdb database object.

I always close mine, but always the line after I close my RecordSet http:#16260297
Not sure why on earth I do.. But it's always there.

Dave ... for the code you posted it makes perfect sense because you are creating a seperate database object but don't try it with a var that was Set = CurrentDB.

It's the old rule of thumb.
If you open it close it.
If you don't... just put it down gently and back slowly away...

I do the same thing then too.. I've never had any problems:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = Currentdb
    Set rs = db.OpenRecordset("SELECT * FROM myTable;")

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

I suppose that's possible - in that I'd heard it *can* lead to problems - so I ran screaming from it years ago (doh!... broke my own rule about being careful there).

But I remember Steve saying here that he's found it to be reliably fatal.

(Probably a bit like my CurrentDb.Openrecordset() issue )

yes ...it is all in the order of things ... as you said you are closing the recordset object before the database object. I always code my cleanup in reverse order of my original Set statements to make sure I am not opening the door to hanging references by trying to kill an object that can't be because there is still an existing dependency.

I was under the assumption that CurrentDb created a new instance of the database object, where as DBEngine(0)(0) was like a pointer to one..

So I guess I can see you would be casuing mayhems using:

Set db = DBEngine(0)(0)
db.close

but not with Currentdb, which I would have asssumed should be closed under the open it you close it rule

(I also remove objects in the reverse order I create them - no idea if most people do.  But it makes perfect sense to me).

I also agree with Dave - in the definition of CurrentDb.

But I don't close it - because I don't open it. (It's a reference that was returned for me by my mate Bill).
I set it to nothing of course - as with everything.

I think this is one of the reasons I enjoy EE, we have full discussions regarding technical details and after getting different approaches, opinions and reviewing out collective knowledge we can forge standard practices :-)

I read the Access 12 blog from yesterday this morning ... more eye candy for users, nothing for developers yet :-(

I need to catch up on the Blog.  
Typical that it's user friendly stuff.

I don't need new toys (OK they'd be nice) but use good useful functionality and improvements.
Come on MS.  *Impress* me!

I agree Steve, this is a big reason I enjoy EE.. You guys have taught me basically everything I know, and these discussions continually help me build on my knowledge.

Just had a look at the blog, eye candy indeed

I miss questions like this...
;-)