Can I use Access 2007 for Professional Application Development?

[Artificer]

I am a .NET developer and a group of friends came to me with this
question: They want to develop commercial applications targeted for 50
or less users using SQL Server as the back end. They want to know if
access could be used for this environment because it appears that it
is easier to develop using it than using Visual Studio.

I know that access 2007 can be use SQL Server but I don't know if you
can develop forms that use stored procedures instead of SQL Statements
(for performance) and if the code can be properly protected. Also I
would like to know if there is any way to implement a custom login
mechanism for security.
If you can provide me with example of commercial applications
developed on Access that will be nice!

Thanks in advance!

 

[Susie DBA [MSFT]]

you just need to learn Access Data Projects

ADP are a dream come true

with MDB if you want to build a form based on a sproc; you need to
write about 100 lines of code.
with ADP it is as easy as setting the recordsource to a sproc

 

[(PeteCresswell)]

Per Artificer:

I am a .NET developer and a group of friends came to me with this
question: They want to develop commercial applications targeted for 50
or less users using SQL Server as the back end. They want to know if
access could be used for this environment because it appears that it
is easier to develop using it than using Visual Studio.

Yes.

In my experience, the man-hour factor is somewhere between 3 and 50.

3 from the time I re-wrote a couple of simple Access apps in VB6.

50 from the man hour diff between a delivered MS Access application by
myself, and an IT effort that replicated it in .NET. Actually the factor was
something over 100, but they added maybe 40% more functionality.

If I had to ballpark it for myself, I'd say between 3 and 5 for MS Access
vs .NET - both with the same back end.

I know that access 2007 can be use SQL Server but I don't know if you
can develop forms that use stored procedures instead of SQL Statements
(for performance) and if the code can be properly protected. Also I
would like to know if there is any way to implement a custom login
mechanism for security.

Yes. Use ADO instead of DAO to get to the back end and in the back end only
allow access through stored procedures and views.


The penalties for all those saved man hours are several, but not necessarily
show-stoppingly severe. I'll leave it to others to point them out.

 

[Albert D. Kallal]

As for commercial programs...yes, it is a great environment.

There are some caveat you need to be aware of:

First, you have to evaluate your skill set. While users with no programming
experience can actually build amazing applications, you do need a good
understanding of the development platform.

I mean, if you are familiar with .net, then perhaps you could consider that
environment. The question becomes how much time do you have to learn
ms-access vs. that of knowing your current tool set?

I mean, can you afford two, or three months to learn the new environment, or
simply use that time to be productive with your current set of tools.

To
really get up to speed, the amount of time to learn Vb.net, VB6, or
ms-access is actually equal in terms of investment. DO NOT kid yourself
that you save large amounts of time using ms-access (you will SAVE huge
AMOUNTS of time, but ONLY if you know have to get ms-access to do the
dirty work for you. If you don't know how to make ms-access sing, then you
going to be in trouble real fast).

In fact, as compared to
VB6, if you writing a lot of code, them ms-access actually has a STEEPER
learning curve then does vb6. I do NOT recommend jumping into a project of
any size or significance, and LEARNING the product as you go..that is bad
idea...

So, if you have the luxury of learning a new tool, then ms-access is a great
choice.

keep the following in mind:

There are certainly more levels then just "trained" or "not trained".
Generally there are a "lot" of skill levels, but the following breakdown is
sufficient. **

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)

Stage 6 Master (has internalized X, knows when to break the rules)

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)


One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure.

So, keep in mind your skill levels. Here is a good article on converting a
application to ms-access. There are some interesting lessons, and some good
points on the ms-access development process.

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000003.html

If you want to see some screen shots of a commercial type application in
ms-access, try these of mine:

http://www.kallal.ca/ridestutorialp/index.html

and:

http://www.members.shaw.ca/AlbertKallal/Rides/Rides.html

While ms-access is one of the most productive products on the planet, you
still have to have a good software development process..and familiarly with
the tool is still required for success...

Good luck!!

 

[Artificer]

Sory what you mean by the man hour factor? You mean the time you
spended developing it? English is my second language!

 

[dbahooker]

Albert;

I am the only stage 7 person on this channel

everyone else is a MDB fairy

-Hooker

 

[Arvin Meyer [MVP]]

Sory what you mean by the man hour factor? You mean the time you
spended developing it? English is my second language!

