Compact BE while idle... so confused!

[perryche]

I have searched the site and probably came across dozens of posts... I
am somewhat confused about the replies... here is my situation, see if
someone can point me to where to go:

I have splitted up the DB to FE and BE, I want to autocompact BE when:
1. all users exited FE (or if not out by certain time of the day and it
is idle (forgot to logout), force them out of it to do a compact)
or
2. Last person out of accessing the BE (FE exited)

Thanks in advance,
Perry

[DFS]

pe******@yahoo.com wrote:

I have searched the site and probably came across dozens of posts... I
am somewhat confused about the replies... here is my situation, see if
someone can point me to where to go:

I have splitted up the DB to FE and BE, I want to autocompact BE when:
1. all users exited FE (or if not out by certain time of the day and
it is idle (forgot to logout), force them out of it to do a compact)
or
2. Last person out of accessing the BE (FE exited)

You'll have to build your own programs and auditing tools to do this.
You'll need to have Access security turned on, or have your own custom
security/login system in place. You'll need to track when each person logs
in and out - checking for the last person logging out will be probably be
done via a hidden form in the FE. When the last person logs out, your
hidden form can start a BE compact operation. Or you can leave a separate
Access or other .exe running and poll the logged in/out table from outside
the FE.

Not sure about forcing someone out of an app. You can probably find other
cdma posts about it.

Thanks in advance,
Perry

[pietlinden]

Not sure about forcing someone out of an app. You can probably find other

cdma posts about it.

Arvin wrote something that does that. KickEmOut
www.datastrat.com

[perryche]

Thank you all for replying. I took pietlin's advice and make it work
pretty well. The only situation is if someone is still in a form,
then, I may not be able to force them out. Thanks again.

Perry

[pietlinden]

pe******@yahoo.com wrote:

Thank you all for replying. I took pietlin's advice and make it work
pretty well. The only situation is if someone is still in a form,
then, I may not be able to force them out. Thanks again.

Perry

Why not? You'd force them out by looping through the front end forms
collection (the open forms) and closing each one. You could be nice
and prompt the user for saving the record or leaving it. The polling
in the frontend of the backend table will tell you when it's time to
boot everyone out.

[Lyle Fairfield]

pi********@hotmail.com wrote:

pe******@yahoo.com wrote:

Thank you all for replying. I took pietlin's advice and make it work
pretty well. The only situation is if someone is still in a form,
then, I may not be able to force them out. Thanks again.

Perry

Why not? You'd force them out by looping through the front end forms
collection (the open forms) and closing each one. You could be nice
and prompt the user for saving the record or leaving it. The polling
in the frontend of the backend table will tell you when it's time to
boot everyone out.

Why force them out? What does this accomplish?

[perryche]

Lyle,
Well, the reason is for routine maintenance if someone else forgot
to logout of database, then, you can not compact it.

Perry

Lyle Fairfield wrote:

pi********@hotmail.com wrote:

pe******@yahoo.com wrote:

Thank you all for replying. I took pietlin's advice and make it work
pretty well. The only situation is if someone is still in a form,
then, I may not be able to force them out. Thanks again.
>
Perry

Why not? You'd force them out by looping through the front end forms
collection (the open forms) and closing each one. You could be nice
and prompt the user for saving the record or leaving it. The polling
in the frontend of the backend table will tell you when it's time to
boot everyone out.

Why force them out? What does this accomplish?

[Lyle Fairfield]

[e-mail] wrote:

Lyle,
Well, the reason is for routine maintenance if someone else forgot
to logout of database, then, you can not compact it.

I have no idea where this idea came from but it is wrong (unless
something has recently changed).

A database (mdb) can be compacted if it can be opened exclusively. If
no user is actively working in the database then it can be opened
exclusively. A user simply having the front end open will not prevent
the backend from being compacted. Given a reasonable number of users,
say 20, there may be many times in a day when the beackend can be
compacted without "kicking" anyone out.

