Access FE with SQL Server BE or .NET FE and SQL Server BE

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I've been programming in Access for a little over a year now; used the app
for several years prior to doing development work. I am just getting into
SQL Server and .NET now. I am wondering what the advantages/disadvantages of
the following two scenarios are:

#1)
Use Access as a FE and SQL Server as a BE

#2)
Use .NET as a FE and and SQL Server as a BE

I would think .NET would be much better for web-based applications. I've
done a fair amount of IIS and ASP.NET work, drilling into Access Queries, and
had great results with that.

Appreciate any insight.

Thanks,
Ryan---
 
See inline comment...

ryguy7272 said:
I've been programming in Access for a little over a year now; used the app
for several years prior to doing development work. I am just getting into
SQL Server and .NET now. I am wondering what the advantages/disadvantages of
the following two scenarios are:

#1)
Use Access as a FE and SQL Server as a BE


#2)
Use [ASP].NET as a FE and and SQL Server as a BE

I would think .NET would be much better for web-based applications. I've
done a fair amount of IIS and ASP.NET work, drilling into Access Queries, and
had great results with that.
Click to expand...

Given that I've lived, breathed, and slept ASP.NET for the past six months
(having worked with Access as a front end on and off for ten years), my own
personal preference is for ASP.NET. However, there's been a huge learning
curve which is just now paying off. Pretty much things aren't as obvious as
you would think and I have yet to find a ASP.NET newsgroup/discussion group
that's as helpful as the MS Office groups. (Typically, a response, if any,
takes days to get.)

I believe that the real power of ASP.NET is seen when its married with DHTML
and CSS. For example, in Access if you have a continuous listing Customers,
you can't have a subform showing their open orders which you can do within
ASP.NET to any number of nested 'subforms' so something like this is
possible...

John Smith
Lisa Jones
Mike Holley
---Order #15
---Order #16
------Item #231
------Item #235
---Order #17
Ronald Carter

Where you click on the person's name and the subdetail expands or collapses.
Within Access, Conditional Formatting is limited to three specific
conditions which can at times be difficult to work with if the algorithm is
rather complex. (I was pulling out my hair this time last summer with that
one.) In ASP.NET, you have full control over how a row in a 'form' is
rendered making the conditional formatting much easier to manage. You can
even make a row in a continuous 'form' highlight as the mouse moves over it
as in the example here...
http://www.dhtmlgoodies.com/scripts/highlight-table-row/highlight-table-row.html

One particularly nice thing is that when you blend ASP.NET, DHTML and CSS
you can give your users with a replacement for a listbox or comboBox where
each individual record can contain multiple lines. An example would be
providing a list of events with event numbers and dates as in...

Bruce Springsteen Concert
Madison, WI - Tues 6/10/2009
---
Bruce Springsteen Concert
Madison, WI - Wed 6/11/2009
---
Bruce Springsteen Concert
Madison, WI - Thur 6/12/2009
---
Cher
Chicago, IL - Thur 6/12/2009

I could probably go on and on and on...easier deployment, anywhere access,
and all around coolness.
 
For example, in Access if you have a continuous listing Customers,
you can't have a subform showing their open orders which you can do within
ASP.NET to any number of nested 'subforms' so something like this is
possible...
Click to expand...

Sure you can. You don't actually "nest" the sub-form, you simply just
place it on the same form.

There is a screen shot of this here:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Look at the very last screen shot. It is a classic screen in which you split
out
donation funds. So, the "many" people are on the left side of the form, and
on the right is the "many" accounts you split the donation out to.

Dropping two sub-forms on a main form is still A COUNTRY MILE less work then
doing this in asp.net. Because their are two sub-forms, you have to add
one meager line of code to the on-current event of the 1st form
(me.Parent.Child1.Requery) to force the 2nd form to update.
One particularly nice thing is that when you blend ASP.NET, DHTML and CSS
you can give your users with a replacement for a listbox or comboBox where
each individual record can contain multiple lines. An example would be
providing a list of events with event numbers and dates as in...

