Upsizing design considerations

It seems I need a wizard just to figure how I need to deploy my future
database.

I’ve developed a number of RDBMS apps over the years using Paradox but now I
must create a new app using MSAccess. I’ve been reading Jennings “Using
Access 2003� and numerous web sites and find conflicting information I cannot
resolve.

I work at a university medical school and need to create a clinical database
of patients. This subspecialty division will never have more the 10 people
accessing the db, mostly non-concurrent, and might have 5000-15000 patients
to track - max. The university has a strong VPN and plenty of wired and
wireless opportunities to connect throughout the campus. I can easily get
server space and we have a school contract with MS for very inexpensive
software. All machines have XP Pro and use Access 2003. (I’d like to use
wireless Tablets in the future too.) Not sure the server OS.

I’m getting very confused determining whether I should use Access Jet for
the back end, or MSDE 2000 (or 2003 w/SP3) or SQLServer 2003. Should I stay
with Jet or use ADP? Security is an issue. At first I determined MSDE would
be the optimal solution for me but now I read elsewhere plain old Jet is
wonderful, reliable, and speedy for up to 25 users.

Very little documentation talk about flexibility to add and link tables
after the original app has been upsized since I expect a considerable amount
of modification after implementation. I expect a lot of tweaking
forms/reports and adding fields ongoing.

I read about a great number of tradeoffs between using Jet or ADP which, as
a new user, seem unusual to me - many queries need to be rewritten and many
forms don’t transfer well after upsizing.

I would like a solution that allows me the most iterative development
flexibility. I work from home, where I can access the VPN, and would like to
send new front ends to the users easily and work on the back end remotely.

Would it be better to have the forms on the server too and not worry about
delivering new forms to each user separately?

TIA
philr

I work from home, where I can access the VPN, and would like to
send new front ends to the users easily and work on the back end remotely.

You can't use split database over most VPN's. So, while you can "send" new
updates to your system via a VPN, you can NOT run a split database across a
VPN. If you can't get up to speed on these technologies, then you might
consider hiring some consulting help, as you really don't want to make a
mess here, and venturing into something that you don't have the correct
skill sets here (why put your reputation, and even your job at risk?).

Anyway, so save you some grief, you can read the following about ms-access
and deploying ms-access on a WAN or vpn:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Should I stay
with Jet or use ADP?

Hum, the above again, the hints at some confusing here. JET, and ADP are not
interchangeable. JET is a database. ADP is a particular type of ms-access
file that works with sql server. Note that you can use a mdb, and link the
tables to sql server (most of us developers prefer this approach over that
of a ADP - but, a good deal of explain is needed to decide which is a better
choice for a given situation). ADP's are more tighelty integrated with sql
server. One main advantage of a ADP is that the ADP includes tools to create
tables and users on sql sserver. So, a ADP is better for inexpainced users,
as writing poor quieres etc. will NOT slow down the speed of the
application, and you don't need to use the server side tools at first. So,
for some, if they are gooing to use sql server, then ADP is a possbile
choice. If you use a mdb, and link tables to sql-server, then you need to
use extra caution in buidling the sql. Fruther, you will also use the
"server side" tools to create the tables when you link a mdb. On the other
hand, even with a ADP since you ARE GOING to be use sql server, then you
likey should, and will have to learn some of the sql srever managment tools
anway. So, why not learn those tools anway? Furhter, creating a table in
sma-ccess, or creating one in sql server is really very simple thing.
Creating tables repsens a tiny fraction of the develpuemtn cost, so either
approach is not a big deal here at alll. On the other hand, desing of the
tables, and having correct relatonsthios and a normalied desint is the MOST
IPROANT thing to get right!!

Since you GOT to learn some of those tools, then you will by the very nature
of the development process use the server side tools to admin, and work with
the database anyway. The fact that a ADP has some tools to let you create a
table is of a very minor nature, and would NOT be a issue of deciding to
use a ADP project over using a mdb project that is linked to sql server.
So, both a mdb or a ADP can be used with sql server. It is not a either or
choice here.