I can run the code from

http://www.ffdba.com/downloads/Compa...ked_Tables.htm

from db1 in which I have linked the tables of northwind.mdb while I
have the db2, also with that tables of northwind.mdb linked, open.
Northwind.mdb is compacted. I can't run it if db2 has a form open which
is editing data from Northwind.mdb.

I run the code on the front end's open which is likely to have the
first user of the day compact the backend, or I run it on demand from a
menu, with a reminder popping up every Friday to remind that it be run.

[perryche]

Lyle, Like you stated, when someone is still in the database, using the
form, I can not compact the BE. In fact, whenever I see a .ldb, then I
can not compact it. Unless an user exit the database, I can not
compact it. This has been my experience, but tell me if I am wrong.
How can I ensure everyone is out so that I can compact the BE if not
force them out? What if someone is in the form and forgot to exit and
left for the day, what option do I have then?

Thanks again,
Perry

[Lyle Fairfield]

pe******@yahoo.com wrote:

Lyle, Like you stated, when someone is still in the database, using the
form, I can not compact the BE. In fact, whenever I see a .ldb, then I
can not compact it. Unless an user exit the database, I can not
compact it. This has been my experience, but tell me if I am wrong.
How can I ensure everyone is out so that I can compact the BE if not
force them out? What if someone is in the form and forgot to exit and
left for the day, what option do I have then?

Thanks again,
Perry

I'm sorry but I don't know what to say. I think my previous post
exhausted my input on this matter.

[John Mishefske]

pe******@yahoo.com wrote:

Lyle, Like you stated, when someone is still in the database, using the
form, I can not compact the BE. In fact, whenever I see a .ldb, then I
can not compact it. Unless an user exit the database, I can not
compact it. This has been my experience, but tell me if I am wrong.
How can I ensure everyone is out so that I can compact the BE if not
force them out? What if someone is in the form and forgot to exit and
left for the day, what option do I have then?

The user doesn't have to necessarily exit the front-end app; the front-end app just has to
close all its connections to the back-end.

So Lyle's code checks if there are any open forms or reports under the assumption that
those forms/reports would be "bound" objects - thus holding an open connection to the data
store - the backend .mdb file - and exits the proc after informing the user.

As the developer you should know which forms/reports are bound objects and close them
before attempting this code. Without any bound objects (and nothing else holding an open
connection to the BE like a Public variable), the .ldb file disappears (also assuming no
other users have an open connection to the BE).

The proc still fails if another user holds a connection (i.e. the .ldb file exists) and
that is appropriate. You are attempting to find a particular condition where no users are
holding an open connection (and thus the .ldb locking file doesn't exist).

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.)

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

[Lyle Fairfield]

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.

[John Mishefske]

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.

Yes but to be honest I don't think it really makes a noticeable difference although lots
of folks that I've recommended this to claim it "saved them". I'm sure you are correct;
with 100 Mb networks, switches and faster PCs than we used 10 years ago its hard to
believe this has significant performance impact.

My problems usually seem more related to either permissions or issues with 8.3 naming or
path length of the BE or some SMB/NetBEUI registry setting.

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

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

I can run the code from

http://www.ffdba.com/downloads/Compa...ked_Tables.htm

from db1 in which I have linked the tables of northwind.mdb while
I have the db2, also with that tables of northwind.mdb linked,
open. Northwind.mdb is compacted. I can't run it if db2 has a form
open which is editing data from Northwind.mdb.

The form doesn't have to be editing. It only has to display data
from a table.

Anyone who is using a recordset to maintain a persistent connection
to the back end in order to improve performance of opening forms is
not going to ever be able to get an exclusive lock on the back end.

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

[David W. Fenton]

John Mishefske <jm**********@SPAMyahoo.comwrote in
news:dA**************@tornado.rdc-kc.rr.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.)

