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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|