Would it be better to have the forms on the server too and not worry
about
delivering new forms to each user separately?

The above question requitez that you, (or your devlopers) can understand the
diffence betwene an appcliaton, and a file. Often, people using ms-access do
not take time to learn the diffence between what is a file, and what is a
applation. To grasp this simple, but often overlooked convovpet, you can
read the follwing about using ms-access in a multi-user envoment.

http://www.members.shaw.ca/AlbertKal...plit/index.htm

You are going to be working with a good number of technologies here (sql
sever, ms-access, networking etc), and you might want to enlist some help at
the start of this project, as you want to get this right. A good source can
be other departments, or even students (for example, if you have a bunch of
students taking some courses where they learn sql server, then these people
can help you you to use, and setup sql server).

Finally, if you want to use wireless technologies, pda's, tablets etc, then
you likely need to bring in some consulting help. Each of the mentioned
disciplines such as ms-access, sql server, wireless technology, pda's etc
can take YEARS of learning for EACH discipline. In fact, you can spend 6
years just learning ms-access, and you will not near have mastered the
product (that is as long as most degrees that your institution offers! So,
while you could let first year students do some brain surgery...it is not a
very prudent approach...and you need to consider the same for ms-access).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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

Lets try this again with the spell checker!!

I work from home, where I can access the VPN, and would like to
send new front ends to the users easily and work on the back end remotely.

You can't use split database over most VPN's. So, while you can "send" new
updates to your system via a VPN, you can NOT run a split database across a
VPN. If you can't get up to speed on these technologies, then you might
consider hiring some consulting help, as you really don't want to make a
mess here, and venturing into something that you don't have the correct
skill sets here (why put your reputation, and even your job at risk?).

Anyway, so save you some grief, you can read the following about ms-access
and deploying ms-access on a WAN or vpn:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Should I stay
with Jet or use ADP?

Hum, the above again, the hints at some confusing here. JET, and ADP are not
interchangeable. JET is a database. ADP is a particular type of ms-access
file that works with sql server. Note that you can use a mdb, and link the
tables to sql server (most of us developers prefer this approach over that
of a ADP - but, a good deal of explain is needed to decide which is a better
choice for a given situation). ADP's are more tightly integrated with sql
server. One main advantage of a ADP is that the ADP includes tools to create
tables and users on sql server. So, a ADP is better for inexperienced users,
as writing poor quires etc. will NOT slow down the speed of the
application, and you don't need to use the server side tools at first. So,
for some, if they are going to use sql server, then ADP is a possible
choice. If you use a mdb, and link tables to sql-server, then you need to
use extra caution in building the sql. Further, you will also use the
"server side" tools to create the tables when you link a mdb. On the other
hand, even with a ADP since you ARE GOING to be use sql server, then you
likely should, and will have to learn some of the sql server management
tools
anyway. So, why not learn those tools anyway? Further, creating a table in
ms-access, or creating one in sql server is really very simple thing.
Creating tables represents a tiny fraction of the development cost, so
either
approach is not a big deal here at all. On the other hand, design of the
tables, and having correct relationships and a normalized design is the MOST
IMPORTANT thing to get right!!

Since you GOT to learn some of those tools, then you will by the very nature
of the development process use the server side tools to admin, and work with
the database anyway. The fact that a ADP has some tools to let you create a
table is of a very minor nature, and would NOT be a issue of deciding to
use a ADP project over using a mdb project that is linked to sql server.
So, both a mdb or a ADP can be used with sql server. It is not a either or
choice here.

Would it be better to have the forms on the server too and not worry
about
delivering new forms to each user separately?

The above question requires that you, (or your developers) can understand
the
difference between an application, and a file. Often, people using ms-access
do
not take time to learn the difference between what is a file, and what is a
application. To grasp this simple, but often overlooked concept, you can
read the following about using ms-access in a multi-user environment.

