A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Upsizing design considerations



 
 
Thread Tools Display Modes
  #1  
Old September 5th, 2005, 09:42 PM
philr
external usenet poster
 
Posts: n/a
Default 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
  #2  
Old September 5th, 2005, 11:37 PM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default

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


  #3  
Old September 6th, 2005, 12:01 AM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default

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




  #4  
Old September 6th, 2005, 06:25 AM
philr
external usenet poster
 
Posts: n/a
Default

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
  #5  
Old September 6th, 2005, 09:40 AM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default

"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


  #6  
Old September 7th, 2005, 01:17 AM
philr
external usenet poster
 
Posts: n/a
Default

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

  #7  
Old September 7th, 2005, 05:14 AM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default


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 he

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/AlbertKal...des2/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.

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

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



  #8  
Old September 9th, 2005, 06:55 PM
philr
external usenet poster
 
Posts: n/a
Default

I went to my local Barnes & Noble to look up more information on using
Terminal Services with SQL Server. A look through the index of the books
available didn’t mention TS. Is there another keyword or term or a recommend
book that can teach me this rdbms distribution method? Other resources?

If I use Access ADP with SQL Server do I lose any query capabilities? The
book I’m reading covers Jet SQL but, under ADP, queries apparently turn into
the da Vinci toolset. During ADP development does Access handle these query
differences behind the scenes so I don't need to think much about the
differences or do I need to find another reference to understand the
differences? IOW, does Access create the T-Sql for automatically?

From an experience POV, how would I best set up my hard drive to segregate
files for this development? IOW, as stated previously, I want to do
development work from home and eventually load the data onto a B.E. SQL
Server elsewhere and send the ADP Front End to the users elsewhere on the WAN
at later dates.

If I deliver the BE and FE in encrypted format do I have to re-encrypt with
each deployment? Do I need to just encrypt the BE data?

Thanks,
Philr

  #9  
Old September 10th, 2005, 12:53 AM
Albert D.Kallal
external usenet poster
 
Posts: n/a
Default

"philr" wrote in message
...
I went to my local Barnes & Noble to look up more information on using
Terminal Services with SQL Server. A look through the index of the books
available didn't mention TS. Is there another keyword or term or a
recommend
book that can teach me this rdbms distribution method? Other resources?


Well, TS is a basic system that runs on windows 2000 server. So, it is not
specific to sql server, or any particular software application for that
matter. So, just like we can't find a book on using terminal services and
ms-word, I doubt you will find one for TS + sql server. So, you will not
find a book specific to "one" particular application when using TS. So, TS
works with virally ANY software you can run on the desktop. I doubt you will
find a book on using word, and TS. However, you most certainly should be
able to find a book on terminal services. (some books that cover
administration of windows 2000, or 2003 server likely would cover TS). I
mean, you can use windows 2000 as a domain server (that simply means that
all computers logging on to the network get ALL OF their security from
windows 2000 server). If your domain server goes down, and NO one can
actually log on to their computers! My point here is that domain servers are
big part of the windows server system, but I don't think you will find a
book JUST on domain servers for windows - well, ok...probably on Amazon you
can, but I think you see my point here).

The home page for TS is he

http://www.microsoft.com/windows2000...l/default.mspx

So, from the above, you can find white papers, links to articles, and even
newsgroups....


If I use Access ADP with SQL Server do I lose any query capabilities? The
book I'm reading covers Jet SQL but, under ADP, queries apparently turn
into
the da Vinci toolset. During ADP development does Access handle these
query
differences behind the scenes so I don't need to think much about the
differences or do I need to find another reference to understand the
differences?


