[AccessD] Article on upgrading

Stuart I agree with you on all topics.

I've done things with Access that some people don't even know it's even
possible to do, which is why those same people consider it a toy.

in regards to #1, I meant inline comments as is done in VBA:

INSERT INTO tblSomeTable (field1, field2...)
    SELECT field1, field2
    FROM tblSomeWhereElse
    WHERE Something =1
          AND HireDate > @SomeDate --Added on 1/1/2011 for some stupid
reason
          AND SomeOtherReason = @TheCriteria  --Added 7/1/2011 because
someone decided this is now needed

Another reason I love the inline comments is I've had some accountants have
me add criteria, then question it 6 months later
so I tend to put stuff in the stored procedure (used for a report) that
reminds me so.

--AND Status Flag IN ('A','R')-- old criteria prior to 6/9/2009
AND Status Flag IN ('A','R','V')--Added on 6/9/2009  as per chuck's email
asking for this

I love emailing them their original email asking for it to be changed.


#2, I guess I'm used to dealing more with ADPs than MDBs.
     But this too, was worded badly.
     A stored procedure needs to be changed because a new table needs
     to be added or criteria changed in WHERE clause.

    If this was a non split mdb or if the user was creating the dynamic sql
    in the FE (not just access but VB/C# FE's too), the FE would have to be
changed.
    SQL using ADPs or C#,VB.NET, Web FE's see the change instantly.


 #3 When have you seen access return a resultset faster than SQL?

 #4  Yes, you can use functions in the FE, but once again, if it can be done
in the BE
      and does not slow it down, and allows changes without affecting the
FE, I prefer doing
      it back there.

 #5 :)

 #6 They're still triggers. Bleh! :P

 #7 I do this too, but it is nice to have a built in feature that does this.

 #8 me too, but like I said above, it's nice to have something built in do
this.

 #9 true, but it gets ugly when you have to do a lot of case statements.
     SELECT
             CASE WHEN X=0 THEN 'You are Royally Fd today!'
             CASE WHEN x=1 and Sky=blue and StarsInTheSky =0 THEN 'Its a
perfect world'
             CASE WHEN x=1 and Sky = gray and StarsInTheSky=0 THEN
'MustBeMonday'
             CASE WHEN X=1 and Sky = black and StartsInTheSky =1 THEN 'Enjoy
the night!'
             --Many more cases here
            ELSE 'Just a normal day'
     END AS TypeOfDay

  #10, Oh, I remember now, restore back in time!!!!
        I've never had to use this for reasons of my own <ahem>
        but I've had other developers yell out "OH SHIT" when testing
something
        on a live system and not use a transaction/rollback.

        no prob, restore to a point back in time, like 10 minutes. done. :)


On Fri, Jul 29, 2011 at 10:40 AM, Stuart McLachlan
<stuart at lexacorp.com.pg>wrote:

> Just to play Devil's Advocate :)
> - See comments in line
>
> --
> Stuart
>
> On 29 Jul 2011 at 9:30, David McAfee wrote:
>
> > 1. The ability to write comments in the SQL
> >
>
> Access queries have a Description property which lets you store comments
> about the query.
>
> > 2. The ability to do changes to data in the back end, without
> > requiring a FE change. I know most will say that you can do this in
> > Access too, but the FE should be for presentation of the data and a
> > place to enter the data. Need to change a view or sproc? Change it,
> > and as long as input parameters haven't changes, to tweaking is needed
> > in the FE.
>
> Changes in an Access BE table are seen automatically by an Access FE.
>  Changes in an
> SQL Server table are NOT seen automatically in an Access FE.  You need to
> relink the FE to
> see the changes.
>
> >
> > 3. Speed
> >
>
> Sometimes.
>
> > 4. Ability to use UDF's
> >
>
> You can use Access functions in queries to an Access BE
>
> > 5. Stored Procedures!
> >
>
> VBA
>
> > 6. Triggers, even though I try not to use them. I feel if the system
> > is designed correctly,
> >     there is no reason for a trigger. Now if a system, such as an ERP,
> >     has
> > stored procedures
> >     which are not allowed to be modified, then I can see a reason to
> >     use a
> > trigger for a table
> >     that gets updated.
> >
>
> Access 2010 has table macros/triggers.
>
> > 7. The ability to run scheduled jobs and back ups each night
> >
>
> I've been running scheduled jobs and backups with Access BEs since ver 97.
>  Task
> Scheduler and  command line arguments are your friend.
>
> > 8. The ability to email from the BE if certain conditions are found
> > (new record found during a job ran at midnight)
> >
>
> I've been writing functions to automatically email from Access based on
> various conditiions
> since ver 97.
>
> > 9.  Case statements in SQL
> >
>
> Access queries can use IIF() and Access functions containing SELECT CASE
> and other
> more complex/powerful conditionals.
>
>
> > I'm sure I can think of more reasons to use SQL :)
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>