 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / July 2005Is acceptable BE Database splitted into 4?
I have finished a project in MS ACCESS 2003. Since the master tables were quite huge, I decided to split my database to FE/BE into 4 database. These databases were splitted to Inventory Master Table, Vendor Master Table, Main Transaction Table, and Details Transaction Table. The main database (FE) consists of Queries, Relationship, Forms, Codes, and Reports as they are expected to be on FE. I have used the table link manager to do all the task in splitting the database and thereby linking to each of the other BE databases (Inventory, Vendor, Transaction, Details Databases). Those 4 BE's are located in a network using of course the UNC path as link table manager configured it. As of testing, the FE which are now distributed to 50 users are quietly working very well. Ready for production after rigid testing. Just want to clarify more issues on networking. My question are: 1. Did I make the right procedure? 2. Is this set-up will compromise the performance? (Inventory Master File contained 60,000 records, Vendor 30,000 records, Transaction will be accumulating when the application start to use, and so with the details). 3. Why then when I tested to my PC, the FE transaction form is loading with 10 seconds (with the above info?) saying on the bottom bar "Calculating..". What is this all about? Is the network?, or the link database being loaded? 4. Is there any possibility of corruption? (If users are concurrently updating, adding record at the same time? My FE was configured to have the default setting which is "Open databases using record level locking"; Set default record locking to "No locks"; Default open mode to "Shared", Edit/Find "Don't display list... to 30,000; General "Compact on Close". 4 BE's was configured the same). 5. F-up on item 4, are my 4 BE's automatically compacted same as my FE when closing the database? 6. If the procedure I did is wrong, any advice to meet the minimum requirement in setting-up multi-user application? Note: the above FE/BE's was not configured to a user-level security or never applied any security measure. I did some end-user friendly security measure for the above FE/BE's. Your help and advice is very much appreciated. Thanks in advance... Jrb Answers in-line below.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > My question are: > > 1. Did I make the right procedure? In my opinion, a major disadvantage of having multiple back-ends is that you cannot automatically enforce referential integrity. You can only set up relationships between tables in the same MDB. Therefore, I'd say you didn't do the best possible thing. > 2. Is this set-up will compromise the performance? (Inventory Master File > contained 60,000 records, Vendor 30,000 records, Transaction will be > accumulating when the application start to use, and so with the details). 60,000 records in a single table isn't that large. Unless you have a huge number of transactions and details, you probably won't have a problem with the 2 Gb limit in a single table. > 3. Why then when I tested to my PC, the FE transaction form is loading > with > 10 seconds (with the above info?) saying on the bottom bar > "Calculating..". > What is this all about? Is the network?, or the link database being > loaded? It's not uncommon to experience some degradation of performance once you split the database. Take a look at what Tony Toews has to see at http://www.granite.ab.ca/access/splitapp/performance.htm > 4. Is there any possibility of corruption? (If users are concurrently > updating, adding record at the same time? My FE was configured to have the > default setting which is "Open databases using record level locking"; Set > default record locking to "No locks"; Default open mode to "Shared", > Edit/Find "Don't display list... to 30,000; General "Compact on Close". 4 > BE's was configured the same). Unfortunately, there's ALWAYS a possibility of corruption. Access tends to be very sensitive to network problems. Should any of your users have an iffy NIC, your chances of corruption are much higher. > 5. F-up on item 4, are my 4 BE's automatically compacted same as my FE > when > closing the database? No. Compacting the FE has no impact on the BE(s). Databases cannot be compacted when there are users in them: that's why compacting the FE does not even try to compact the BE. (And I forgot to mention above that Compact On Close is meaningless in the BE, since you're not really opening it) Dear Douglas, Thanks a lot for your great advice. Does this mean that I am going to put back my Inventory Master & Vendor to a single BE database and leave the other (Transaction & Details) as it is, to make 3 BE's? or to put back all in one BE? For sure, transaction and details tables would create a huge number of records when the application starts. What would you recommend then? How and when does corruption occurs?, how could we manage it?, is it the FE that will cause the BE to be corrupted? How about the below default settings? are these enough to handle the network transaction application? Your unselfish advice is highly appreciated. Thanks in advance... Jrb >Answers in-line below. > [quoted text clipped - 47 lines] >not even try to compact the BE. (And I forgot to mention above that Compact >On Close is meaningless in the BE, since you're not really opening it) Define "huge number of records". Depending on the record size, Access is capable of dealing with tables of a million plus rows. Personally, if I thought that the data associated with my application was going to be too large to fit in a single MDB, I would move to SQL Server. The corruption problems I was talking about will occur in the back-end. While the front-end can corrupt as well, it's not as common, plus it's trivial to fix: you simply give the user a new copy of the front-end, and you don't have to worry that you may have lost any data. I usually go with the default settings, so I don't see any problem with them. One thing that I'm not sure was explicit in your post. All these users are going to be on the same LAN, aren't they? If there's a WAN involved, all bets are off! SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Dear Douglas, > [quoted text clipped - 77 lines] >>Compact >>On Close is meaningless in the BE, since you're not really opening it) Dear Douglas, Thank you for your invaluable advice. As I see from your advices, my records would not reach that large. If it is a million plus rows, then I am quiet sure that my records would not reach more than that. I am going to follow your advice and put it back all the 4 BE's into 1 MDB. My users are on LAN so I guess there will be no bottleneck. Now I am ready to start the production and will observe the transaction. I hope you could still be around when I have some queries that needs to be solved. God Bless and hoping you may have more users to receive your help. Thanks a lot... Jrb >Define "huge number of records". Depending on the record size, Access is >capable of dealing with tables of a million plus rows. [quoted text clipped - 19 lines] >>>Compact >>>On Close is meaningless in the BE, since you're not really opening it) >Thank you for your invaluable advice. As I see from your advices, my records >would not reach that large. If it is a million plus rows, then I am quiet >sure that my records would not reach more than that. I am going to follow >your advice and put it back all the 4 BE's into 1 MDB. I've got a client with 600K records in one table and 400K records in each of three other tables. Along with 150 other tables in the same MDB. 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 |
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.