[AccessD] Old Dog NewTricks

There are a couple of other issues to consider.

1) If you want source control, Access is problematic.
2) If your application is going to get large, lots of queries, forms etc. and particularly lots of 
code, access is problematic.  Access doesn't have a lot of organizational tools for grouping stuff. 
  Yes, you can go with separate FEs but suddenly you have severe maintenance issues trying to 
discover "where used" kinds of things across the FEs.
3) If you want to use large libraries, and particular libraries where one depends on another, Access 
is problematic.
4) If you need threads, fugedaboutit.
5) If you want to execute stored procedures in SQL Server, Access is problematic.  Access is single 
threaded.  When it executes a stored procedure it will stop code execution waiting for sql server to 
return.  If SQL Server takes a long time (long running query) you end up with users seeing that the 
user interface is locked.  Users tend to reboot or use task manager to close access when the user 
interface becomes unresponsive.
6) When you push the envelop in Access, you begin to get issues with Access page faulting, or 
staying open when it should be closing.  Lots of decompile / compile / compact / repair cycles 
chasing ghosts.
7) When you need a developer team to handle pieces of a system, Access is problematic (see #1).
8) If you want a run-time so you can just ship an exe...

I have written one extremely large application in Access, ~200 tables, 1.5 gigs of data and 
counting, ~200 forms, a couple of hundred queries.

Access was superb in getting me to this point but it sucks trying to go any further.  The client has 
invested a lot of money in this system and of course they are reluctant to "start over".

I wish it was in C# now.  Understand that I have never built a system of this size in C#, but I know 
that many of the issues I have could be handled in C# but are very difficult (or impossible) in Access.

It is these cases where you look at Access and wish... wish that Access had better big system tools, 
had threading, had real libraries, had access to all the cool things that are in .Net.

Most of the things I am discussing are not an issue while the system is small, and most of the 
things I am discussing become a problem once a system reaches a certain size.


John W. Colby
www.ColbyConsulting.com

On 1/13/2011 12:30 PM, Dan Waters wrote:
> Hi Tony,
>
> These are the significant technical differences that I believe exist (that
> affect the user/customer) between a split FE/BE Access app and a
> client/server .Net/SQL Server windows forms app:
>
> 1) Security: Access login security is only nominal.  I use it at my
> customers just so the database knows who is logged in.  If you use Access
> login security, you must assume that no one in the company will try to do
> something nefarious.  With SQL Server, you can use Windows Authentication,
> which is far better.
>
> 2) Concurrent User Quantity: With Access, you normally can have perhaps 20
> concurrent users before performance begins to slow down.  This could be more
> or less depending on exactly how your system is set up.  Some people on this
> list have said that they can have over 50 concurrent users on a system where
> most users are entering data into forms specified as data entry forms.
>
> 3) Reach:  On a LAN, Access performance is great.  On a WAN, Access
> performance is infuriating.
>
> So, for these reasons, you might think that .Net/SQL Server is the way to
> go.  But there are many apps where you don't need windows authentication,
> your number of concurrent users is limited (or you can create a limit), and
> you are on a LAN.  For these situations (perhaps the majority) Access is
> excellent.
>
> If you are making a system for your own company, you plan to learn Access
> well enough to continue to support it and improve it, and you (will always)
> meet the three requirements above, go ahead and use Access.  However, Visual
> Studio and the .Net languages have made significant improvements over the
> last several revisions (i.e., 'dream language').  Someday you may be asked
> to let someone on a WAN log into your system, or someday you may be asked to
> 'make it work on the internet', and then you'll need to learn .Net anyway.
>
> If you're planning on making systems for your own clients, today you should
> start with .Net and SQL Server.
>
> Good Luck!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tony Septav
> Sent: Wednesday, January 12, 2011 4:49 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Old Dog NewTricks
>
> Hey All
> Sorry I do not want to start a flame up (I think that is the term),  but
> in my research of VB.Net the conundrum of what are the advantages of my
> using VB.Net to enhance the development of a database application versus
> ACCESS keeps coming up. If you can could you please direct me to
> articles expressing this view or your views pro/con (I am not writing an
> article on this, it is just for my own curiosity) it would greatly be
> appreciated (remember I am a Newbie to VB.Net). If you don't mind, to
> avoid clogging up the user group,  please  EMail me offline. Any
> responses will be greatly appreciated.
>
> Thanks
> Tony