 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / July 2006Split Databases - Best Practices?
I'm starting this thread to pull together related questions and to check my understanding. Please debate apparent misconceptions. Do you agree that these are best practices? 1. Use JET OLE DB when connecting to BackEnd (BE) MDB file. Reason - thread safety. a. ODBC Table Links should be updated using DNS-less code like: adoTbl.Properties("Jet OLEDB:Link Datasource") = strPath & "myBackEnd.mdb" Reason - Ensures OLE DB and saves work when deploying frontends (FE). b. ADO should be used for all DIRECT BE connections. Reason - DAO does not support OLE DB. 2. DAO should be used when working within the FE (i.e. querying table links). Reason - It's powerful. The default recordsets for bound forms are DAO objects. 3. Maintain a Persistent Connection to BE. Reason - Avoids locking problems/delays. 4. Use 8.3 file name conventions in the path to the BE and use mapped drives vice UNC. Long names should be converted when referencing. See: http://support.microsoft.com/default.aspx?scid=kb;en-us;889588 5. Keep the BE as close as possible to the root of the shared drive. Reason - Speed. 6. The Seek method should only be called against a direct BE connection. If querying a table link, FindFirst (DAO) or Find (ADO) must be used instead. Reason - You can't open table-type recordsets from a table link. 7. Set "Default record locking" to "Edited record." Reason - Helps prevent corruption. 8. The FE should be copied to each workstation (not shared). Reason - Sharing defeats the purpose of splitting. 9. Reference the latest DAO and ADO libraries (must be present on ALL workstations). 10. Use Access 2000 at a minimum, but 2003 is preferred. Reason - 2000 is known to be buggy on a network. >3. Maintain a Persistent Connection to BE. > Reason - Avoids locking problems/delays. Biggest reason is much better performance when multiple users are on back end. Tony  SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Seems to me point 4 should really 4a, 4b and 4c >4. use mapped drives vice UNC. What do you mean by this? Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Oh, now I can't find my reference. It said that using "Z:\MyShare\db1.mdb" was better than "\\MyServer\MyShare\db1.mdb" (UNC). Better for performance maybe, but it relies on all FE users having Z:\ mapped. Scripts can do that, but I would say the applicability of this practice depends on your environment. I have no idea as to the performance impact. The info on long names is probably as important, and they (Microsoft) used a UNC in their example. - Tony M. >Oh, now I can't find my reference. It said that using >"Z:\MyShare\db1.mdb" was better than "\\MyServer\MyShare\db1.mdb" [quoted text clipped - 3 lines] >idea as to the performance impact. The info on long names is probably >as important, and they (Microsoft) used a UNC in their example. - Agreed that shorter paths are better than long paths for performance reasons. I"ve seen postings and/or received emails from folks who've said this made a huge difference when the paths were particularly deep. But UNC vs drive letters is a matter of preference in your environment. Both have the advantages vs disadvantages. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Windows more-or-less creates mapped drives for shares: that's what you see in 'my network connections'. So the performance differences are probably non-existent. I use the windows api to convert file paths to short in my re-link code. I don't make suggestions to the users about the use of UNC or mapped drive letters. I've really only had a problem with file location on Novell networks, and that apparently is still a problem - but only when we switched the client from A97 to A2000 (jet 3.5 to jet 4.0) (david) > Oh, now I can't find my reference. It said that using > "Z:\MyShare\db1.mdb" was better than "\\MyServer\MyShare\db1.mdb" [quoted text clipped - 4 lines] > as important, and they (Microsoft) used a UNC in their example. - Tony > M. >6. The Seek method should only be called against a direct BE >connection. > If querying a table link, FindFirst (DAO) or Find (ADO) must be >used instead. > > Reason - You can't open table-type recordsets from a table link. Agreed that you can't use Seek on linked table but I've never used Findfirst or Find. I just use recordsets frequently with the query and Where clause created in VBA code. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm (About point 6) and furthermore, with DAO, you are NOT limited to CurrentDb. You CAN use OTHER databases objects, and use one which, eventually, correspond to the table-direct where you would be able to use SEEK, in the few occasions where a limiting SQL statement with a WHERE clause would have been inappropriate. http://www.mvps.org/access/tables/tbl0006.htm Vanderghast, Access MVP >>6. The Seek method should only be called against a direct BE >>connection. [quoted text clipped - 8 lines] > > Tony >7. Set "Default record locking" to "Edited record." > Reason - Helps prevent corruption. News to me. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >8. The FE should be copied to each workstation (not shared). > Reason - Sharing defeats the purpose of splitting. And can greatly increase the chances of corrupting the FE. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >10. Use Access 2000 at a minimum, but 2003 is preferred. > Reason - 2000 is known to be buggy on a network. Not any more it's not. A client was happily using a mix of A97 and Jet 3.5 SP3 and A2000 and latest version of Jet. 25 workstations against a 300 Mb backend with 160 tables. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >1. Use JET OLE DB when connecting to BackEnd (BE) MDB file. Reason - >thread safety. Eh? Not even sure what Jet Ole DB is. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm The OLD DB recommendation came from a note on page 89 of O'Reilly's "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. "If you're using ADO libraries, connect via the OLEDB driver rather than using Open Database Connectivity (ODBC). ODBC is not thread safe -- it isn't stable in a multi-user environment and can cause data corruption." - Tony M. >The OLD DB recommendation came from a note on page 89 of O'Reilly's >"Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. Now that's a book I've never heard of. Never heard of the authors either. >"If you're using ADO libraries, connect via the OLEDB driver rather >than using Open Database Connectivity (ODBC). ODBC is not thread safe >-- it isn't stable in a multi-user environment and can cause data >corruption." Ah, so why not just used ADO against linked tables and avoid OLDEDB and ODBC all together. DAO doesn't allow you to even use an ODBC driver from within Access against an Access MDB. Which is, in my opinion, a good thing as that's just extra overhead. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm NOW THIS IS GETTING INTERESTING!!! They mention you on the facing page. There's a link to your Auto FE Updater utility. They say, "it runs on your server and automatically updates client frontends. It works, it's free, and Tony would love to get feedback and new feature requests." Runs on your server? Are they saying that it runs as part of the BE on a file server, or that you need a remote service? Do they presume too much? This is a good book, even great. I like how it is written, an excellent problem solving reference. Your last comment had me confused. So, I just discovered how split databases connect. I think. Thanks. I thought that when you defined a table link to an MDB, it by default used an ODBC connection. Wrong. I just found this comment on MSDN in "What Data Sources Can I Access with DAO and ODBC?": "DAO is best used with databases that the Microsoft Jet database engine can read, which includes all the above except ODBC data sources. Best performance is with Microsoft Jet (.mdb) databases. Attaching external tables, especially in ODBC data sources, to an .mdb database is more efficient than opening the external database directly through the MFC DAO classes without attaching." It is pretty clear now that DAO is the default connector. And, it's even recommended over ODBC for connecting to other Jet databases. So, is DAO thread safe? Is it better than OLE DB as well? If it were, would "they" tell us? I heard it from a good source that ADO is dead, replaced by ADO.NET, and that DAO is being revived in the next version of Access. Perhaps we'll have the answer then. -Tony M. So with respect to practice # 1 DAO vs. OLE DB - Questionable, TBD. >From the perspective of maintaining links though, it is handy to do it in code. This way, you can move copies of your database around and easily link to dev data vice production, and back. >NOW THIS IS GETTING INTERESTING!!! > >They mention you on the facing page. There's a link to your Auto FE >Updater utility. They say, "it runs on your server and automatically >updates client frontends. It works, it's free, and Tony would love to >get feedback and new feature requests." Very nice. The book must be at least decent then. <chuckle> >Runs on your server? Are they saying that it runs as part of the BE on >a file server, or that you need a remote service? It entirely runs on the client workstation but is loaded from your server. By your they mean the reader and not me, Tony. >It is pretty clear now that DAO is the default connector. And, it's >even recommended over ODBC for connecting to other Jet databases. So, >is DAO thread safe? Is it better than OLE DB as well? If it were, would >"they" tell us? I heard it from a good source that ADO is dead, >replaced by ADO.NET, and that DAO is being revived in the next version >of Access. Perhaps we'll have the answer then. Well, DAO was never dead in the first place. It had been deprecated mind you but there is now a new data engine. Access 12's new data engine http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx >So with respect to practice # 1 DAO vs. OLE DB - Questionable, TBD. >>From the perspective of maintaining links though, it is handy to do it >in code. This way, you can move copies of your database around and >easily link to dev data vice production, and back. That's one of the benefits of using linked tables on a drive letter. When I want to access the live backend I map the drive letter to the network share. When I want to access the test backend I SUBST the drive letter to a folder on my system. Furthermore, if I'm on the live backend, the main menu background is in blood red. This can be determined by using an API call to see if the drive on which the backend resides is a network or local drive. Alternatively you just use some code to relink the tables back and forth. Relink Access tables from code http://www.mvps.org/access/tables/tbl0009.htm Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm > I just found this comment on MSDN in "What > Data Sources Can I Access with DAO and ODBC?": [quoted text clipped - 5 lines] > efficient than opening the external database directly through the MFC > DAO classes without attaching." MFC is the Microsoft Foundation Classes, so this paragraph applies to C++ programs that use MFC. "Thread-safe" is not an issue unless you are using C++ or another language that allows you to initiate "multi-threading". That is not an issue with Access - DAO - Jet DB Engine - .MDB data file. I've not seen any convincing arguments for using ADO with an .MDB database, whether the data tables were split into a separate .MDB backend, or contained within a monolithic database. In Access terminology "multiuser" can apply to either a split or monolithic database, though you'll find a overwhelming consensus here that a split database has significant advantages. Generally, in the Access world, we don't use the term "multiuser" for "client-server" databases, for example, Access MDB - DAO - Jet DB Engine - ODBC - MS SQL Server (though you can substitute any ODBC-compliant DB for MS SQL Server). Most of the client-server work I've done over the years was with other servers, not because they were "better" in my view, but because the corporate IT department at my client companies had selected another DB as a/the corporate standard. Client - server arrangements have their own set of requirements, idiosyncracies, and performance issues (in a multiuser environment all the data extraction and manipulation is done on the user's machine; in C-S, the data extraction and most of the manipulation is done on the server). It is in Access ADP client-server database applications connected to MS SQL Server that ADO has seen its greatest use.) Larry Linson Microsoft Access MVP (About point 1-) Your list of possibilities is not exhaustive. Access uses (used), as example, NetDDE. So, unless you have hidden assumptions (like the back end being MS SQL Server), and so on, there are other techniques to consider before jumping to a conclusion, as you did. Vanderghast, Access MVP > The OLD DB recommendation came from a note on page 89 of O'Reilly's > "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. [quoted text clipped - 5 lines] > > - Tony M. >"If you're using ADO libraries, connect via the OLEDB driver rather >than using Open Database Connectivity (ODBC). ODBC is not thread safe >-- it isn't stable in a multi-user environment and can cause data >corruption." I think this comment is meant for non Access data storage systems such as SQL Server. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm I suspect they're talking about using ADO against other DBMS (like SQL Server or Oracle). Typically in Jet, you're going to be using linked tables, and as far as I'm aware, you cannot link tables using Ole DB. In code, you can use Ole DB when creating the ADO Connection property, but I'm not sure I'd want to be going that route for a strictly Jet application. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > The OLD DB recommendation came from a note on page 89 of O'Reilly's > "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. [quoted text clipped - 5 lines] > > - Tony M. Hi Doug, I was wondering if you would chime in. Here's part of my ADO code that resets table links to Ole DB: adoTbl.Properties("Jet OLEDB:Link Datasource") = strPath & "myBackEnd.mdb" Thanks to everyone for answering this thread. I've learned a lot. Hopefully I'm not the only one confused enough to need this kind of help. I wonder if we should start this thread over again with better gouge, leaving out the misconceptions. Perhaps you all would like to add to the list anything I left out? Each contributing comments on what they've seen works to make the best of a bad situation, that is having to split cause you have no better option like SQL Server. Thoughts? Thanks. - Tony M. >I wonder if we should start this thread over again with better gouge, >leaving out the misconceptions. Perhaps you all would like to add to >the list anything I left out? Each contributing comments on what >they've seen works to make the best of a bad situation, Sure, just visit my website. http://www.granite.ab.ca/accsmstr.htm >that is having >to split cause you have no better option like SQL Server. What do you mean by that? You should be splitting your MDB even if you just Access to store your data. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >I wonder if we should start this thread over again with better gouge, >leaving out the misconceptions. Perhaps you all would like to add to >the list anything I left out? The trouble is we have no idea what misconceptions you have without you stating them. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm I think it would be better if an MVP started the thread, and then others added to it. I'll keep my 2 cents in the form of questions vice assertions. Deal? - Tony M. >I think it would be better if an MVP started the thread, and then >others added to it. I'll keep my 2 cents in the form of questions vice >assertions. Deal? Sorry but no. I mean we can't answer a question that hasn't been asked. And we don't know If you want to know how to make an multi-user app run smoother visit my website. http://www.granite.ab.ca/accsmstr.htm One specific page is the "Splitting your app into a front end and back end Tips" page at http://www.granite.ab.ca/access/splitapp/ for more info. Also see the Corruptions and Performance pages. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Okay. Thanks. If anyone else wants to start a new thread, please just post a few ideas that you think are best practices for split environments. Maybe others will add to it. One question I have before we end this is with respect to the RecordCount issue. I've read that RecordCounts don't work well with linked tables. Splitting an existing database that uses this method could cause it to break in ways that may be hard to detect, a logic thing. Should I be worried about this as I approach the idea of splitting a dozen or more interlinked shared mdb files? Thanks, -Tony M. RecordCount works fine: as long as you go to the end of the recordset first. You could check for each occurrence of .RecordCount, and insert another line in front so that you end up with rs.MoveLast lngRecords = rs.RecordCount SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Okay. Thanks. If anyone else wants to start a new thread, please just > post a few ideas that you think are best practices for split [quoted text clipped - 12 lines] > > -Tony M. I have found a reference that covers this topic in depth, Ch 3 "Designing a Client/Server Application" in "Building Microsoft Access Applications" by John L. Viescas. A very solid attempt to share all the tricks of the trade, the chapter mostly focuses on file-server/split architectures. Well done, thorough, and easy to understand. My only complaint and the reason I didn't buy it the first time I saw it, the table of contents reads like a how-to on building his specific application. Looking things up in terms of access technologies/tasks is tough, but I've found that I can hardly turn a page without finding something I've just got to read. The book comes with his whole solution on CD. Pretty extensive. -Tony M. I doubt you'll hear any dissension from us! <g> SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >I have found a reference that covers this topic in depth, Ch 3 > "Designing a Client/Server Application" in "Building Microsoft Access [quoted text clipped - 9 lines] > > -Tony M. The original MFC C++ database classes were not thread safe. Anything built with them was not thread safe. There was a long running story that the 'Access' ODBC driver was not thread safe. A logical conclusion would be that the Access ODBC driver was built with a version of the C++ MFC database class which was not thread safe. This conclusion was re-enforced by the oft-repeated assertion that the Access ODBC drivers had been built with a version of VB that was not thread safe. That certainly appeared to be garbage: who outside of the biased ignorant would believe that the Access ODBC driver had been built in VB? There was also the possibility that the Access ODBC drivers called back to a version of VB that was not thread safe. Again, a pretty unlikely story: from ODBC, you can't call back from the Access driver into VB: you can only do that inside Access. However, eventually a more credible assertion emerged: by default Jet is configured to use three threads, and ASP is configured to pool multiple database threads, and if you let ASP call the create thread api to create a Jet database thread, Jet may call the create thread api from within the call. The create thread api is not re-entrant, and your database thread gets trashed. This suggests three possible solutions: Configure Jet to use only one thread: Configure ASP to use only one thread, or, Call Jet using some kind of thread-safe call that isolates the two calls to the create-thread api. ASP people don't like single-threading their database calls. I've never got anyone to report back on the effect of Jet configuration. There is apparently one particular kind of ADO call that gets around the api re-entrancy problem, but it's not all ADO/OLEDB calls that are legal: you still have to know what you are doing. In the end, we still don't recommend Jet for web servers. Not just because it might not be stable in a high demand situation: more because you have to take it offline and have file-system access in order to do back up and modifications. If you are running your own web server for internal use, it's not an issue. (david) > The OLD DB recommendation came from a note on page 89 of O'Reilly's > "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. [quoted text clipped - 5 lines] > > - Tony M. > b. ADO should be used for all DIRECT BE connections. Why would you want to do direct BE connection? Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >I'm starting this thread to pull together related questions and to >check my understanding. Please debate apparent misconceptions. BTW I decided to respond separately to each comment so as to keep responses shorter and easier for folks to debate the merit of one approach vs another. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm I'm getting dizzy... Cheers! Fred P.S. But at least I understand now what's going on! >>I'm starting this thread to pull together related questions and to >>check my understanding. Please debate apparent misconceptions. [quoted text clipped - 4 lines] > > Tony >I'm getting dizzy... <chuckle> >P.S. But at least I understand now what's going on! Yeah, I shoulda put that one first but I never thought about that until the end. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >Please debate apparent misconceptions. P.S. This could be an interesting discussion. <smile> Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm (About point 2) If you use SQL to query your tables, you don't really care about ADO or DAO. And in general, you should use SQL, not recordset looping. After all, we are not in VB5 to loop on a recordset to fill a combo box list, isn't it? So the point should be, consider to use SQL first. Vanderghast, Access MVP > I'm starting this thread to pull together related questions and to > check my understanding. Please debate apparent misconceptions. > > Do you agree that these are best practices? (...) > 2. DAO should be used when working within the FE (i.e. querying table > links). > > Reason - It's powerful. The default recordsets for bound forms are > DAO objects. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.