Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-03-22
33
Medium Priority
?
429 Views
Last Modified: 2009-12-16
Would you prefer method A or method B, and why?  

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

B:
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?

Thanks.
0
Comment
Question by:Mike Eghtebas
  • 11
  • 9
  • 9
  • +1
31 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 672 total points
ID: 16260297
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;")

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

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

Dave
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16260339
So, you prefer method B.  Why?
0
 
LVL 34

Expert Comment

by:flavo
ID: 16260351
Just what I have used, no actual hard facts behind it, sorry :-(
Dave
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16260439
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
0
 
LVL 34

Expert Comment

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

I hope others chime in too
Dave
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 664 total points
ID: 16261465
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.

Steve

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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16261519
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
0
 
LVL 34

Expert Comment

by:flavo
ID: 16261919
Steve,

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

Dave
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 664 total points
ID: 16262890
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).
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16263054
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")
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16268335
<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


0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16268769
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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16271684
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16271786
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?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16272776
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 ?!?!?!?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16272815
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 :-)
0
 
LVL 34

Expert Comment

by:flavo
ID: 16273826
>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..

0
 
LVL 39

Expert Comment

by:stevbe
ID: 16280292
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



0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16280325
Yeah - nothing in this world could persuade me to go close to closing a currentdb database object.
0
 
LVL 34

Expert Comment

by:flavo
ID: 16280348
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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16280383
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.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16280402
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...
0
 
LVL 34

Expert Comment

by:flavo
ID: 16280418
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16280456
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 )
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16280463
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.
0
 
LVL 34

Expert Comment

by:flavo
ID: 16280467
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16280560
(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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 16280757
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 :-(
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16281075
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!
0
 
LVL 34

Expert Comment

by:flavo
ID: 16281094
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16541306
I miss questions like this...
;-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month20 days, 20 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question