[dba-SQLServer] Access BE to SQL

Robert,

Yes, but SQL Server does not have VBA, which is my point.  It has a language
of sorts but it is simply not equivalent.  Thus there is no way to just take
your vba functions and "use them" in SQL Server.  You have to translate each
one that is used in any sql statement, one by one, testing that the results
are equivalent. 


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 Robert L.
Stewart
Sent: Friday, October 06, 2006 9:44 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Access BE to SQL

Actually John, you can reference home made functions in SQL Server, they
just have to be created in SQL Server and not Access.  I use them all the
time.  Yes, he has some light areas and number 1 is one of them.  There is a
lot more naming that you should do in SQL Server that you do in Access,
indexes, foreign key relationships, constraints, etc.  And redesigning the
application to use stored procedures, after creating all of the stored
procedures, is going to take a while.

At 09:40 PM 10/5/2006, you wrote:
>Date: Thu, 5 Oct 2006 21:06:31 -0400
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Access BE to SQL
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <004801c6e8e3$a9dcadf0$647aa8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>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

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