Database Issue

[Anthony]

What will happen if I write this line:

db.close

in a sub procedure, when I didn't actually open the database?

Example:

Sub MyProc()

Dim db as Database, rs as Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("MyTable")

With rs
'Do something
End With

rs.Close
Set rs = Nothing

db.Close

Set db = Nothing

End Sub


Notice the line: db.Close. Does this line need to be there? If not, does
the execution of this line cause the database to run slow?

 

[Douglas J. Steele]

In some of the earlier versions of Access (Access 1.0, 1.1 and, I think,
2.0), that could have caused problems.

Newer versions are smart enough to know that you can't close a database if
it's the current database.

If it slows the application down, you'd need an extremely accurate stopwatch
to notice the difference! <g>

 

[Anthony]

Thank you, Douglas.

Well, the reason I ask is because in a former post I got a response that
executing the db.close when the db didn't actually "open" could cause the
database to do something haywire and run sluggish.

But from what I gather from your response, this would not be the case.

Also, a question about the "Persistent Recordset"

We have several FEs that linke to a single BE.

Each of these FEs load a Hidden Form at the AutoExec macro, and in the open
event of the form it instantiates a recordset to a linked table that is
linked to a "dummytable" in the BE.

This recordset is closed at the close event of the form.

Have you heard of this method before?

It is used to maintain good performance speed across all the FEs.

However, we are occasionally experiencing sluggish FE activity that is so
slow. An event takes over a minute, when it should just take about 4
seconds.

Would it be beneficial if we reset the persistent recordset once every 5
minutes on the hidden form's timer event? In other words, every five
minutes, the persistent recordset will die and re-establish.

Or, what about launching a hidden database from the windows startup folder
that instantiates the persistent recordset? That way, no matter how many
times the FEs are closed and re-opened, the persistent recordset will keep
the user logged into the LDB file.

What are your thoughts about this?

Anthony

 

[Douglas J. Steele]

No, you shouldn't need to refresh the recordset. And no, a hidden database
that instantiates a persistent recordset isn't going to buy you anything.
The issue is with the each FE. FE1 having a recordset open won't do anything
for FE2.

Have you read through what Tony Toews has at
http://www.granite.ab.ca/access/performancefaq.htm?

 

[Anthony]

How does the Persistent Recordset work?

The more details, the better

Thanks

 

[Anthony]

Douglas,

On this page: http://www.granite.ab.ca/access/performanceforms.htm, where
Tony mentions assigning the rowsource SQL programmatically at the load event
of the forms, here is my question:

If I am going to later change the recordsource (SQL) at a later event
procedure while the form is open, would it be necessary to assign it at
Form_Load()?

Or, does assigning the SQL recordource to the control at Form_Load(), even
if it is later going to be replaced, does this speed up the processing of
the form?

 

[Douglas J. Steele]

Since you're going to be setting the recordsource in a later event, there's
nothing to be gained by doing it in the Load event, only to change it.

 

[David W. Fenton]

In some of the earlier versions of Access (Access 1.0, 1.1 and, I
think, 2.0), that could have caused problems.

Newer versions are smart enough to know that you can't close a
database if it's the current database.

There's difference between using CurrentDB() and DBEngine(0)(0) to
initialize your variable. If you use the former, closing it never
causes a problem. If you use the latter, the pointer is somehow
different and can lead to problems.

I never close db variables that point to the currently open
database. I think it's better to think about what you're doing and
close only external databases, and not write lines of code that
don't do anything at all.

Of course, I never use CurrentDB() in my code, but instead use my
dbLocal() function to initialize and return the reference to the
currently open database. I just use it directly without ever
initializing any database variables, so this completely obviates the
need to even think about closing it.

 

[Anthony]

Question about this page:
http://www.granite.ab.ca:80/access/performancefaq.htm

What does "- System utilities" suggestion mean (Line 15 of the page.)

??

 

[Douglas J. Steele]

Scroll down the page. Each of those topics is expanded upon further down.