http://www.members.shaw.ca/AlbertKal...plit/index.htm

You are going to be working with a good number of technologies here (sql
sever, ms-access, networking etc), and you might want to enlist some help at
the start of this project, as you want to get this right. A good source can
be other departments, or even students (for example, if you have a bunch of
students taking some courses where they learn sql server, then these people
can help you to use, and setup sql server).

Finally, if you want to use wireless technologies, pda's, tablets etc, then
you likely need to bring in some consulting help. Each of the mentioned
disciplines such as ms-access, sql server, wireless technology, pda's etc
can take YEARS of learning for EACH discipline. In fact, you can spend 6
years just learning ms-access, and you will not near have mastered the
product (that is as long as most degrees that your institution offers! So,
while you could let first year students do some brain surgery...it is not a
very prudent approach...and you need to consider the same for ms-access).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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

Thanks Albert for your great answer.

I've read your separate web pages here and am scratching my head. I would
like to use SQLServer (MSDE?) on the server and create an easy to maintain
front end so our small staff can enter and access data. A thin client
solution would work fine but I don't have access (small a) to the tools - I
think? I thought of Access as a front end because it was easily available
but I'm not wedded to it. And as you say it's not reliable over a WAN.

I'm pretty sure the tech guys are running Windoows 2003 on their servers and
I can get access to it. Terminal Services seem to come with Windows2003
which I don't have. How does one design the tables/relationships/queries in
SqlServer remotely if not using Access and upsizing? What and where are the
"server side tools" you speak of?

I much prefer separate front ends and back ends which worked well for me
using Paradox and confusing in Access. Keeping a separate front end on each
client was a pain though. I guess I'll have to do more reading on Terminal
Services. Does this work with MSDE too? Will much change with SqlServer
2005?

Do you foresee any problems using a wireless notebook to access the VPN/WAN
and then SqlServer?

thx, philr

"philr" wrote in message
...
Thanks Albert for your great answer.

I've read your separate web pages here and am scratching my head. I would
like to use SQLServer (MSDE?) on the server and create an easy to maintain
front end so our small staff can enter and access data.

Yes, the above sounds good.

A thin client
solution would work fine but I don't have access (small a) to the tools -
I
think? I thought of Access as a front end because it was easily available
but I'm not wedded to it. And as you say it's not reliable over a WAN.

Well, a split database as a "file share" over a wan is not reliable, but in
my list of "possible solutions", I do suggest that using ms-access as a
front end to sql server (or the MSDE) is a workable approach. (sql server
can handle those really fast and minor disconnects that occur). So, you can
use a adp, or mdb with linked tables (in both cases...we still install the
application part on each pc).


I'm pretty sure the tech guys are running Windoows 2003 on their servers
and
I can get access to it. Terminal Services seem to come with Windows2003
which I don't have.

Actually, windows products starting at windows 2000 has terminal services
included. You can use two free remote users, but after that, you got to pay
for client licenses (it can get expensive quite fast). So, while I love
terminal services, and if you got a company say with 15 users, but only a
FEW need to use the software at home, or remotely on the road, then it is a
great solution. The first two remote users are free, and a few more users is
not too expensive. For larger numbers of remote users, then the cost gets
quite high very fast! A web based solution becomes a much cheaper on the
licensing side, but you incur much larger development costs.

By the way, depending on your expected number of users, and the fact that
you are planning to deal with wireless, and remote users, then a web
solution might be better then ms-access here (and, no deployment issues
exist).

How does one design the tables/relationships/queries in
SqlServer remotely if not using Access and upsizing? What and where are
the
"server side tools" you speak of?

