[AccessD] MDB to SQL Server

JWColby jwcolby at colbyconsulting.com
Wed Feb 21 22:05:08 CST 2007


And given the average Access database, this is hardly a limit.  I have what
I consider a "large" database, where after 4 years of operation, it is up to
about 600 mb of data.  If it were in SQL Server (it is not), at least when
it hit that limit the client could pay up to get the full version. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Moss
Sent: Wednesday, February 21, 2007 4:56 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] MDB to SQL Server

Actually SQL Server 2005 Express has a 4G capacity per database, and I guess
an unlimited number of databases per. Th 4 gig doesn't include the log file,
just the .mdf.

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list