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

Would you prefer method A or method B, and why?  

Set rs = CurrentDb.OpenRecordset("Select Name From [C:\Folder1\Data1.msb].[Table1]")

Set dbs= Databases("C:\Folder1\Data1.msb")
Set rs = dbs.OpenRecordset("Select Name From Table1")

Q1: Is Set dbs= Databases("C:\Folder1\Data1.msb") syntax correct?
Q2: What method would you use and why?

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1. No,

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

    Set w = CreateWorkspace("", "admin", "")
    Set db = w.OpenDatabase("C:\myFile.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM myTable;")

    Set rs = Nothing
    Set db = Nothing
    Set w = Nothing

2. Above, unless I am / were to do a bulk insert I guess.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
So, you prefer method B.  Why?
Just what I have used, no actual hard facts behind it, sorry :-(
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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.


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

I hope others chime in too
Option C:
Set rs = DBEngine(0)(0).OpenRecordset("Select Name From Table1")

this will be slightly faster than CurrentDB because CurrentDB automatically causes a refresh of all the object definitions  inside all the collections inside the Database container.

creating a seperate connection (option B) I would think would be slower beause you are doing exactly that, creating a seperate connection instead of using one that already exists and has been instantialted.


ps. For anyone reading this who does not already know .... Mike is only using a field called Name as part of an example because he knows this is really a bad idea because Name is a reserved word in Access and will likely cause trouble sooner or later if you use it as a field name.
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.

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

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


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

Leigh PurvisDatabase DeveloperCommented:
I personally would also use B)

Even with local tables - recordsets opened directly with currentdb can go out of scope.
(Not as reliably as I've known tabledefs do so - but they still do).
Leigh PurvisDatabase DeveloperCommented:
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


Leigh PurvisDatabase DeveloperCommented:
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.

Leigh PurvisDatabase DeveloperCommented:
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
    '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 ?!?!?!?
Leigh PurvisDatabase DeveloperCommented:
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")

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!!!


Leigh PurvisDatabase DeveloperCommented:
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.
Leigh PurvisDatabase DeveloperCommented:
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;")

    Set rs = Nothing
    Set db = Nothing
Leigh PurvisDatabase DeveloperCommented:
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 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)

but not with Currentdb, which I would have asssumed should be closed under the open it you close it rule
Leigh PurvisDatabase DeveloperCommented:
(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 :-(
Leigh PurvisDatabase DeveloperCommented:
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
Leigh PurvisDatabase DeveloperCommented:
I miss questions like this...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.