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

  • Thread starter Vladimír Cvajniga
  • Start date
A

Albert D. Kallal

For a post that is 5 days old, it certainly not considered rude to start
another post/thread..

I not sure why my name is mentioned here....as I don't see my participation
in that previous thread....

To test a persistent connection, simply open up your front end, and then
open ANY table (use the tables tab). Simply double click on any linked
tabled. So, you are now viewing data in a linked table.

Now, minimize the table....

Now, launch your applications main form..and see if the application runs
faster. If it runs faster, then the persistent connection is helping, if the
application does not improve, then the persistent connection did not help.
Remember, a persist connection is simply to FORCE and KEEP open the back end
database AT ALL TIMES.

So, if you simply launch the front end, and then simply click on a linked
table in the table tab to open and view the data in a table (that is
linked), you have a persistent connection. DO NOT close this table...but
simply minimize it..and then see if performance improve.

If the performance does improve, then you can consider writing some code in
your start-up code that forces open a persistent connection. however, you
don't need to write, or test, or build some code...just click on a table to
open it (you do this in the front end). Keep the table open..and then simply
then try your main form etc to see if it runs faster.

You don't have to write code to test this you ONLY need some code AFTER you
tested this.......

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

Vladimír Cvajniga

Hello,

TYVM for your respond. I've mentioned you because you have posted something
about persistent connection (PeCo) in one of previous threads:
http://www.developerfood.com/re-spl...57ea-2ff6-4154-9ab7-43802c718db9/article.aspx.

Pls, could you check up my scenario and tell me if there was something
wrong. In that case, PeCo didn't help at all and that's why I thouhgt that
PeCo was just a myth. But after that discussion I googled the web and went
to my old code... and tried to check up what was wrong. IMO, my scenario
seems to be OK.

Long time ago I've "discovered" that if I ran Word or Excel and then opened
an A97 app, Access performed very slow until I closed the other MSO apps.
I'm not sure if it's the same in US (EN) release of Access - I use Czech
version. There may be some unknown language issues... as there's at least
one known "language issue" in A2002, see
http://groups.google.co.uk/group/mi...5eb7b45e442/9825ddb44d18b76b#9825ddb44d18b76b.

At the moment I'm trying to collect as most information on PeCo as I can. I
appreciate any comments.

TIA

Vlado
 
A

Albert D. Kallal

Vladimír Cvajniga said:
IMO, with persistent connection it's quite qifficult to check up if MDB is
open or not, see
http://groups.google.co.uk/group/mi...estion+Cvajniga&rnum=1&hl=en#9263d55d37251713.
Click to expand...

Why do we have to check anything? Just click on a table to open it..and then
minimize it...you are done!!!!

Nothing more complex to test here.......

If you find the above helps, then of course you can write some code to keep
the back end open, but you don't actually have to write any code to test
this.

I don't think it is a huge complex problem to double click on a table, and
then minimize it (if that is too hard..then time for a coffee break!!).
 
V

Vladimír Cvajniga

#1 in NOT being helpful...

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

V.
 
V

Vladimír Cvajniga

#1 in NOT being helpful...

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

V.
 
T

Tom Wickerath

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/samples/query_timing_results.zip


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

Tom Wickerath

J

John W. Vinson

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

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

John W. Vinson [MVP]
 
T

Tom Wickerath

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/expert_contributors.html
__________________________________________
 
D

David W. Fenton

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.
Click to expand...

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

David W. Fenton

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.
Click to expand...

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

David W. Fenton

#1 in NOT being helpful...

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

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

Vladimír Cvajniga

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
 
T

Tom Wickerath

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

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/expert_contributors.html
__________________________________________
 
V

Vladimír Cvajniga

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/performanceldblocking.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? ;-)
 
T

Tom Wickerath

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?
Click to expand...

I have not done so.


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A2002 bug: Can't set RecordsetType 5
A2002: Form_Timer pops up the form 4
Access 2000 FAILS to relink ODBC tables with correct credentials - PART2 9
"Access 2002 Desktop Developer's Handbook" chapter 7 file - does not work w/A2003 9
You entered an expression that has an invalid reference to the property Form/Report 1
Count unique items in pivot table results 5
Successful Emailing from an Access program without using Outlook. 1
Intentional HREF to a .lnk file, how do I lose the IE Security Warning? 0

Top