[dba-SQLServer] Access BE to SQL

JWColby jwcolby at colbyconsulting.com
Thu Oct 5 21:40:02 CDT 2006


LOL, you just want to be prepared.  

One thing I have discovered is that man tends to minimize the time required
to do anything, from changing a tire to painting the house, to writing a
function or query.  If you are very good at whatever you are doing, you
often have the experience to overcome this tendency.  Professional painters
just know how long painting takes, professional developers likewise.  But
this is not something you are familiar with and JET and SQL Server are just
different animals.  Thus a simple "multiply by three" might well get you in
the ballpark.  You will come up to speed eventually.  The first half of any
given set of tasks will go way slower than that, and the last half will go
faster.

One thing you might want to consider is moving up to SQL Server 2005.  That
has the full on VB.Net as the scripting language right inside of SQL Server
(or so I am told).  While VB.Net and VBA might not be a close match, for the
kinds of things you might run into it might way better than what is
available in SQL Server 2K.  As an added bene, when you move the app itself
to .net, you will already have the core database running on SQL Server 2005,
and you will be becoming a VB.Net kinda guy.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Steve
Capistrant
Sent: Thursday, October 05, 2006 10:09 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Access BE to SQL

John,  I don't know whether to smile or cry.  Perhaps I'll split the
difference with a sigh.
 
Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
www.symphonyinfo.com
Main: 763-391-7400 ext 801
Toll Free: 888-357-1373 ext 801
Direct:  612-237-0075

________________________________

From: dba-sqlserver-bounces at databaseadvisors.com on behalf of JWColby
Sent: Thu 10/5/2006 8:16 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Access BE to SQL



Looks good.  Now...

As with all time estimating, a very realistic time estimate can be found by
taking the number and multiplying it by 3.  Then take the unit and increase
it by 1.  In other words, if you think it will take 15 minutes, multiply 15
times 3 (45) and up the minutes to hours. 

Other than that, your estimates look fine to me.

Seriously though, 

1.  Migrate Data to SQL.  Resetting of autonumbers, referential integrity,
some indexes.  332 instances @ 10 minutes = 55 hours

Multiply by at LEAST 3.

2.  Convert queries to views.   Convert high-impact queries into Views and
Stored Procedures.  For each of those migrated queries, redesign filtering
code to pass parameters back to SQL Server.  1451 instances @ 15 minutes =
362.75 hours.  Limiting to high impact queries: 300 instances @ 15 minutes =
75 hours.

Multiply by at LEAST 3.

Etc.

Etc.

Etc.

And yes, I am serious.  You will have no clue until you get in there what a
mess you have.  Access allows things that SQL Server does not, such as
referencing home built functions in VBA inside of queries.  How do you
replace those?  Referencing built-in VBA functions inside of queries.
Easier to replace but still often not trivial.  You have to learn the
similarities and differences between the Access version and the SQL Server
equivalent.  And what about data type differences?

You will end up completely rewriting portions of your application inside of
SQL Server.  The more "Access tricks" in the Access app, the tougher it will
be to rewrite.

And one thing you missed completely is testing of the changes to ensure that
the result still does whatever it did originally, with exactly the same
result.

Access was designed from the ground up to be a RAD environment and in order
to achieve this it gives you power beyond anything that SQL Server has ever
imagined. 

I will be watching this thread closely, I can tell you that. 

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Steve
Capistrant
Sent: Thursday, October 05, 2006 5:56 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Access BE to SQL

Well, looks like I'm going to be hanging out in this group a little more.
My team is about to migrate an Acc/Acc (AccessFE over AccessBE) application
to Acc/SQL (AccessFE to SQL Server).  I'm sure this topic has been well
discussed, but could I please as for some starter tips and feedback?

Also, I am considering hiring out some or all of this to someone who's done
it a few times, so let me know if you are interested.

The Acc/Acc app is a mature 6 year old system sold commercially, but getting
unwieldy in size, speed, and corruptability.  Long term plan is VB.NET over
SQL.  Short term (1 year) is to just get the BE in place, and rewire the FE.
Pushing heavy queries to the BE are expected to go a long way in improving
performance, buying us time to migrate the FE later.

The application has 332 tables, 940 saved queries, 239 queries dynamically
defined in code, 282 reports, 119 code modules, 899 recordset manipulations,
and over 25,000 lines of code.  FE is 27 meg (in MDE format) and growing.

Here's the essential task list as we see it (lacking experience), including
an estimate of time:

1.  Migrate Data to SQL.  Resetting of autonumbers, referential integrity,
some indexes.  332 instances @ 10 minutes = 55 hours

2.  Convert queries to views.   Convert high-impact queries into Views and
Stored Procedures.  For each of those migrated queries, redesign filtering
code to pass parameters back to SQL Server.  1451 instances @ 15 minutes =
362.75 hours.  Limiting to high impact queries: 300 instances @ 15 minutes =
75 hours.

3.  NewID modification.  Adjust the programming code in all places where a
new record is created and the resulting unique ID is captured.  Access and
SQL behave differently - Access allows capture before posting, SQL does not.
66 instances @ 15 minutes = 16.5 hours.

4.  Boolean field modification.  Access and SQL both support a Boolean data
type SQL allows a null option whereas Access does not.  All default Boolean
settings in the app must be reviewed and changed as necessary to account for
this difference.  664 instances @ 5 minutes = 55 hours.

5.  Paging on List Views.  Currently, all list views (on forms) are
structured to allow display unlimited number of records (often returning
hundreds of thousands of records).  To minimize the traffic hit, we want to
reengineer  few of the biggest ones to implement paged subsets (e.g., 100 at
a time).   15 major list views @ 2.5 hours each = 37.5 hours.  Targeting
high impact only: 5 lists@ 2.5 hours = 12.5 hours.

-------
How's that look?  Missing things?  Over/underestimating time?  Thank you in
advance.

Steve Capistrant
Symphony Information Services
scapistrant at symphonyinfo.com
www.symphonyinfo.com
Main: 763-391-7400 ext 801
Toll Free: 888-357-1373 ext 801
Direct:  612-237-0075


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com









More information about the dba-SQLServer mailing list