Compact BE while idle... so confused!

[David W. Fenton]

Re: Compact BE while idle... so confused!

"Lyle Fairfield" <lylefairfield@ aim.comwrote in
news:1153879408 .805674.48790@7 5g2000cwc.googl egroups.com:

>This would be one good reason to not to maintain an
>open-connection to the back-end through a start-up form or the
>main menu/main form. (Lots of developers do that in that hope
>that network performance is enhanced since an open connection
>exists from program startup.

>
Do you do this John? I have never found that it made any
difference at all and so I have never done it. I know that this
was recommended in The Jet DataBase Programmers Guide 3.0 but that
was a long time ago. With the speed of processors and disk writes
today the creation of a tiny file is instantaneous.
In all honesty though I have never worked with a slow connection
to the backend.

I'm surprised that it was in the Jet 3.0 programmers guide, as I
never encountered problems with this until Access 2000. A97 was
always perfectly fast in creating and deleting the LDB file, but A2K
was *much* slower about it.

Of course, most of my apps maintain some kind of persistent
connection in the background, either an open form or a public db
variable, so I've never experienced a situation where it was an
issue, but I do know that A2K is *much* slower in creating and
deleting the LDB file than previous versions of Access.

It also may make a difference what version of Windows the file
server is.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

Re: Compact BE while idle... so confused!

Lyle Fairfield wrote:

>>This would be one good reason to not to maintain an open-connection to the back-end
>>through a start-up form or the main menu/main form. (Lots of developers do that in that
>>hope that network performance is enhanced since an open connection exists from program
>>startup.

>
>
Do you do this John? I have never found that it made any difference at
all and so I have never done it. I know that this was recommended in
The Jet DataBase Programmers Guide 3.0 but that was a long time ago.
With the speed of processors and disk writes today the creation of a
tiny file is instantaneous.
In all honesty though I have never worked with a slow connection to the
backend.

I use this in several of my apps (DAO, MDB BE). In these cases it was as
a reaction to the specific network. It made a huge difference in
performance. Neer as I can tell the problem is not with the actual
creatin time of the LDB, but some sort of latency issue. There is a lag,
then bang, the connection and file are created. Its sort of like the
server is checking something prior to allowing the connection and
waiting until it gets the go ahead.

I also use code to compact the BE from the FE and in it I drop this
persistant connection along with closing the forms, compact, and then
reconnect.

FWIW, I've never seen this lag when connecting to SQL Server so it may
have something to do with Jet being a file server and locking issues on
the server.

The above is purely imperically derrived speculation. As you say, in
many networks it makes no difference at all, but in those that it does
it makes a big difference. If someone could nail down the server
registry setting that triggers this, then we could verify that instead
of using a persistant connection.

--
Bri

[perryche@yahoo.com]

Re: Compact BE while idle... so confused!

thank you all for your replies. So, how do you maintain a consistent
connection so that ldb is not created and deleted all the time?

Perry

Bri wrote:

Lyle Fairfield wrote:

>This would be one good reason to not to maintain an open-connection to the back-end
>through a start-up form or the main menu/main form. (Lots of developers do that in that
>hope that network performance is enhanced since an open connection exists from program
>startup.

Do you do this John? I have never found that it made any difference at
all and so I have never done it. I know that this was recommended in
The Jet DataBase Programmers Guide 3.0 but that was a long time ago.
With the speed of processors and disk writes today the creation of a
tiny file is instantaneous.
In all honesty though I have never worked with a slow connection to the
backend.

>
I use this in several of my apps (DAO, MDB BE). In these cases it was as
a reaction to the specific network. It made a huge difference in
performance. Neer as I can tell the problem is not with the actual
creatin time of the LDB, but some sort of latency issue. There is a lag,
then bang, the connection and file are created. Its sort of like the
server is checking something prior to allowing the connection and
waiting until it gets the go ahead.
>
I also use code to compact the BE from the FE and in it I drop this
persistant connection along with closing the forms, compact, and then
reconnect.
>
FWIW, I've never seen this lag when connecting to SQL Server so it may
have something to do with Jet being a file server and locking issues on
the server.
>
The above is purely imperically derrived speculation. As you say, in
many networks it makes no difference at all, but in those that it does
it makes a big difference. If someone could nail down the server
registry setting that triggers this, then we could verify that instead
of using a persistant connection.
>
--
Bri

[Bri]

Re: Compact BE while idle... so confused!

perryche@yahoo. com wrote:

thank you all for your replies. So, how do you maintain a consistent
connection so that ldb is not created and deleted all the time?

There are a few ways to do it, but the way I usually do it is to open a
Recordset variable on a table in the BE. I do this in my MainMenu form,
so I know it will always happen. Also, I use a table that is small and
static like a lookup so it has minimum overhead in creating the
recordset. Don't forget to close and reset the variable on close.

Air Code:
In a Module (sets up the public var for the Recordset and the Database):

Private dbC As DAO.Database
Public rsPersistant as DAO.Recordset

Public Property Get db() As DAO.Database
'A Property will set itself as necessary even after a code reset
If (dbC Is Nothing) Then
Set dbC = CurrentDb
End If
Set db = dbC
End Property

In the MainMenu form:

Sub On_Open()
'LinkedTable is the name of the linked table you are using for this
Set rsPersistant = db.OpenRecordet ("LinkedTabl e")
End Sub

Sub On_Close
rsPersistant.Cl ose
Set rsPersistant = Nothing
End Sub



--
Bri

[perryche@yahoo.com]

Re: Compact BE while idle... so confused!

Bri,
Your codes hide my MainMenu, what if I want it to show? Should
I use the code on a dummy form instead?

BTW, Can't I just link the MainMenu directly to a table in the
BE?

Thanks again.
Perry

[John Mishefske]

Re: Compact BE while idle... so confused!

Bri wrote:

perryche@yahoo. com wrote:
>

>thank you all for your replies. So, how do you maintain a consistent
>connection so that ldb is not created and deleted all the time?

>
>
There are a few ways to do it, but the way I usually do it is to open a
Recordset variable on a table in the BE. I do this in my MainMenu form,
so I know it will always happen. Also, I use a table that is small and
static like a lookup so it has minimum overhead in creating the
recordset. Don't forget to close and reset the variable on close.
>
Air Code:
In a Module (sets up the public var for the Recordset and the Database):
>
Private dbC As DAO.Database
Public rsPersistant as DAO.Recordset
>
Public Property Get db() As DAO.Database
'A Property will set itself as necessary even after a code reset
If (dbC Is Nothing) Then
Set dbC = CurrentDb
End If
Set db = dbC
End Property
>
In the MainMenu form:
>
Sub On_Open()
'LinkedTable is the name of the linked table you are using for this
Set rsPersistant = db.OpenRecordet ("LinkedTabl e")
End Sub
>
Sub On_Close
rsPersistant.Cl ose
Set rsPersistant = Nothing
End Sub

Couple of enhancements you can make would include not opening a whole table in the
recordset but instead retrieve one field and no rows.

Set rsPersistant = db.OpenRecordet ("SELECT oneFieldNameHer e FROM [LinkedTable] WHERE 1=0",
dbOpenForwardOn ly)

No records will be returned but the connection (and the .LDB file) will be created.

I'd do this in a Startup form (like a Splash Screen) and then hide the form (Me.Visible =
False). You can then use this form as a way to run code at the end of the user's Access
session.

Since this would be the first form opened it is guaranteed to be the last form closed by
Access regardless of how the user exits the app (the only exception would be a crash).

Putting code in that form's Unload event will allow you to run any type of cleanup you
need at session end like saving the state of the app (form position, current record,
colors, preferences, etc), or compacting or backing up or auditing info, etc.

--
'---------------
'John Mishefske
'---------------

[Lyle Fairfield]

Re: Compact BE while idle... so confused!

Eeeek!

I am thinking that any flavour of MS-SQL Server and unbound JET are
both looking better and better as this thread goes on.

[Bri]

Re: Compact BE while idle... so confused!

Perryche@yahoo. com wrote:

Bri,
Your codes hide my MainMenu, what if I want it to show? Should
I use the code on a dummy form instead?
>
BTW, Can't I just link the MainMenu directly to a table in the
BE?
>
Thanks again.
Perry

It shouldn't hide the form, there is no code in there that would do that.

If you link the form to a table, then you have a detail section and
records showing. That would make the menu form into something else. It
would keep the connection open though.

--
Bri

[Bri]

Re: Compact BE while idle... so confused!



John Mishefske wrote:

Bri wrote:
>

> perryche@yahoo. com wrote:
>>

>>thank you all for your replies. So, how do you maintain a consistent
>>connection so that ldb is not created and deleted all the time?

>>
>>
>>
>There are a few ways to do it, but the way I usually do it is to open
>a Recordset variable on a table in the BE. I do this in my MainMenu
>form, so I know it will always happen. Also, I use a table that is
>small and static like a lookup so it has minimum overhead in creating
>the recordset. Don't forget to close and reset the variable on close.
>>
>Air Code:
>In a Module (sets up the public var for the Recordset and the Database):
>>
>Private dbC As DAO.Database
>Public rsPersistant as DAO.Recordset
>>
>Public Property Get db() As DAO.Database
> 'A Property will set itself as necessary even after a code reset
> If (dbC Is Nothing) Then
> Set dbC = CurrentDb
> End If
> Set db = dbC
>End Property
>>
>In the MainMenu form:
>>
>Sub On_Open()
> 'LinkedTable is the name of the linked table you are using for this
> Set rsPersistant = db.OpenRecordet ("LinkedTabl e")
>End Sub
>>
>Sub On_Close
> rsPersistant.Cl ose
> Set rsPersistant = Nothing
>End Sub

>
>
Couple of enhancements you can make would include not opening a whole
table in the recordset but instead retrieve one field and no rows.
>
Set rsPersistant = db.OpenRecordet ("SELECT oneFieldNameHer e FROM
[LinkedTable] WHERE 1=0", dbOpenForwardOn ly)
>
No records will be returned but the connection (and the .LDB file) will
be created.
>
I'd do this in a Startup form (like a Splash Screen) and then hide the
form (Me.Visible = False). You can then use this form as a way to run
code at the end of the user's Access session.
>
Since this would be the first form opened it is guaranteed to be the
last form closed by Access regardless of how the user exits the app (the
only exception would be a crash).
>
Putting code in that form's Unload event will allow you to run any type
of cleanup you need at session end like saving the state of the app
(form position, current record, colors, preferences, etc), or compacting
or backing up or auditing info, etc.

Yup, good suggestions. I suggested a small table for that reason, but a
limited recordet would do nicely too. I suggested the main menu form as
not everyone uses a splash form, but pretty much everyone has a main
menu (at least I'm assuming so).

--
Bri

[Bri]

Re: Compact BE while idle... so confused!


Lyle Fairfield wrote:

Eeeek!
>
I am thinking that any flavour of MS-SQL Server and unbound JET are
both looking better and better as this thread goes on.

As I mentioned before, this seems to be an issue only with some network
setups. I would prefer to know the server settings needed to correct the
source of the problem, but in the absense of that knowledge, you use
what you can to get the job done. SQL Server has its own qwerks too, and
don't get me started on unbound, that defeats the main benefits of
Access. Each of these has its place, its pros, its cons. The right tool
for the job depends on the job.

--
Bri

[perryche@yahoo.com]

Re: Compact BE while idle... so confused!

Bri and others,
First, thank you very much for all your input. I will try
them soon. BTW, the reason the form was hidden, was because there was
a little typo there...

Set rsPersistant = db.OpenRecordet ("SELECT oneFieldNameHer e FROM
[LinkedTable] WHERE 1=0",
dbOpenForwardOn ly)


Shouldn't it say "db.OpenRecords et" instead of "db.OpenRecorde t"?

Thanks again, anyway.

Perry

[Lyle Fairfield]

Re: Compact BE while idle... so confused!

but pretty much everyone has a main

menu (at least I'm assuming so).

I don't think so. Some of us, at least one, work exclusively on the
premise that forms are for data and menus are for commands and never
the twain shall meet. My applications open to blank screen with a
custom menu at the top. TTBOMK this is the standard Windows interface.
My guess is that more than more than 50% of all problems here start
with something like, "How can I have a button on a form that does
whatever when I click on it ...?" My position is that unless we are
doing something extremely specific to the current record on the form
the correct answer is, "Don't."

[Rick Brandt]

Re: Compact BE while idle... so confused!

Lyle Fairfield wrote:

>but pretty much everyone has a main
>menu (at least I'm assuming so).

>
I don't think so. Some of us, at least one, work exclusively on the
premise that forms are for data and menus are for commands and never
the twain shall meet. My applications open to blank screen with a
custom menu at the top. TTBOMK this is the standard Windows interface.
My guess is that more than more than 50% of all problems here start
with something like, "How can I have a button on a form that does
whatever when I click on it ...?" My position is that unless we are
doing something extremely specific to the current record on the form
the correct answer is, "Don't."

I don't disagree that this is the Windows "standard", but I have one app
that defaults to just a main menu bar, and also provides a user property to
allow for a menu form to be displayed as well. The users almost universally
prefer the menu form and I have had numerous tech support calls from new
users who thought the app was broken because "There's nothing on the screen
when I open it".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

Re: Compact BE while idle... so confused!

John Mishefske <jmishefskeNO@S PAMyahoo.comwro te in
news:2Qeyg.2654 $zg.1978@tornad o.rdc-kc.rr.com:

Set rsPersistant = db.OpenRecordet ("SELECT oneFieldNameHer e FROM
[LinkedTable] WHERE 1=0", dbOpenForwardOn ly)
>
No records will be returned but the connection (and the .LDB file)
will be created.

Er, why open a recordset? Setting a db variable to point to the back
end creates the LDB file, and that's all that's needed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

Re: Compact BE while idle... so confused!


perryche@yahoo. com wrote:

Bri and others,
First, thank you very much for all your input. I will try
them soon. BTW, the reason the form was hidden, was because there was
a little typo there...
>
Set rsPersistant = db.OpenRecordet ("SELECT oneFieldNameHer e FROM
[LinkedTable] WHERE 1=0",
dbOpenForwardOn ly)
>
>
Shouldn't it say "db.OpenRecords et" instead of "db.OpenRecorde t"?

Yup, thats why I labeled it as air code. I was freehand typing vs
cutting and pasting real code. If it wasn't for Intellesense, I'd be
debugging twice as long. :{)

--
Bri