Bruce Springsteen Concert
Madison, WI - Tues 6/10/2009
---
Click to expand...

We do the same thing in ms-access by using a continues form. Again, it drag
and drop. And, not only would that give us two lines of display, each line
can have things like combo box, check box etc. Try coding that in asp.net!!

Again, if you look at the above screen shots, the difference between a list
box
and a continues form is HARD to distinguish. In the above screen shots, a
continues form was used because we wanted a check box in the list.

So, if we want more then one line of detail, then one just uses a continues
form in place of a listbox. What we DO miss is the ability to have each row
expand or contract. that is difficult, but having two lines of detail for
each row is a piece of cake in ms-access.

Keep in mind with our new image control in 2007, we can even have continues
forms that display different graphic images for each row (and, we can do
this without ANY CODE at all). Again, how can you display a list like
a grid with edit buttons, and furthermore and have each row show a
different picture without whacks of code in asp.net? This is not even
close in terms of work.

Note that with the new anchoring of controls in 2007 is
very much like what a web form and you when now re-size the form, you
do see re-sizing of text boxes just like a web page and how anchoring
works in asp.net. (the anchoring is the same concept in access now).

I be the 1st to admit that html screens can often have more flexible
displays for text type data then what we have in ms-access.

However, at the end of the day building a sub sub as above or having
two lines of detail in a continues form (with buttons etc. that repeat)
you find that access runs absolute circles around coding and building
of these things compared to asp.net.

Some say you have add a "zero" on the cost of your project when you move
it from access to asp.net That means $500 of access work becomes
about $5000 of work in asp.net. I don't think the factor is that large, but
using 4-5 times the amount of labor and time is a REASONABLE figure
to get the same functionality out of a asp.net page for editing some data
as to what we creating every day in access with drag and drop.
 
Albert D. Kallal said:
Sure you can. You don't actually "nest" the sub-form, you simply just
place it on the same form.

There is a screen shot of this here:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Look at the very last screen shot. It is a classic screen in which you split
out
donation funds. So, the "many" people are on the left side of the form, and
on the right is the "many" accounts you split the donation out to.
Click to expand...
I was thinking in terms of this...
http://www.codeproject.com/KB/webforms/MasterDetail.aspx

And of course you could do the same thing with the last example.
Dropping two sub-forms on a main form is still A COUNTRY MILE less work then
doing this in asp.net. Because their are two sub-forms, you have to add
one meager line of code to the on-current event of the 1st form
(me.Parent.Child1.Requery) to force the 2nd form to update.
Click to expand...
Yes, it is much, much easier. But you get what you pay for. Once I got the
concept of nesting under my belt, I simply copied and pasted the code from
one pagae to another. If you want to discuss number of lines of code, I will
admit that ASP.NET requires 300% more effort to nest controls with a total of
3 lines of code.
We do the same thing in ms-access by using a continues form. Again, it drag
and drop. And, not only would that give us two lines of display, each line
can have things like combo box, check box etc.
Click to expand...
If you reread the post, you'll see that I was discussing that in listbox or
combobox, its one line in the list per record. If you need to display
additional data for the records, you have to rely on columns.
In Access it would be
Name City State
John Smith New York NY
---
Lisa Jones Atlanta GA

Using ASP.NET...
John Smith
New York, NY
---
Lisa Jones
Atlanta, GA

Remember, we're not discussing continuous forms were the controls are
stacked top to bottom, We're discussing how information in a LISTBOX or
COMBOBOX is displayed.
Try coding that in asp.net!!
Click to expand...
Been there done that. I've already discussed publishing it on
4GuysFromRolla.com as soon as I write it up.
 
This is kind of what I thought; learned a few new things too. Any additional
thoughts?
Thanks everyone!!
Ryan---
 
