Access vs... ASP 1

[bdfigler]

Any thoughts?

There's a push at my company to change our internal data entry/storage system to an ASP/SQL-Server model. I have been very vocal about using Access as a front-end instead of ASP (SQL Server would backend nevertheless). We are using Access now (though not well) so employees accessing/using the software isn't a problem. My reason for pushing Access is simply that it will take less time (read: money) to design. Also, it can run faster for some operations because lookup tables and form information is stored locally. Before I make my final push, I would like to get some feedback here.

BTW, I am posting on this forum (rather than another Access forum) because I think users here might have some more perspective on ASP/programming issues.

Thanks in advance. -Brad

 

[Databaseguy]

ASP is great for Web pages. The other plus is that any user in the company that has a browser can get at your data through the companies intranet, and no Run time or complied versions are required.

Unless you’re really good at ASP though, my opinion is that Access will have more capability. Of course, I'm an ASP beginner and an Access veteran, so my opinion may be biased.
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[JimHorton]

Another biased opinion towards Access:
The whole 'All-Internet-All-the-Time' push has been overdone, in my opinion, by some companies. The primary reason for going to Browser-based apps was so their employees could access it from anywhere. There is definately some merit to this, but if you have a decent IT staff, why couldn't they access the VB or Access front-end/Oracle-SQL7 backend just as easy? Read on.

You need an internet connection for both--done. You have some initial setup for the VB, or Access, and upgrades. So what. I ran a 5 meg setup.exe over the internet in a matter of minutes the other day for the app I'm developing. Many of the java 'applets' think backwards and assume that everyone is on the internet with a 2400 baud modem (one of the main planks of java). Many now, and soon most connections will be at speeds around as fast as the typical hard-drive was when java was developed--java, with all its compromises to get around slow connections. Not forward thinking in my opinon. Yes, java it filled and is still filling a niche while the internet was growing. But now, you can run a 5 meg setup.exe over the internet in a minute or two and--voila! You have a rich, user friendly, FAST VB app connecting to a server, rather than a clunky Single-Document Interface (read ' compromised') app, along with that awful delay that, even over fast connections, is inherent with the browser. And the whole SDI--I dislike an app that won't allow me to have several forms open at once--if I have to hit the 'Back' button 3 times (and WAIT) to see the customer info that was an alt-tab away with a vb app, I see it as a big step backwards.

OK, so the hardware/OS must be uniform. But most companies that I've worked with don't say "Let's have the order-takers on the left side of the building use macs, those across the hall on the right side can use PC/Windows, and hey, lets give the sales force laptops with linux...and the Customer service dept--they can use 5250 Terminals! What a great idea!" And as far as 'compatibility' goes, who hasn't seen the error/warning messages: "you must have Netscape 5.0 to run this" or is it IE 4.0? Or Netscape 6.1? Ooops, you don't have the proper plug-in. "Error-Java-classes not installed".. etc. etc, etc. But at least the browser is secure... Oh, I forgot about all those holes in IE5, most Netscape versions, etc, etc. 'Sandbox' my ear.

The internet and the browser are truly great, especially for what we're doing right now, or for a mass-market company doing B-to-C (like Amazon) or a company's B-to-B apps, who need a quick and dirty order-entry screen for the unknown 'other side', who *will* be on macs, suns, linux boxes, and PC's. But if you're developing an in-house app for a known set of people and hardware, why compromise it when you don't have to?
--Jim

 

[Quehay]

Another angle:

I'm approaching the same threshold. A primary consideration: Access KILLS a WAN if you attempt to pull data, since you've got both encoded data and filesharing (i.e., send the whole recordset and then I'll figure the results of the SQL--although if you have SQL Server BE this isn't the case).

Also, changes to a Front End have to be distributed to clients. This is a real maintenance hassle. If you've got a pure web client situation then interface changes are updated for all seamlessly.

With a web client you are only sending text, and thus can have a user hit a local .mdb across the WAN without the bandwidth crunch.

It's worth looking at the Access Data Pages. Like FrontPage they allow creation of dynamic pages and data input without the need for advanced ASP skills (and similarly they create code that will make the ASP-adept scream because it can't be edited effectively).

It would be great to hear any success stories out there...

 

[JimHorton]

QueHay,
In my example, though, I"m *not* talking about access as the backend--it's Oracle, with vb or Access frontend, and a VPN connection through the internet (or, for the office-based machines) a simple Lan connection.