There is a lot of overhead in the creation of the LDB file. In an
app with a small number of users, where it is often likely that a
user will be the first to open the back end, the persistent
connection can be very helpful, since otherwise, the LDB will be
repeatedly deleted and recreated as bound forms are closed and
opened.

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

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11*********************@75g2000cwc.googlegrou ps.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]

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]

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]

pe******@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("LinkedTable")
End Sub

Sub On_Close
rsPersistant.Close
Set rsPersistant = Nothing
End Sub

--
Bri

[perryche]

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]

Bri wrote:

pe******@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("LinkedTable")
End Sub

Sub On_Close
rsPersistant.Close
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 oneFieldNameHere FROM [LinkedTable] WHERE 1=0",
dbOpenForwardOnly)

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]

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]

Pe******@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]

John Mishefske wrote:

Bri wrote:

> pe******@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("LinkedTable")
End Sub

Sub On_Close
rsPersistant.Close
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 oneFieldNameHere FROM
[LinkedTable] WHERE 1=0", dbOpenForwardOnly)

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]

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]

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 oneFieldNameHere FROM
[LinkedTable] WHERE 1=0",
dbOpenForwardOnly)
Shouldn't it say "db.OpenRecordset" instead of "db.OpenRecordet"?

Thanks again, anyway.

Perry

[Lyle Fairfield]

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]

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]

John Mishefske <jm**********@SPAMyahoo.comwrote in
news:2Q****************@tornado.rdc-kc.rr.com:

Set rsPersistant = db.OpenRecordet("SELECT oneFieldNameHere FROM
[LinkedTable] WHERE 1=0", dbOpenForwardOnly)

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]

pe******@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 oneFieldNameHere FROM
[LinkedTable] WHERE 1=0",
dbOpenForwardOnly)
Shouldn't it say "db.OpenRecordset" instead of "db.OpenRecordet"?

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

[Bri]

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."

To each his own. The main idea to get from my original premise is that
the code to hold open the recordset needs to be in a form that can stay
open for the duration of the application session. Whether it a main menu
or a hidden splash screen is secondary to the issue.

I, like Rick, agree with you that the 'Windows Standard' is to use the
Menus rather than a main menu (or switchboard). In apps like Word,
Excel, etc. this makes sense to the user as they then open a Document.
In Access it is similar in reality, but not as similar in perspective to
the user. I have had similar experience to Rick's in that the users seem
to prefer the menu form to the menu bar and since they are the ones I'm
writing the app for, I try to accommodate them.

--
Bri

[polite person]

On 28 Jul 2006 06:31:33 -0700, "Lyle Fairfield" <ly***********@aim.comwrote:

>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."

Nowadays there are more and more toolbars, I believe the plan is to remove menus altogether. When
overlapping windows finally disappear the original desktop concept will have vanished together with
"modeless"interaction.

[David W. Fenton]

Bri <no*@here.comwrote in news:dtryg.254275$Mn5.58202@pd7tw3no:

To each his own. The main idea to get from my original premise is
that the code to hold open the recordset needs to be in a form
that can stay open for the duration of the application session.
Whether it a main menu or a hidden splash screen is secondary to
the issue.

Could you use a class module and have the class's terminate event
clean up what needs to be cleaned up? Isn't that kind of what the
OnClose event of a form is, in a sense?

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

[Bri]

David W. Fenton wrote:

Bri <no*@here.comwrote in news:dtryg.254275$Mn5.58202@pd7tw3no:

>>To each his own. The main idea to get from my original premise is
that the code to hold open the recordset needs to be in a form
that can stay open for the duration of the application session.
Whether it a main menu or a hidden splash screen is secondary to
the issue.

Could you use a class module and have the class's terminate event
clean up what needs to be cleaned up? Isn't that kind of what the
OnClose event of a form is, in a sense?

I suppose so, but I have a lot less experience with classes than you do,
so it isn't the first thing I think of as a solution to a problem. I
suppose I should probably use classes for a lot of other things I do,
even if its just to get more familiar with them.