If you pursue ASP.NET, I'd look into taking a community college course in C#
to get you familiar with the general syntax and several key concepts. I had
taken a course in Java years ago which set down a very nice foundation.

I'd also recommend a course that covers HTML, DHTML and CSS. I had built a
Classic ASP site back in 2002 which relied very heavily on DHTML & CSS so was
already head of the game when I started playing with ASP.NET.

In general, ASP.NET is such that you can get a decent, no-frills site up and
running fairly easily, however the coolness takes time. Case in point, just
today I started playing with AJAX controls haven't had any issues (so far)
adding them into existing sites.

If you don't have Visual Studio. MS does have VS Web Developer Express
Edition available for free as well as SQL Server Express.

(Oh and buy the way, if you go with a web front end you can send out links
to people that will automatically take them to a specific record...its one of
those questions that occasionally crops up in this newsgroup.)
 
ryguy7272 said:
#2)
Use .NET as a FE and and SQL Server as a BE

I would think .NET would be much better for web-based applications. I've
done a fair amount of IIS and ASP.NET work, drilling into Access Queries, and
had great results with that.
Click to expand...

But how much longer did it take you to create the same app in .Net vs
Access? Now granted .Net has it's advantages such as multi tier and
better version tools and works better for multiple developers. But
otherwise?

Tony
 
It did take longer especially due to the HUGE learning curve.

In the future, will going with ASP.NET take longer than building something
in Access, YES. But I strongly believe that the control that ASP.NET provides
is well worth it.

You don't see major corporations using Access as a front-end to their
enterprise critical applications.
 
Remember, we're not discussing continuous forms were the controls are
stacked top to bottom, We're discussing how information in a LISTBOX or
COMBOBOX is displayed.
Click to expand...

Yes, I understand that, but my whole point is that we "often" use a
continues form in ms-access in place of a listbox. When we do so, we get
check boxes, combo boxes, and even "edit" buttons. We can also use that
continues form to "select" individual records which in "most" cases is what
a
list box is used form. So, sure, you taking talking about a listbox, but for
what reason would one not use a continuous form when you need those extra
features you speak of? That is my point. A text box that dispalys two lines
in a continuous forms simply repeats for us.

I am not looking to throw any kind of cold water on your points made here.
At the end of the day the .net environment is a rich and fabulous
development platform. However, you have to pay for that extra time and
effort.

The other point is that was important, is that people often refer that
you can't have two continuous forms to model the "many to many" as a
sub-form. (and you pointed this out). I simply pointed out that the simple
act of placing the two continuous forms side by side gives you the same
results And, as I pointed out it only adds one line of code to the whole
process.

Again, I just wanted to point out this is not really a limitation
of access, and again compared to other development products in the
marketplace, access runs absolute circles around other development products
in terms of setting up this type of relation and data modeling of
relationship between two tables on a form. And since these gridds are
true access forms, then we have a huge number of form events such as
before update, after update, before delete, on current etc. This list
of events for a grid contorl is rather large, and the same list for
a standard form, and also means a reduction in the learning curve also.

The other significant point I was making that our continuous forms have ALL
OF the rich form controls that we have at our disposal, so we really have a
great grid control, in many ways better than the flex grid control and most
grid controls in the marketplace today. Any button, check box, combo box,
and now even the new image control will repeat over and over for us, and it
is also data bound for us.

At the end of the day, I'm not trying to compare MS access to the.net
development environment. That would be comparing apples and oranges.

However, on the other hand when you are building screens to edit and work
with data..... access is really a fabulous product to build those data edit
screens, and furthermore it far less work.

A good developer in access will produce applications significantly faster
and with MORE functionality as long as you keep within the restrictions of
the kind of development and interfaces that you need.
 
To be honest, I wouldn't be the least surprised if the next evolution of
Access involved some sort of hybridization that combines the ease of use of
the current GUI with the flexibility provided by a web-based environment.

