[AccessD] MDB to SQL Server

Dan:

>1) SQL Server 2005 Express has a 2G storage capacity, and is free.

I don't think money is an object here.  Head office has SQL Server and 
licences.  All my users run their accounting and equipment applications and 
my project management and costing software.  They do a significant amount of 
double entry.  I'd prefer to integrate with their systems but so far, I've 
been refused any rights or access.  Presumably I could run a full blown SQL 
Server BE separate and apart from what they do.

>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.

This would help monitor the copying of the open BE provided that is the 
primary remaining cause of corruption.  A very useful tip.

>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?

This is an option I am entertaining.

>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.

I have a function like CurrentDb that maintains a static object variable 
connected to the BE and only refreshes when necessary.  I first wrote about 
relinking performance when maintaining an open connection about 7 years ago 
at this list and have used the technique ever since.

>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!  :-)

The simple fact is that my users were spoiled with a system that was many 
times faster and the performance has deteriorated with the passage of time.  
I could display less data and split out more tabs and do a bit more Just In 
Time data retrieval.  The system essentially retrieves only one record upon 
navigation of the primary forms, but sub forms may have up to a couple 
hundred related records.  However, many of the subforms have records based 
on  relatively large tables that can not be diminished in size and this 
appears to be the greatest slowdown.  The truth is, there are few tables 
with more than 10,000 records, it's just that there are well over 150 tables 
and I'm pulling data from many of them for each form.

Ultimately, the question is, can I go up to 70 or so users in the next five 
years with a database that was originally designed to support a half dozen 
with a much smaller data file.  I can certainly play with upsizing, but I 
suspect that, given the relatively small size of the tables (from a SQL 
Server perspective), I'm not going to get a performance increase.  It would 
probably allow me to normalize further than I have heretofore and I should 
get better security and robustness.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com

_________________________________________________________________
http://local.live.com/default.aspx?v=2&cp=43.658648~-79.383962&style=r&lvl=15&tilt=-90&dir=0&alt=-1000&scene=3702663&cid=7ABE80D1746919B4!1329