[AccessD] MDB on the web

To make sure I answer all of your questions, I will reply >>>inline.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, February 14, 2008 5:24 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] MDB on the web

Hi Drew:

That's good to know. Are all these intranet mdbs have web based
interfaces?
>>>Yes.  In fact for the past several years, 99% of the databases I have
built have a web interface.  The only major system I have built that has
a non-web interface is our helpdesk system.  It has a VB interface,
because it has abilities which would be a nightmare to try to
incorporate into a web interface (one example of such a capability is
that it allows our users to create a snapshot of their desktop, or any
open window when creating a request.  Due to IE security, interaction
with the desktop level can be problematic)

What type of connections do you use?
>>>Since both our Intranet and Internet sites are hosted internally, and
I have direct access to the IIS servers, 95% of these systems use a .dll
to use the database (made in VB).  I know I'm a little behind the time,
because most people are using .Net (both VB and ASP), but I have so many
tools that make developing these projects easily, there's no real
advantage to switch.  If I do use ONLY asp (again, I have tools to work
with databases, in fact I posted one a few weeks ago.  It builds a
'connection' object function, and asp 'classes' for any table/query), I
use the Jet.4.0 OLEDB provider in ADO.  Another item to note, 95% of the
systems I have in place are Access 97 .mdbs.  In fact, I am in the
middle of building my first BIG system that will use a 2000 format
database (same provider either way).

adOpenUnspecified, adOpenForwardOnly, adOpenKeyset, adOpenDynamic or
adOpenStatic?
>>>OpenKeyset

What session timeouts do you use or do you use any? 
>>>Depends on the system.  I'd say most of my systems don't use a
session timeout.  Of course, the pages I build are 'hit and run' types,
where they pull their data and then close the database.  There are a few
systems where I use the IIS session timeouts, one such system is our
website, you can log in (to order stuff on our website), and that system
is set to time out I think in 15 minutes, maybe 30, I built it years
ago.  However, it's 'invisible' to the end user, because I put a session
cookie on the end user's machine so that if the session times out, it
automatically logs the user back in, if they still have the same browser
open.

Is the connection string set something like this?:

Set constr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
              Server.MapPath("/mdb/mydatabase.mdb") 
>>>I must admit, I rarely use an actual connection string.  Typically
I'll have something like this:
Function DBConnect(cnn as ADODB.Connection)
Set cnn=new ADODB.Connection (with asp, you'd have to use
Server.CreateObject)
Cnn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnn.open "E:\IntranetMDB\SomeDatabase.mdb" 'I hard code locations.  You
can do that with a hosted provider too, because I've never seen one
actually physically change the path of files you store on their systems
End function


Is there any reason why only MDBs have been used, other than "If it
ain't
broke don't fix it"?
>>>Several reasons:
#1.  Speed.  No matter how you slice it, a local .mdb is almost always
going to be faster then a server side db.  Server side db's certainly
have advantages, but with those advantages comes the cost of more
overhead.  If the server side db is running on the webserver, the speed
is going to be close, but if it's on another server, you have the delay
of handing off the processing.  Now, this 'advantage' is reversed when
it comes to VERY massive data sites.  If you need to query millions of
records, a dedicated server is probably going to work faster making the
'hand off' time negligible.  But then again, returning millions of
records is going to be a pain to a web user.

#2.  Cost.  Obviously MYSQL wouldn't apply here, but if we were to use a
server side db in house here, it would be SQL Server (though we do have
an Oracle DB).  Access is an absolute freebie since we have Access in
our Office Suite anyways. (And the webserver doesn't even need a license
to use the .mdb)

#3.  Personal Choice.  I cut my teeth on databases with Access.  I can
develop a database/table structure blind folded in Access.

#4.  Backups.  This is a plus and a minus. I can backup any database on
our Intranet or Internet site by simply copying the .mdb.  However, our
tape backups balk every so often when backing up these .mdbs (they won't
back it up if the .mdb is open).  Since every system I have developed is
setup with the 'hit and run' approach, a web user has to be actually
loading a page at the exact moment the backup is trying to backup the
mdb, so our backups are at least 90% successful with the .mdbs.

#5.  Portability.  Using an .mdb, with the Jet 4.0 provider in ADO, I
can move my web sites to any IIS machine (and every operating system
since Windows 98 has it's own asp capable IIS server).  No need to have
a database engine also running on that machine.  This comes in real
handy with development.

#6.  SQL.  Kind of another personal choice.  But every database engine
has it's unique quirks when it comes to SQL.  For example, I can use:

SELECT * FROM tblX WHERE DateField=#1/1/2008#

That won't work on a SQL Server.  Having the ability to use the same
query in both the live system and an access system is a must.  (the only
quirk is that ADO uses % instead of * for a wild card).

>>>To be continued


Would you have any other suggestions or recommendations? 

TIA
Jim

The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.