--
Bri

[Bri]

David W. Fenton wrote:

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.

Is it? That would be a lot simpler to deal with. The Property code alone
would then do the trick. OK, just did a test and you are corect, the LDB
file is created when the db variable is set and destroyed when it is set
to Nothing. Cool, I like learning stuff like this that simplify things.
Thanks for the tip.

--
Bri

[David W. Fenton]

Bri <no*@here.comwrote in news:O5Nyg.264003$Mn5.155697@pd7tw3no:

David W. Fenton wrote:

>Bri <no*@here.comwrote in news:dtryg.254275$Mn5.58202@pd7tw3no:

>>>To each his own. The main idea to get from my original premise is
that the code to hold open the recordset needs to be in a form
that can stay open for the duration of the application session.
Whether it a main menu or a hidden splash screen is secondary to
the issue.

Could you use a class module and have the class's terminate event
clean up what needs to be cleaned up? Isn't that kind of what the
OnClose event of a form is, in a sense?

I suppose so, but I have a lot less experience with classes than
you do, so it isn't the first thing I think of as a solution to a
problem. I suppose I should probably use classes for a lot of
other things I do, even if its just to get more familiar with
them.

I'm suggesting it only as an alternative to the hidden form.

But I'm not certain it would be as reliable as a hidden form, since
I suspect the hidden form's OnClose event is going to execute more
quickly than an instantiated class module's Terminate event.

Any thoughts from anyone else?

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

[David W. Fenton]

Bri <no*@here.comwrote in news:neNyg.263109$iF6.169594@pd7tw2no:

David W. Fenton wrote:

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

Is it? That would be a lot simpler to deal with. The Property code
alone would then do the trick. OK, just did a test and you are
corect, the LDB file is created when the db variable is set and
destroyed when it is set to Nothing. Cool, I like learning stuff
like this that simplify things. Thanks for the tip.

No problem. It has always confused me why the recommendation for
this has always used a recordset.

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

[perryche]

David or Bri,
So, while I thought I had Bri's code down, now David
suggested this: "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." So, exactly what need to be done then? Please advice.

Perry

[Lyle Fairfield]

pe******@yahoo.com wrote in news:1154300852.880396.84900@
75g2000cwc.googlegroups.com:

David or Bri,
So, while I thought I had Bri's code down, now David
suggested this: "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." So, exactly what need to be done then? Please advice.

Did you try doing nothing?

Did you try doing any of the methods suggested? Do they make opening forms,
whatever, noticeably faster? I have never seen this. Obviously others have
and do. But there's no point in doing anything if speed is not affected. No
one could say this is good programming. It's a crutch to overcome a very
poor characteristic of Access/Jet.

Here's what the second edition of the Microsoft® Jet Database Engine
Programmer’s Guide has to say about the issue. You might want to read it
carefully, note the similarities and differences between what it says and
what has been stated in this thread and test a number of things suggested
here and there.

*****
"Implement Persistent Connections with Linked Tables
Microsoft Jet uses the locking information (.ldb) file to track which users
have the database open and to track the locking of pages in the .mdb file.
In Microsoft Jet 3.0 and 3.5, the .ldb file is deleted when the last user
closes the database. This is done to prevent accumulation of .ldb files
when replication is being used. However, in situations where only one user
is accessing a linked table, particularly in a looping routine, this causes
a significant degradation in performance. The reason for this is that
linked tables do not keep persistent connections to the database where
their data resides. This causes additional disk I/O to delete, re-create,
and establish locks on the locking information file.

To prevent this, design your application so that it maintains a persistent
connection to any linked Microsoft Jet tables. To do this, declare public
recordset variables for each linked table your application uses, use the
OpenRecordset method to open those tables at the beginning of your
application, and only close these recordset variables when your application
itself closes. Maintaining persistent connections to linked tables can
improve performance significantly because it prevents Microsoft Jet from
constantly deleting, creating, and obtaining locking information from the
other database’s locking information file."
*****