Well, if you use a ADP, then you can create tables, and they are actually
created on the server. However, do note that if you have sql server, then
you got what is called the "client tools". These are the query analyzer, the
enterprise manager, and a few more goodies. So, the "table design" tools can
be found in the Enterprise Manager (EM). So, you can build and save quires
using the EM on your machine, but the resulting tables are of course are
saved on the sql server (you will find using the query builder in the EM
very much like ms-access query builder, actually somewhat nicer). So, if
you create a new query (these are called "VIEWS" in sql server lingo if you
are wondering), then that query will be saved on the sql server. And, these
tools work over a remote connection. So, if you have a vpn connection at
home to the sql server at work, then you can use these tools to manage sql
server. Typically, these "client" tools are installed on your machine. And,
to connect to sql server, you can actually use a IP address (so, unlike a
JET file share, you do not OPEN files across the network, but actually
connect via a socket connection to the sql server - this is similar in
concept as to how a web browser connections to a web site).

There is two ways to get the sql server client tools:

a) if you got a copy of sql server, then you can use the disks to
install ONLY the client tools on your desktop pc

b) download a trial edition of sql server, but do NOT install sql server
(use your free MSDE on the office cd). You take the trial edition of sql
server and ONLY install the client tools. It turns out that there is NO
expiry date on the client tools, but only the sql server!! (thus, you can
get all of the "big" corporate tools for free, and actually use the free
edition of sql server on the office cd (MSDE) at zero cost. Since the MSDE
is compatible with sql server, then the client tools work on this little
engine also!!


I much prefer separate front ends and back ends which worked well for me
using Paradox and confusing in Access. Keeping a separate front end on
each
client was a pain though.

Yes, I spent about 1 hour, and put in a bit of code to "check" for a new
version. (I simply have a table in the Front end with a version number,and
also a table in the back end with a version number. If they don't match...I
copy a new front end from the server). This kind of code was easy to write.
However, if you don't want to roll you own as many many developers have,
then you can grab Tony's free updater he

http://www.granite.ab.ca/access/autofe.htm


So, the above will solve the update problem once and for all.....

I guess I'll have to do more reading on Terminal
Services. Does this work with MSDE too?

Yes, TS does work with the above. Remember, TS means that NO software is
installed on each computer (except for the client software, this is like
installing a browser...but from that point on, all the rest of the software
is on the server). So, once done, you don't have to ever install any more
software on the client computers...but only on the server side.


Will much change with SqlServer
2005?

Well, actually, I would now likely choose the sql "express" edition of sql
server, as it is free, and has NO throttle like the MSDE (so, it don't have
a limit of 5-10 users..but can handle MANY more). You can find the free
edition he
download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

about:
http://www.microsoft.com/sql/express/


sql server 2005 comes with its own tools, and is thus likely a better choice
then trying to download, and install the sql server client tools.

I got a copy of sql 2005 express running on a test box, but have not used it
for anything yet. (I plan to as soon as I got a few minutes).


Do you foresee any problems using a wireless notebook to access the
VPN/WAN
and then SqlServer?

No, not really. As I mentioned, sql server is designed to work in these
cases, and a dropped connection might occur, but that dropping will NOT
damage your data files as it will with a jet file share. However, if you got
tablets, or pda's in mind here...then along with web tools, a .net solution
might be better (so, as your communication needs expand, ms-access is not
always the best choice).

If you only got 5, or 15 users, and have control over the machines, and they
are on the same LAN, then you can't touch ms-access for ease of use, and
deployment. However, as you expand, and want remote users, wireless stuff,
pda's, and more, then choices become increasingly more complex.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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

Access Front End option:
I guess I really would like to try Access on the Front End with MSDE on the
back end although I’m very interested in the Terminal Services option. My
hesitation might be my lack of SQL query creation knowledge.

Does either ADP or .mdb tables over a WAN provide a bigger benefit for
remotely updating forms/reports and queries?

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?

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?

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?

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.?

How much knowledge do I need of SQL to create queries with SQL Server’s
Query Builder?

Thanks,
philr