Mr. Kallal & Mr. Fenton: What did I do wrong?

Thx, Tony. I'll bear it in mind when I'll test it again. Not going to test
anything tonight... at 3:45 am...
Vlado

P.S. Happy Easter to everybody!

"Tom Wickerath" AOS168b AT comcast DOT net pÃ*Å¡e v diskusnÃ*m pÅ™Ã*spÄ›vku
...
Hi VladimÃ*r,

To be honest, I've never tried opening a persistent connection on an empty
recordset. I always have one record (the version number) present. I don't
know if this would make any difference, but it might be worth a quick
test.


Has anyone tried persistent connection on a P2P?

I have not done so.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"VladimÃ*r Cvajniga" wrote:

TYVM for your respond, Tom.

I apologise Mr. Kallal if I did something really unfair...

It's very important for me to know if I did something wrong in my
scenario
(in opening/closing code). Because when I'll try the persistent
connection
again I'll do it same way as I did it before. My experience is that
there's
a performance problem on a start up initialization on anonther machine if
a
BE MDB is already open. Ie. we have already tested this situation. We
tried
persistent connection with an empty recordset which was not used for
anything else so there should be no locking except when we open the same
empty recordset form different machine. As to performance there was a
difference between two versions, see 2a):
1) persistant connection (empty recordset): any startup on different
machine
was slow
2) non-persistent connection:
a) if the first user didn't open BE MDB, ie. he/she just run an
unbound
main (dashboard) form, the second (third, etc.) user's startup was fresh
(no
perfomance problem)
b) if the first user opened BE MDB, all other users were slowed down
on
a startup (same as with persistent connection)

That's why I wanted to "clear" the problem step by step. Mr. Kallal has
mentioned that there may be differences between various types of
networks -
it's an interesting information but I miss details: is it a LAN speed or
LAN
type or something else what I should take care about?

Secondly, with persistent connection I'm affraid that there's no exact
way
how to determine if BE MDB is really open. I may need this information
when
I want to compress/repair BE MDB from FE.

As I have already mentioned: we use different way to speed up A97's
performance. AFAIK, there are no known issues on this technique so far.

I have downloaded query_timing_results.zip. TYVM for letting me see the
results. All I can say is: I'm really surprised! I think I will try
persistent connection once again on my home P2P network. And I'll try
David
Fenton's technique, too, ie. use db variable instead of a recordset. Has
anyone tried persistent connection on a P2P?

TIA

Vlado

On Sun, 8 Apr 2007 17:06:04 -0700, Tom Wickerath AOS168b AT comcast DOT net
wrote:

Yes, the Switchboard Manager wizard does create a local table. However, this
table is moved to the back-end file as soon as you use the database splitter
wizard.

In my experience, the Switchboard then immediately stops working (because the
switchboard wizard code uses Seek and expects the table to be local). Only if
you re-import the Switchboard table into the frontend can you use the
switchboard!

One reason I tend to avoid the wizard generated switchboard.

John W. Vinson [MVP]

"Vladimír Cvajniga" wrote

I have downloaded query_timing_results.zip. TYVM
for letting me see the results. All I can say is: I'm really
surprised! I think I will try persistent connection once
again on my home P2P network. And I'll try David
Fenton's technique, too, ie. use db variable instead
of a recordset. Has anyone tried persistent connection
on a P2P?

The difference in the limit of number of connections between machines on a
P2P versus a server may make any findings in the former not useful in
predicting performance in the latter.

Yes, you can use linked tables between machines in a P2P. But, most of the
performance information and suggestions you see here and in references
applies to a server on a LAN.

Larry Linson
Microsoft Access MVP

Hi John,

I just finished testing three new switchboards, created using Access 2000,
2002 and 2003. In each case, the Switchboard Items table was a linked table.
It works fine in all three versions.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"John W. Vinson" wrote:

On Sun, 8 Apr 2007 17:06:04 -0700, Tom Wickerath AOS168b AT comcast DOT net
wrote:

Yes, the Switchboard Manager wizard does create a local table. However, this
table is moved to the back-end file as soon as you use the database splitter
wizard.

In my experience, the Switchboard then immediately stops working (because the
switchboard wizard code uses Seek and expects the table to be local). Only if
you re-import the Switchboard table into the frontend can you use the
switchboard!

One reason I tend to avoid the wizard generated switchboard.

John W. Vinson [MVP]

I think you making this more complex then you need.

As the others said if the ldb locking file remains, then you are ok...

The example posted in that thread was:

Public Function fncIsOpenMDB(ByVal strPath As String) As Boolean
Dim db As DAO.Database

On Error Resume Next
fncIsOpenMDB = False
Set db = DBEngine.OpenDatabase(strPath, True)
fncIsOpenMDB = (Err 0)
db.Close
Set db = Nothing


End Function


