Dan Waters
dwaters at usinternet.com
Wed Feb 21 14:43:01 CST 2007
Jurgen, A few thoughts: 1) SQL Server 2005 Express has a 2G storage capacity, and is free. 2) You can protect your production BE data file using Windows folder permissions in an interesting and non-intuitive way. You'll need to get a copy of the book by Garry Robinson on Real World MS Access Security, and read through Chapter 12. This prevents regular users from opening the folder, but allows them to use a FE that connects to a BE in that folder. I use this at two customers, and it works well. 3) It sounds as though you have 10 years' data accumulated into one BE file. Is there a possibility that some of the data could be moved to an archive BE file? 4) For speedier performance, several people have suggested creating a connection to the BE from the FE when first opening a database. Keeping the connection open can improve performance because the overhead associated with opening and closing many connections (recordsets, queries, bound forms, etc.) can be avoided. However, there is a registry key which defines a time-out period for an inactive connection to Jet - the default value is 10 minutes. 5) Why are you getting complaints about your system which is faster? Because everyone knows that complaining to the corporate parent is useless, but when they ask you to do something, you respond! :-) HTH! Dan Waters -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz Sent: Wednesday, February 21, 2007 2:20 PM To: accessd at databaseadvisors.com Subject: [AccessD] MDB to SQL Server People: Given that the organization I work for has decided that the database I built for them over the years will be going from a maximum of 41 to about 50 users in the course of this year and the complexity and size of our database has been growing at an ever accellerating rate and we have been suffering from the occasional corruption, the question of what we can do has been asked of me. We are running individual DAO FE files via a pair (soon to be four) of servers against a file server MDB on a gigabit LAN. Corruptions started happening a couple years back when users copied the backend MDB file to their local laptops. When we were on a local network, this never caused any grief but when we switched to a terminal server environment, the copy duration went to several minutes and, unhappily, the original server data flie frequently became corrupted. If any users logged on after the corruption, they were given an error about a damaged database while existing logins proceeded without issue. The only resolution was to bump everyone off, repair the BE file and then allow everyone back on again. Typically, one record in a single table would be found to be at fault with a ton of invalid junk in the fields. That record would typically have to be deleted. Curiously, it often contained out of range numbers even after repair. We put a stop to copying open MDB files to remote laptops (you could still make a copy on the server and then copy that copy without issue). Notwithstanding that, we occasionally get a corrupt MDB BE file showing a similar kind of damage. Of course I have no way of knowing whether anyone tried to copy the file or whether it was just the fact that user or MDB limits had been exceeded. Corruption may happen once every 4 to 8 months. When I started all this in about '96, my boss was told that our parent company would look at our data needs in 5 to 7 years. My boss was told today that it would be another 4 years at least. I suspect that target will be signifcantly exceeded. We could be up to 100 users by that time, with a 200 Megabyte BE file. The whole discussion about DAO/ADO caught my attention as I haven't been doing much with our system in the past 4 years other than some refinements and enhancements. Access development has become more of a sideline. I wrote the basics at a time when DAO was my only option and wasn't give the resources to convert to ADO. SQL Server has never been an option, even though the parent company, which grosses over a billion in annual revenue, relies on it for their systems. If I can provide sufficient justification, I may get the go ahead to upsize. The question I am posed is, what is the next step for the system I built. My boss is complaining about performance of some of our forms, but the fact is, our forms display considerably more information on a screen than any of the parent company forms and they still open and populate a great deal faster than our parent company forms. When I pull a safety stats report on screen from the parent company on a single manager on perhaps 20 projects, it may take 20 to 30 seconds to load, yet my slowest project form will load within 10 seconds. My load time is a bit slow because of a number of lists and combos that retrieve a large number of records or retrieve from tables that I can reduce in size by archiving datas, but it shows a great deal more information from a broader variety of sources. I know I can gain sginficant performance by denormalizing. I've always split my addresses into tables for country, province/state, city and address. By storing all the data in a single field rather than in separate tables, the joins process significantly faster. I have used Callback functions for many lists and combos to aviod hitting a BE repeatedly, but, for continuous forms using combos filling from a table with a large number of records, the forms poplulate quite slowly, much more slowly than when the combo is filled with an SQL rowsource. I could make Just In Time combos and use arrays of controls (as I have done in various situations) but I think my best option is to cut down the size of the data file, giving users the option to see current data only, and current data unioned with archived data and let them wait the small percentage of the time when that is necessary. Given that we are moving from 2 servers to 4 over the next few weeks, it seems that people are starting to realize that our division has increasingly significant needs and be more amenable to suggestions. I welcome any input from the list. Ciao J|rgen Welz Edmonton, Alberta jwelz at hotmail.com _________________________________________________________________ Find out the restaurants participating in Winterlicious http://local.live.com/default.aspx?v=2&cp=43.658648~-79.383962&style=r&lvl=1 5&tilt=-90&dir=0&alt=-1000&scene=3702663&cid=7ABE80D1746919B4!1329 >From January 26 to February 8, 2007