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 |
#21
|
|||
|
|||
Persistanct connections
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 |
#22
|
|||
|
|||
Persistanct connections
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] |
#23
|
|||
|
|||
Persistanct connections
"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 |
#24
|
|||
|
|||
Persistanct connections
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] |
#25
|
|||
|
|||
Persistanct connections
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 |
#26
|
|||
|
|||
Persistanct connections
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 |
#27
|
|||
|
|||
Mr. Kallal & Mr. Fenton: What did I do wrong?
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 |
#28
|
|||
|
|||
Persistanct connections
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/ |
#29
|
|||
|
|||
Persistanct connections
"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/ |
#30
|
|||
|
|||
Persistanct connections
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/ |
Thread Tools | |
Display Modes | |
|
|