I may say that I have never been able to identify any improvement in speed
through maintaining a persistent connection to linked tables. Perhaps that
is because I seldom or never access a linked table in a looping routine;
perhaps it is because I have been fortunate enough to have always worked
with stable, fast networks; perhaps it is because the nature of my
applications has always been small, specialized, calculation intensive and
never rosters, clients or large populations in general.

--
Lyle Fairfield

[David W. Fenton]

pe******@yahoo.com wrote in
news:11*********************@75g2000cwc.googlegrou ps.com:

So, while I thought I had Bri's code down, now David
suggested this: "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." So, exactly what need to be done then? Please
advice.

Well, you can find out the back end this way:

Mid(CurrentDB.TableDefs("[one of your linked tables]").Connect,11)

and then use that to open the back end database:

Dim dbBackEnd As DAO.Database

Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)

where you've assigned strBackEnd a value corresponding to the back
end returned by the Mid() command above.

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

[perryche]

Lyle,
I did try Bri's method and it works fine, but again, I did
not notice any speed improvement. I think it should be at least the
same speed.

David,

Well, you can find out the back end this way:

What do you mean by "find out"?

Mid(CurrentDB.TableDefs("[one of your linked tables]").Connect,11)

Where do this code go? Module? (I am very green in coding)

and then use that to open the back end database:

Dim dbBackEnd As DAO.Database

Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)

where you've assigned strBackEnd a value corresponding to the back
end returned by the Mid() command above.

I suppose then add these code to the onopenevent of the mainmenu?
So, would this then help with faster open and access? as compared to
just linking the mainmenu to a simple 1-recorded table in the BE?
What's the advantage?

Sincerely,
Perry

[Lyle Fairfield]

pe******@yahoo.com wrote:

Lyle,
I did try Bri's method and it works fine, but again, I did
not notice any speed improvement. I think it should be at least the
same speed.

The same speed as what?

So, would this then help with faster open and access? as compared to
just linking the mainmenu to a simple 1-recorded table in the BE?
What's the advantage?

That was my question. My point was/is that I have never found any
advantage whatever. If you find an advantage you should do it. If not,
then why would you do it?

[David W. Fenton]

pe******@yahoo.com wrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

>Well, you can find out the back end this way:

What do you mean by "find out"?

Well, you want to open a connection to the back end data file. To
find out what that data file is, you run the following command:

>Mid(CurrentDB.TableDefs("[one of your linked
tables]").Connect,11)

Where do this code go? Module? (I am very green in coding)

Yes, it would be in VBA. I assumed that you could at least do the
basics, as you seemed satisfied with the recordset version of the
same thing.

>and then use that to open the back end database:

Dim dbBackEnd As DAO.Database

Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)

where you've assigned strBackEnd a value corresponding to the
back end returned by the Mid() command above.

I suppose then add these code to the onopenevent of the mainmenu?

Or, create a standalone module with a function to do this and call
that from the OnOpen.

So, would this then help with faster open and access? as compared
to just linking the mainmenu to a simple 1-recorded table in the
BE?

I don't know that it would be faster.

What's the advantage?

Less code.

This is how it would be implemented:

In your main menu's module:

Dim dbBackEnd As DAO.Database

In the OnOpen:

Dim strBackEnd As String

strBackEnd = Mid(CurrentDB.TableDefs("[table]").Connect,11)
Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)

In the OnClose:

If Not (dbBackEnd Is Nothing) Then
dbBackEnd.Close
Set dbBackEnd = Nothing
End If

That's it.

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

[perryche]

Thank you David and the rest who charms in this post. I learned a lot.
I really appreciate all your input. And no, in am very new to VB and
yes, I am able to manipulate the code enough to get things working from
time to time. Thanks again.

Perry