Some questions about efficiency: 3

[dirkg]

-if you use a variable like <br> dim dbs as database<br> set dbs = currentdb<br> or set dbs = DBEngine.Workspaces(0).OpenDatabase(&quot;test.mdb&quot<br>is it then better to do dbs.close each time at the end of the procedure to remove dbs from the workspace?<br><br>- what is faster:<br> DoCmd.RunSQL &quot;UPDATE Employees &quot; & _<br> &quot;SET Employees.Title = 'Regional Sales Manager' &quot; & _<br> &quot;WHERE Employees.Title = 'Sales Manager';&quot;<br><br>&nbsp;or<br><br> Set dbs = CurrentDb<br> strSQL = &quot;UPDATE Employees &quot; & _<br> &quot;SET Employees.Title = 'Regional Sales Manager' &quot; & _<br> &quot;WHERE Employees.Title = 'Sales Manager';&quot;<br> ' Create new QueryDef.<br> Set qdf = dbs.CreateQueryDef(&quot;UpdateTitles&quot;, strSQL)<br> ' Execute QueryDef.<br> qdf.Execute'<br><br>-when you want to append one specific record to a recordset, is it better to use the .edit method from code or to execute an SQL?<br><br>All tips are welcome!!<br><br>Greetings,<br><br>Dirk<br><br><A HREF="mailto:dirk.news@yucom.be">dirk.news@yucom.be</A><br>

 

[DougP]

I saw something about using &quot;set dbs = currentdb<br>&quot; instead of set dbs = DBEngine.Workspaces(0).OpenDatabase(&quot;test.mdb&quot<br>It's faster better whatever.<br><br>Yes anytime you open a recordset, close it<br>That's good practice in any language<br><br>This is better/Faster<br>DoCmd.RunSQL &quot;UPDATE Employees &quot; & _<br>&quot;SET Employees.Title = 'Regional Sales Manager' &quot; & _<br>&quot;WHERE Employees.Title = 'Sales Manager';&quot;<br><br>Cause you are not creating another definition i.e. &quot;QueryDef&quot;<br><br>SQL is fastest cause its what Access uses anyway.<br>It takes your code or query and &quot;Converts it to SQL&quot; then passes it to the Jet. ANSI SQL-89 is what Access uses.<br><br>In general Less code that has to be read is faster.<br>One liners are faster that several lines.<br>Access is VERY resource intensive. Today its cheaper to buy a faster PC than re-write code. In some case's a faster PC is the only answer if you link to ODBC.<br>I done tests here:<br>A Pentium II 350 or III or better, with 128 meg or better, on NT workstation is the best combination.<br>Win 2000 Pro is even faster. <br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[CharlesDFW]

Dirkg:<br><br>I would add to DougP's comment.<br><br>If you're hard coding the creation of a querydef, why not simply create the query and keep it in the database as an object. If you don't want users to see it, mark it hidden. I recommend this because as a persistent object in the database, it will benefit from Rushmore. Rushmore will optimize it so that it works more efficiently. When you compact the database, Jet will analyze statistics and keep the query optimized.<br><br>I only create querydefs where I don't want to have users accidentally executing some query. When they're created in code, they aren't as optimized, and run slower.<br><br>Also, do you have a compelling need to create a different database object in your code? If you are only wanting to create a recordset, why not just use the currentdb? Such as:<br><br>dim rst as recordset<br>set rst=Currentdb.Openrecordset(&quot;gofetchmyrecords&quot<br><br>That's one less object that has to be created in memory.<br><br>Charles<br><br>

 

[DougP]

Also if I'm not mistaken when you create a QueryDef it shows up as an object in the database. And the next time you run your code to create it again you will get an Error saying it already exists. So then you have to write more code to delete it. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Amiel]

<b>Absolutely not</b>!<br><br>The statement set db = DBengine(0)(0) expands to :<br><br><font color=red>set local-variable of type database to reference the default database in the default workspace</font><br><br>You should not close the default workspace/database because you never opened it! what you did was create a variable that references the current workspace/database.<br><br>...good programming practice, from the 'C' programming handbook cira. 1985, states that we should be carefull to close all open file handles. You're not opening files with the above statement, simple creating a reference to an existing object that MS Access created when you launched the program. <br><br>Further, as far as performance is concerned. when any global variable in any module is referenced during program execution the entire modules code is loaded into memory and remains loaded until the program exits (by closing the .mdb), you cannot explicitly remove it from memory. Local variables behave like local variables, meaning that the memory allocated for local varables is reclaimed when the function exits.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>