*NOT* that the developer would have to learn HTML, DHTML, CSS and ASP.NET,
but rather that the design environment does all the work for the developer
whereby he/she continues to drag and drop controls whereever he/she wants
them. The developer then having the choice of which language to use for the
application - Access VBA, VB or C# with the finished app still running within
Access due to the QUERIES and REPORTS. Naturally, such a hybridization would
lead to the ability to convert the forms to a pure ASP.NET solution just as
an Access backend can be upsized to SQL Server.
 
I have VB 2008 Express, Visual Web Developer 2008 Express, and SQL Server
2008 Express. I’m experimenting with all of these now. Lots to learn…

Thanks for everything,
Ryan---
 
David H said:
It did take longer especially due to the HUGE learning curve.

In the future, will going with ASP.NET take longer than building something
in Access, YES. But I strongly believe that the control that ASP.NET provides
is well worth it.
Click to expand...

What kind of control?
You don't see major corporations using Access as a front-end to their
enterprise critical applications.
Click to expand...

Yeah, that's because they're written in COBOL. And I certainly see
mid sized companies running mission critical apps in Access. ERP
controlling a welding shop with hundreds of employees. Timekeeping
app for a construction company with up to 1000 employees.

Tony
 
Tony Toews said:
What kind of control?
Click to expand...
Primarily over the look and feel of the UI, but also over the entire
environment.
Yeah, that's because they're written in COBOL. And I certainly see
mid sized companies running mission critical apps in Access. ERP
controlling a welding shop with hundreds of employees. Timekeeping
app for a construction company with up to 1000 employees.
Click to expand...

We have the 'Great Legacy Application Purge of the Millennium' to thank for
introducing applications built on more recently developed languages. The
'green screen' isn't as widely used as you would think. While Access for a
small business may work, you also have to factor in the issue of whether or
not it can support the long term growth of the company. There's a reason why
'Gap Analysis' is a part of any project to replace an application.
 
David H said:
While Access for a
small business may work, you also have to factor in the issue of whether or
not it can support the long term growth of the company. There's a reason why
'Gap Analysis' is a part of any project to replace an application.
Click to expand...

Define small business. I have clients with 500 or 1000 employees
running mission critical apps on Access. I've done work for Fortune
100 companies in Access. I was working at North America's largest
ever industrial construction project for a few weeks building an app.
At that point they were down from a peak of 15,000 employees to only
1,500. And in a few more weeks they archived the app and data to
never be used again.

Tony
 
Access or DotNet App as front end is like buying a Dell, or making your own
computer piece by piece. With a Dell, you know all the components have been
tested to work together, while with the other solution, maybe your choice
will bring more or less compatible components together. And your level of
expertise does not need to be the same, for some kind of work you may have
in mind.

In fact, with Access, you can do some milleage without writing a single line
of code, after all, but with DotNet, even if you chose the right components
right at the start, you are quite likely to need more than just plugging
'properties' in a visual environment (even if it is possible to buit some
basic app doing just that, and one or two graphical 'tables' (entities) )
but since DotNet does NOT assume you want to work with a database, while
Access does, you may find that Access 'hold your hand' and 'guide' you in a
much friendlier manner.

That being said, the Access development environment (VBE, not VBA, but VBE)
is becoming an 'old lady', in comparison with, say Visual Studio.
Futhermore, investing in learning VBA is not as profitable as it was few
years ago, when VBA was everywhere: the VBA leverage is not as evident as it
was. Futhermore, all the 'big names' promoting VBA have left: who write book
about using such and such acronym with VBA anymore ? or even
promote/explain COM and the use of classes with VBA? tell you what is a
Dictionary, or even a user made Collection? A great advantage about ACCESS,
though, is that people who wrote it were already knowing that it would deal
with data, and all those concepts are already 'made' for you: you only have
to use them, not to 'develop' them, and thus, the need to really know all
that is not as great as if you were starting from scratch. Still the
comparison between buying a Dell versus making yourself a PC from discrete
pieces.


Vanderghast, Access MVP
 
Back
Top