Here's a succes story--I created an Access frontend with Oracle backend for a major firms Customer service phone center. Most reps were office based, but many were home based with dialups (a VPN was in the thought process at the time), and the Lan app could fetch a record from the db of nearly 2 million records in well below 1/4 second--they had the option of Like or "=", and even with Like, they'd be returned with cursory info in a form of say, 30 J% Doe's, before their finger left the enter key. Upon double clicking the one they wanted, a full form with a subform, selecting from Call Records for that customer (call record table with 5-6 Mil records) would be up in 1/2 sec.

The same app, when I'd do some development at home, *over a 28,800 modem*, would return the forms in about 1 second. This was 3 years ago, I'm not sure if they've gone from dial-up for their home-reps to a VPN, with DSL or Cable Modem, but as you can see, the response will only get faster. Can the web do that?

So my point was that, with the right architecture, an Access/Oracle or VB/Oracle app is as fast as you could want, and *always* richer and more flexible (from a programming standpoint) than anything a browser could ever hope to be.
--Jim

 

[Quehay]

Thanks Jim,

Your work scenario is so similar to mine I'd almost swear you were working in the same company (Network and VPN users). This is encouraging! I've been installing project-scale Access FE/BE combinations in LAN scenarios, but we're ultimately moving to an Oracle solution (company standard for all other db's).

Your arguments for the greater depth of true client vs. web app are right on the money--I have to remember this when I get in a room with the web-mongers. Access gives a wealth of automation in Office (some of it just through menus), and the multiple forms argument is a good one.

If you're inclined to share your experience I'd like to hear your treatment of ODBC vs. ADO and pass-through SQL, native tables, reports, etc. There's a lot to decide when switching to the Oracle BE.

Thanks,

Jeff

 

[JimHorton]

Jeff,
There's alot to explain, I'll write more later on this. But one thing about odbc and Ado--it's the same pipe, so one is not going to give greater throughput than the other, Ado just has some more bells and whistles, but they are important ones in some cases The app I mentioned used both simultaneously, but that was just because when I wrote it, Ado was still fairly new, slower, and had fewer features than it does now--I added some ado a year or so ago when doing some upgrades.

Think of odbc vs. ado like a garden hose spigot. The same spigot on the front of your house gives the same total 'bandwidth', as in gallons per minute, but ado is like having one of those fancy sprayers with 6 different spray patterns, where with odbc you've got to put your thumb over the end of the hose to get a certain type of spray. But they'll both fill a bucket in the same amount of time.

A key reason I went to ado was for the parameters--Not stored procedure parameters, since you can pass parameters in a call to a stored procedure in a passthru query with odbc,(though you can't return a value). I'm talking about query parameters, such as for a recordset.

In the search routine, which was the very most used part of my app--the rep picks up the phone, the customer NEVER knows their customer ID, so they give a name, phone#, etc. Orignally, I was just building the sql, say:
"Select * from Cutstomer where FirstName Like 'J%' AND LastName = 'Jones'"
This is all very well, but every single time Oracle (or any rdbms) sees an sql statment, it must hit it's data dictionary, check to see the table exists, the fields exists, the syntax is right, then develop an execution plan--UNLESS--unless the query has already been checked and 'passed' these checks. It only knows this by the an exact string comparison of what's in the sql shared pool. If J Jones called every time, no problem. But everyone's different, so with parameters, the value of the parameter isn't checked, just the statement, so now:
"Select * from Cutstomer where FirstName Like ? AND LastName = ?'"
That statement never changes, and the time savings is noticeable when you're talking about getting 1/4 second response--to have to do all those other syntax checks, etc, is an eternity. It's not a problem when you're running a report that's taking a minute or two anyway, but for a phone center, it's everything.

So I just created 5 or 6 'canned' sql strings put into ado command objects, for when the cust gave the phone#, the city, etc. Then at startup, I'd be sneaky and while the user was sipping his coffee and waiting for the app to start, I'd run each sql behind the scenes to 'preload' the shared pool. That way no users, not even the first one to open the app, had to wait for any of the common search queries to do these checks.
Anyway, if you want any more specifics, let me know...
--Jim

 

[bdfigler]

You guys are talking about ADO/ODBC connections, but as far as I know Access 2000 can 'link' to tables from a SQLS7 DB (and the ADO connection is implicit). When a recordset object is created from one of these linked tables, is Access requesting the entire table and then picking through it or is it requesting just the recordset?