A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old April 9th, 2007, 12:16 AM posted to microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default Persistanct connections

PS. I forgot to mention that if you happen to have a bound switchboard form,
then opening the table minimized, as Albert suggested, would likely not show
any performance improvement, because the switchboard form would already be
maintaining the persistent connection.

Note: The Switchboard wizard in Access creates a bound switchboard form.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
  #12  
Old April 9th, 2007, 12:45 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Persistanct connections

On Sun, 8 Apr 2007 16:16:00 -0700, Tom Wickerath AOS168b AT comcast DOT net
wrote:

Note: The Switchboard wizard in Access creates a bound switchboard form.


Bound to a *local*, not a backend table, though; isn't it? Or is this
different in 2007?

John W. Vinson [MVP]
  #13  
Old April 9th, 2007, 01:06 AM posted to microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default Persistanct connections

Hi John,

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. You would have to specifically import the table back into the FE
database, and there would be very little benefit of doing so--this table is
typically so small that a table scan (ie. transferring all records over the
network wire from this table) is really no big deal.

On the other hand, if you use the Switchboard Manager wizard *after*
splitting your database, then, yes, it would create a local table. I would
think that most of the development work would be done before splitting. In
that case, you'd want to import the new Switchboard Items table from your FE
database into your BE database, delete the copy in the FE database, and then
establish a linked table as usual.


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:

Bound to a *local*, not a backend table, though; isn't it? Or is this
different in 2007?

John W. Vinson [MVP]

  #14  
Old April 9th, 2007, 01:43 AM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Persistanct connections

"Albert D. Kallal" wrote in
:

However, as a general rule, I always do create a persistent
connection, as on some networks..it don't make a difference, but
on others...it makes a HUGE difference.


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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old April 9th, 2007, 01:48 AM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Persistanct connections

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

PS. I forgot to mention that if you happen to have a bound
switchboard form, then opening the table minimized, as Albert
suggested, would likely not show any performance improvement,
because the switchboard form would already be maintaining the
persistent connection.

Note: The Switchboard wizard in Access creates a bound switchboard
form.


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

So, for anyone who has used the default Switchoard wizard setup, and
not moved the Switchboard table to the back end, opening the
switchboard will *not* create an LDB for the back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #16  
Old April 9th, 2007, 01:49 AM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Persistanct connections

Vladimír Cvajniga wrote in
:

#1 in NOT being helpful...

I thougth all MVPs were professionals. Unfortunatelly, some of
them don't seem to... :-(


I think you're a very difficult person to help.

At this point, I don't even know what your problem is because you've
confused the issue by pointing to a different thread.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #17  
Old April 9th, 2007, 01:59 AM posted to microsoft.public.access
VladimĂ­r Cvajniga[_2_]
external usenet poster
 
Posts: 23
Default Persistanct connections

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

"Tom Wickerath" AOS168b AT comcast DOT net pĂ*še v diskusnĂ*m pĹ™Ă*spÄ›vku
...
VladimĂ*r -

I'm sorry to read that you feel this way. I have read the entire thread
and,
personally, I think Albert has been very helpful to you. It seems to me
like
you might be making this issue more difficult than it need be. Of course,
I
cannot talk about the Czech version of Access versus the English version,
as
I have no way of testing your version.

The easiest way to verify a persistent connection is to make sure that you
see a .ldb file created for the back-end (BE) database, as soon as you
open
your front-end (FE) database. Keep a small window open, using Windows
Explorer, so that you can keep an eye on the .ldb file. If your BE is on
a
shared network, you need to do this verification test when you know that
no
other users have the BE file open. Also, do not press the shift key, when
opening your FE database, because you do not want to bypass an Autoexec
macro
or any startup code. No matter what queries, forms or reports that you
close,
you should never witness the .ldb file for the BE database being deleted,
as
long as you have your FE database open. It's really that simple!

In June, 2006, I gave a presentation to the Pacific Northwest Access
Developer's Group titled "Analyzing Your Database with JetShowPlan". A
part
of this presentation included timing tests to run queries on a split
Access
application, with and without a persistant connection. I have included the
applicable parts of this presentation in a .zip file, which I invite you
to
download and take a look at:

http://home.comcast.net/~tutorme2/sa...ng_results.zip


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:

#1 in NOT being helpful...

I thougth all MVPs were professionals. Unfortunatelly, some of them don't
seem to... :-(

V.


  #18  
Old April 9th, 2007, 02:04 AM posted to microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default Persistanct connections

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.


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

"David W. Fenton" wrote:

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

PS. I forgot to mention that if you happen to have a bound
switchboard form, then opening the table minimized, as Albert
suggested, would likely not show any performance improvement,
because the switchboard form would already be maintaining the
persistent connection.

Note: The Switchboard wizard in Access creates a bound switchboard
form.


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

So, for anyone who has used the default Switchoard wizard setup, and
not moved the Switchboard table to the back end, opening the
switchboard will *not* create an LDB for the back end.

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

  #19  
Old April 9th, 2007, 02:21 AM posted to microsoft.public.access
Vladimír Cvajniga[_2_]
external usenet poster
 
Posts: 82
Default Persistanct connections

Everybody is a complicated person. ;-)

This was the problem (a respond to Tony Toews input - but no one else
answered) (BTW, some people do not respond in this newsgroup and it's quite
difficult to find all posts...):

It didn't seem to have an effect. Instead, it took very long to run the
program from another machine if different station has run it as well.
I had a persistent connection based on an empty recordset. Some programmers
say I might have create a persistent connection just by initializing db
variable pointing to the back end. I tried persistent connection according
to http://www.granite.ab.ca/access/perf...dblocking.htm:
Public rsAlwaysOpen As Recordset

Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub


Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("DummyTable")
End Sub


I didn't try David Fenton's Global database connection as described on the
web page. Here are the steps how I did it:


StartUp Form = frm_Main
On Form_Open
1) First action: check connections to shared BE database within a "For Each
tbl In CurrentDb.TableDefs" cycle:
If any connection failed (ie. BE MDB couldn't be found, eg. after a FE
upgrade) there was "Exit For", and a procedure to re-create connections was
run.
2) Set rsVzdy = CurrentDb.OpenRecordset("VZDY") 'open persistent connection
Table VZDY was a table in main shared BE MDB.
3) Initiate main MDB's parameter tables (if run for the first time).
4) Initiate local BE MDBs.
5) Initiate frm_Main (dashboard). There are many actions on shared BE MDB,
eg. reading from parameter tables, etc.


On Form_Close
1) Write some settings to Win registry and to INI-file.
2) Set rsVzdy = Nothing 'close persistent connection
3) Write to a log file (pure TXT file).
4) Compress local BD MDBs.
5) Last user of main BE MDB: backup main MDB.


It's all A97, Czech version. I believe there are no (Jet) DB issues
associated with A97 language versions.
According to some comments (Albert Kallal, David Fenton, ...) I did
something wrong, but I don't know what. :-/


Pls, respond if you have any idea. At the moment we don't use persistent
connection at all. Instead we do some registry tricks, see
http://support.microsoft.com/kb/150384/ and the program performance is
superb! What do you think: should I go back and try persistent connection
once again? ;-)



"David W. Fenton" píse v diskusním príspevku
. 1...
Vladimír Cvajniga wrote in
:

#1 in NOT being helpful...

I thougth all MVPs were professionals. Unfortunatelly, some of
them don't seem to... :-(


I think you're a very difficult person to help.

At this point, I don't even know what your problem is because you've
confused the issue by pointing to a different thread.

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


  #20  
Old April 9th, 2007, 02:34 AM posted to microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default Persistanct connections

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.