Hum, well, you do loose the ability to use VBA in sql expressions. That is
about the largest change. Otherwise, not much difference here. The general
approach to build the queries will be much the same as you have now.
Perhaps the largest difference is that you can NOT using VBA expressions in
the sql when you use a adp (this is because he query runs native on the sql
server, and thus no VBA expressions can be executed). When you use a mdb,
then you can write public functions, and they can be used in sql
expressions. And, about the only other issue here is that a saved regular
select query a mdb application is a "view" in sql server. So, I can't say
that a ADP handles the queries differently, but some functions, and
expression will be slightly different. The overall process of building a
query remains the same, and the query builder will result in a some sql.

IOW, does Access create the T-Sql for automatically?


No, ms-access does not create the t-sql (but, why would it?). You certainly
can however write stored procedures in t-sql, but that assumes you now have
spent the time to learn t-sql. (so, sure, you can write t-sql procedures)

From an experience POV, how would I best set up my hard drive to segregate
files for this development?


Hum, I don't have any specify tips. I guess the issue is that you would
likely run a local copy of sql server for testing, and development. Before
you deploy, you would change the connection. You would have to deal with
things like adding a new table to your test database, and then remembering
to "copy", or create the same table on the production database (but, always
had to deal with this in a split system anyway). Sql server is better, in
that you can generate DDL scripts to create the table on a different
machine. On the other hand, exporting, and import would be possible, since
you have a connection to both sql servers (you local development one, and
the production one at work - got quite a few options, and even bulk copy can
be used with the sql admin tools).


If I deliver the BE and FE in encrypted format do I have to re-encrypt
with
each deployment?


We need to clarify what you mean by encrypted. I as a general rule do NOT
encrypt the mde (FE), as then it will not zip, or compress. However, if you
mean do you compile the mdb to a mde each time? Well, yes, of couse you
re-compile each time as that is the ONLY way to make a mde. You can't make a
new mde (or ade) without re-compiling the applcation. (again, this comes
down to you having worked with a split system for a awhile....if you have
not, then kind need t start doing this, you have to learn to walk before you
run).

Do I need to just encrypt the BE data?


Assuming the data will reside on sql server, then no encryption is needed.
It is assumed that users will NOT have permission to where the back end
files are located. However, users will most certainly have permissions to
the sql server. What you let them see, and use will be up to you (you could
always do that in ms-access anyway). I mean, the general rule is that if
users can see the data..then they can take it....right? (what stops you from
cutting and pasting data from the internet movie database?). So, the data on
sql server is not encrypted in anyway. The only difference between a backed
that is a mdb file, and backend that is sql server, is that with sql server,
you can REMOVE permissions to the directory where the files are from users
(so, sql server lets you hide where the actual data files are, and users
never get to see that directory). However, users will STILL need some
permissions to the tables and data used on sql server. (if they don't have
permissions, then how can they use the data!!). Don't confuse database
permissions with that of how you design your software, and what you let
users see, or not see (they are similar, but different issues).

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

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


  #10  
Old September 12th, 2005, 06:51 AM
philr
external usenet poster
 
Posts: n/a
Default

Thanks again.

I bring up the issue of encryption because I work in the health care field
and have to deal with something called HIPAA in the states which sets a high
bar for privacy. There is some worry, maybe unjustified, that data on the
server needs to be non-viewable by server administrators. Does using the
username log in keep out wandering eyes here? (I realize talented people can
crack CIA databases, but does it offer reasonable safeguards so no one on the
back end can just click and open the files without a determined effort?)

I realize this is an Access form but do you recommend any good books on SQL
Server and ADP solutions?

Thanks,
philr
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Customised design templates 2003 Mary Ann Powerpoint 3 April 25th, 2005 01:18 PM
how do i apply an Axis design or radial design from design templa. nadia Powerpoint 1 April 3rd, 2005 02:21 AM
Form view / Design view - looses chart changes? KIM Using Forms 1 March 11th, 2005 04:47 AM
Design Templates don't apply font sizes consistantly Greg H Powerpoint 1 September 15th, 2004 02:07 PM
opening a document so it is NOT in design mode Brad Pears New Users 1 May 3rd, 2004 09:13 PM


All times are GMT +1. The time now is 10:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.