Yes that's exactly what he means. My experience is that you will save at
least 85% of your time using Access over either ASP or VB.NET. Also, stored
procs don't always increase performance. On tables with a few thousand
records JET may actually be faster since it will only use the server when it
is faster. On large tables, stored procs are usually faster. I use MDB/MDE
with both ADO and DAO depending on what I'm doing. It is very important to
disambiguate your code when using recordsets, if you use both ADO and DAO.

 

[Artificer]

This answers apply for Access 2007?

 

[Albert D. Kallal]

This answers apply for Access 2007?

Yes, for the most part a2007 is the same.

In fact, the forms designer has some really nice improvements. (the grouping
of controls, and the stacked, and tabbed formatting options actually saves a
LOT of messing around and moving controls on the screen. for some tasks, I
find the additions to the a2007 forms designer a LARGE step above that of
the previous versions.

And, we also have a new "split" form in which a grid of data can provide
navigation for editing of records...again a nice feature...

 

[Artificer]

I havent seen any samples about how to create application's login,
password and roles stored on a SQL Sever database and them allowing
the use of a given form to users of a given 'role'. Any idea about
that? Also I will apreciate books recomendations! preferable for
version 2007

 

[Tony Toews [MVP]]

A a r o n K e m p f wrote:

Needless to say Susie DBA doesn't work for Microsoft.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Albert D. Kallal]

The use of "roles" has not special meaning in ms-access.

for sure, the security assigned to your users will be respected.

and, I suppose you could build some routines to "test", or "return"
if a user is a member of that particular role "group".

I mean, you can in the on-open event of a form could run code
to check for "role" membership,and then cancel the form load with
an appropriate message for example....

You have to roll your own logon form also. (and, then setup
a dsn-less connection).

As for books,, here some good links:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Books

 

[Artificer]

Thanks a lot everybody

 

[David W. Fenton]

Yes, for the most part a2007 is the same.

In fact, the forms designer has some really nice improvements.
(the grouping of controls, and the stacked, and tabbed formatting
options actually saves a LOT of messing around and moving controls
on the screen. for some tasks, I find the additions to the a2007
forms designer a LARGE step above that of the previous versions.

But that's not available in MDB/MDE, as Arvin mentioned -- you have
to use the new file format. But everything else applies, as the new
file format uses the same interfaces to SQL Server data.

 

[David W. Fenton]

you just need to learn Access Data Projects

I believe that each of Aaron's promotions of ADPs should be met with
the following response:

ADPs are deprecated by Access and may cease to exist one or two
Access versions down the road. In fact, the current official word on
ADPs, and why they are deprecated, is found here (all on one line):

http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-
446a-8ff2-221769a58ba51033.mspx?mfr=true

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, like the .xls
or.doc file formats. It contains forms, reports, macros, VBA
modules, and a connection string. All tables and queries are
stored in SQL Server. The ADP architecture was designed to create
client-server applications. Because of this, there is a limit to
the number of records that Access returns in any recordset. This
limit is configurable, but you typically must build enough
filtering into your application so that you do not reach the
limit.

Access uses OLEDB to communicate with SQL Server. To provide the
Jet-like cursor behavior desired for desktop applications, Access
implements the Client Data Manager (CDM) as an additional layer
between Access and OLEDB.

Because of the layers required to get from Access to SQL Server in
the ADP architecture, it is often easier to optimize MDB/ACCDB
file solutions. However, there are some scenarios where a report
might be generated significantly faster in an ADP file. To add
these performance improvements and retain the flexibility of SQL
Server, you can build the majority of the application in an MDB or
ACCDB file and have the file load reports from a referenced ADP
file.

One advantage that ADP files have over files in MDB or ACCDB
format is the ability to make design changes to SQL Server
objects. ADP files include graphical designers for tables, views,
stored procedures, functions, and database diagrams.

Particularly note the first sentence of the third paragraph:

 

[Artificer]

If .adp will die them I will not use access. My only interest on it is
using it for client/server development!

 

[dbahooker]

Arvin

you are full of crap kid

SQL is always faster than Jet.

Consider BULK INSERT vs DoCmd.TransferText

ROFL

 

[dbahooker]

I've been using split forms in ADP for 10 years, bro

datasheet on top-- to find records

details at the bottom

MDB isn't fast enough to do things like this

 

[dbahooker]

David;

you are officially full of crap

MDB has been officially depecreated by microsoft
I posted the quote; MDB has been obsolete for 10 years-- from a MS
article

do you need me to find this quote again?

 

[dbahooker]

Because of this, there is an _OPTIONAL_ limit to
the number of records that Access returns in any recordset

I mean for real dude.
this is the BEST FEATURE OF ADP; THE SINGLE REASON I USE IT!