Does either ADP or .mdb tables over a WAN provide a bigger benefit for
remotely updating forms/reports and queries?
No, they both are really the same in this regards. To update code, forms, or
reports, with a ADP, or mdb (actually, it should be mde, or a ADE when you
deploy), then you simply copy out a new copy of the so called Front End
(FE) to each computer. This process does not change one bit if you use a
mdb/mde, or a
ADP/ADE project. Your uses will be running the production copy on THEIR
computers, and you are likely working on the next great release of the
software. So, no, I don't see any differences here as to using a ADP, or mdb
for updating.
If I use Access ADP on the front end with SQL Server on the back end
linking
both should be easy. How would I send new forms, queries, etc. to the end
users if I'm connected remotely (via VPN) and they are on the WAN back
bone
and I don't have access to their computers?
Well, how do you update them now? The normal solution is to have the FE
check
for a new update. If you don't want to spend a 1 hour writing some simple
code to copy the new mde (or adp), then simply use Tony's FE updater, as it
can be found here:
http://www.granite.ab.ca/access/autofe.htm
I don't know if the above works with a adp, but all that updater does is
COPY a file to your computer. So, to answer your question:
To update users, you simply get them a new copy of the front end. This
FE might be a mbe (if using a mdb), or ade (if using adp).
This is not a complex concept, or process here.
You simply need to copy a file to the target computer and they are updated!
(perhaps you not worked in a split environment before...but you will!!).
The easy solution is
thus to have your code check for a update. Heck, some lazy people simply run
a
windows batch file at startup that ALWAYS copies the mde from the server to
the local machine. Gee, you can't get simpler then that!! However, I think
this
kind of a waste to "copy" the program each time a user logs on, but I guess
it is a simple approach. No need to knock yourself out on this one!!
Another approach is to put the mde file on a simple web page. This is what I
do for clients of mine. If you take a look at the following screen shot, I
have a "about" box. Take NOTE OF the "Download/update Rides "button.
http://www.members.shaw.ca/AlbertKallal/rides2/index.htm
So, in this case, I actually have to tell the users you need to upgrade (I
did not want this to be automatic - for some clients, when I deploy to the
network, it is automatic, but not this example!). So, I simply email them,
and
tell them that a update is ready, and to use the help->about option to
upgrade.
The code behind the update button is again VERY simple (takes less time to
write that code then to write this post!!!).
Dim strExe As String
If MsgBox("This will download the lastest version of Rides" & vbCrLf & _
"Do you want to update Rides?", vbQuestion + vbYesNoCancel,
AppName) <> vbYes Then
Exit Sub
End If
' ok...start the update....
strExe = Left(strWebSupport, InStrRev(strWebSupport, "/")) &
"ridesxp.exe"
Application.FollowHyperlink strExe
Application.Quit
I count...what 3 lines of code!! This is not a big project concept here. The
above string "strExe" if examined would be:
http://www.kallal.ca/SomeDirecotryYouUse/ridexxp.exe"
Note the next line of code is a application.quit (I exit the application).
So, that hyperlink simply starts a download. Note that ridesxp.exe is
simply a mde file wrapped in a zip file (I used to use
www.winzip.com, and
if you register the product, then you can create self-extracting zip
files). However, even better is to use the free open source installer. I now
use Inno.
http://www.jrsoftware.org/isinfo.php
So, you got really tons of choices here A web update like above, Tony's
free updater, or spend a 1 hour or so, and roll your own, or use Inno
(inno is my favorite now, and I use it all the time) . These approaches
are all reasonable choices, and which is better for you can only be known by
you!!
However, do remember that ALL OF the above choices are simply copying
a mde file to the target computer...nothing more, nothing less...
Do I create the application in Access, upsize, and somehow declare my
front
end to be ADP? Is data encryption handled entirely in the background?
As mention, you do need to spend some time considering if you are going to
use a ADP, or mdb. It does NOT make sense to develop in a mdb, and then
upsize that to a ADP. If you already got a EXTENSIVE application as a
mdb,then
you would LINK the tables to sql server, and NOT USE a adp (the reasons is
that a lot of things are different in a ADP and lots of your code will NOT
work). If you keep the application as a mdb, then a FAR HIGHER % of the code
will work, and not have to be changed. The only reason to use ADP is that
is
what you stared the application with in the first place. In other words, it
makes NO sense at all to build as a mdb, and then convert/upsize to a ADP.
If you are planning to use a ADP, then USE ONE from the start, and you will
not have to convert one thing!!!
If you got a existing (complex) application as a mdb, then you better leave
it
as a mdb, and simply link the tables to sql server. (again, I said SEVERAL
times, the reasons for using one, or the other will DEPEND on your
situation).
But, you certainly do NOT want to start
out with a mdb with the intention of converting to a adp. If you just got a
few tables, then you can use the sql server tools to import data from a mdb
anyway. (DTS tools). You DON'T HAVE to use the forms wizard to make forms,
nor do you have to use the upsizing wizard to transfer your tables to sql
server.
Remember, we are talking about a box that has some tables, and it is called
sql server. You don't write your application with sql server, as it is JUST
a
box to hold tables. So, you create your application, but your tables can be
in a JET back end, or a sql server back end. The mdb front end
will change very little, if at all in this case. If you use a ADP, then of
course moving from the MSDE to the full blown version of sql server means
that NO code or NOTHING need be changed. So, using a ADP forces
you to work with sql server right from the start, and that can be a good
thing
if you are going to work with sql server.
Web based option
If Terminal Services comes with each copy of Windows 2000 (I assume also
with XP) why would I need to worry about a license restriction of 2? Or
did
you mean SQL Server 2000? If there is a restriction, is this for 2 total
users or concurrent?
Actually I meant windows 2000 server, not sql server. Remember TS is a
remote
system that creates virtual users on ONE box. So, the first two remote users
(concurrent - each with the OWN separate desktop etc) are free (if you use
the admin mode of the server). After that, a licensing system kicks in to
ensure that those multiple users have in fact legitimate licenses for the
software they are running (in other words, you can get away having two
remote
users + one person actually working on the actually server keyboard for a
total of 3 users). They are all working at the same time, and all using the
same software (and all get their own desktop). So, to allow more users,
some very impressive licensing systems kick in, and you there fore have
to purchase additional licenses to run those extra users.
Since our campus uses Microsoft almost exclusively, can SQL Server be used
as a "web service" backend with tools other than Enterprise Manager, Query
Builder, and Terminal Services? Ie Cold Fusion, ASP, etc.?
SQL server is not a web server. If you run a web server, then that web
server can connect to sql sever. And, in fact, you can actually have that
web server connect to a mdb file (if it is sitting on the server). So, as a
matter of preference, yes, you will have your web server (that supplies web
pages to users) connect to sql server for its data needs. That sql server
can often be on the same box as the web server, but depending on the load,
and
for reasons of maintains etc, sql server is often run on a separate box.
Of course, if you are using a web server, and sql server, then you don't
need
ms-access at all here...do you? ms-access will do nothing for this setup at
all. You might as well use asp forms, and even
better is to use the .net tools, and write for the web from day one...
How much knowledge do I need of SQL to create queries with SQL Server's
Query Builder?
No more then what you need with ms-access. As mentioned, I find very little,
if any difference in effort required to use either query builder that is
supplied with each product. If you know the ms-access query builder, then
I don't see more then a few minutes of time to start using the sql one.