The above is NOT good, because the "db" variable goes out of scope right
away. So, the only real issue to be careful here is to make sure the
database variable (or record set variable) you use is a global define
variable in a standard code module.

You don't repeat call over and over the code to open the back end...

You simply ensure that the connection persists.

That is it.....

There is nothing more, nothing less on this subject. As to which setups this
improves performance the most? I don't believe that information is known.

On some systems, creating and deleting the file seems to take a LONG time
(virus software, large path names....etc can all effect this).

The ONLY thing you can be sure of is that you reduce the time it takes to
create the ldb file...you want to eliminate the long delay.

The only thing you need to be sure of is that the variable used to open the
recordset (or database var) is global..and keeps the connection alive.....

As to if this works better on a wan, or lan, or peer-to-peer, it really not
an issue. It usually helps in all cases. the only thing you can do is try
it!!!

If you find a case that were using the persistent connection slows things
down, then sure..share this information with us..it always appreciated when
we can learn something new....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Has anyone tested the difference between opening a recordset on a
local linked table and simply initializing a db variable pointing to
the back-end database? Both create the MDB, but it seems to me that
the latter is more efficient.

I certainly keeping my fingers crossed there's not a difference. However,
someone likely should check!!!

I always opened a table, but your suggestion to use a db variable is a
*much* nicer solution (as it don't care about a particular table name). So,
I actually changed my approach based on your suggestion.......

I will test the two when I have a chance. (I would bet 96% that there no
difference, but you never know...I seen stranger things!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

To see if keeping a persistent recordset open might have the desired affect,
you can simply open a small linked table and keep it open while you run the
problematic part of your application. It should speed up the application
considerably. If it doesn't, that's not your problem.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Vladimír Cvajniga" wrote in message
...
Pls, could you check up
http://groups.google.co.uk/group/mic...2fcbeceb22ac0?

According to your comments I must have done something wrong, but I don't
know what. Pls, answer in this newsgroup.

TIA

Vlado

John W. Vinson wrote in
:

On Sun, 8 Apr 2007 17:06:04 -0700, Tom Wickerath AOS168b AT
comcast DOT net wrote:

Yes, the Switchboard Manager wizard does create a local table.
However, this table is moved to the back-end file as soon as you
use the database splitter wizard.

In my experience, the Switchboard then immediately stops working
(because the switchboard wizard code uses Seek and expects the
table to be local). Only if you re-import the Switchboard table
into the frontend can you use the switchboard!

From A2K on, the Switchboard wizard writes ADO code, so it can't
possibly be using SEEK.

One reason I tend to avoid the wizard generated switchboard.

I do believe it breaks for some reason when you move it to the back
end.

But the switchboard table doesn't *belong* in the back end. Take
this scenario:

1. you create a new report in your development copy.

2. you add it to the switchboard.

If the switchboard table is in the back end, users will see the
choice to print the report, but won't be able to do so, as they
don't have the updated copy of the front end.

For that reason, the switchboard belongs in the front end because it
is connected to the version of the front end you are using, not to
the data. It is a user interface object and the data store for it
belongs with the UI.

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

"Albert D. Kallal" wrote in
:

Has anyone tested the difference between opening a recordset on a
local linked table and simply initializing a db variable pointing
to the back-end database? Both create the MDB, but it seems to me
that the latter is more efficient.

I certainly keeping my fingers crossed there's not a difference.
However, someone likely should check!!!

I always opened a table, but your suggestion to use a db variable
is a *much* nicer solution (as it don't care about a particular
table name). So, I actually changed my approach based on your
suggestion.......

I will test the two when I have a chance. (I would bet 96% that
there no difference, but you never know...I seen stranger
things!).

If the difference truly is the creation of the LDB file, then there
oughtn't be a difference. If there's something else involved (e.g.,
some data manipulation internal to the LDB file), then there might
be a difference.

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

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

In the front end. Which is where it belongs (as a switchboard
form is likely to be specific to a particular front-end version).

I know that there are people who write different versions of a
Front-end application targeted for different groups of users (for
example, managers vs. regular grunts). I do not follow this
practice. Instead, I create one FE for all users, and use the
appropriate VBA code to make certain functionality visible to
selected users. Therefore, if I were to use the Switchboard
Manager [which I absolutely abhor--my switchboard forms are
unbound], I would keep the Switchboard Items table in the BE
database. In that case, I might need to add a field or two to the
Switchboard Items table for controlling when a particular item was
visible to a given user--not really sure, since I haven't
travelled down that road.

What about a new version? How do you coordinate adding a new menu
choice to the switchboard with the creation of new features in the
front end? For instance, you create a report in your development
front end and add a menu choice to the switchboard. If the
switchboard table is in the back end, then all your users will now
see the new menu choice but won't have the report in the front ends
yet.

The switchboard is part of the user interface. Its data stroe
belongs in the front end because it applies to user interface
functionality in the front end.

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