A good reason to use ADP's

[Jerry Boone]

A long time ago Larry Linson (MVP contributor to this group) made a point to
me that mdb's could do anything ADP's (Access Projects) could by using
linked tables to leverage Sql server databases. It's been years since then,
and I still sometimes think about that statement when evaluating the
usefulness of the ADP's I build. Larry and I discussed this technique and
he found it "Credible" (hope you don't mind me quoting you sir) so what the
hell... here it goes - this is for all you die-hard ADP guys (and gals of
course). I know there have been long, drawn out debates over the very
subject at hand, some I have read and been a participant... and perhaps this
recommendation of usage has even came to surface - however, I felt it had
not been mentioned before by the way Larry responded.

After working with ADP's since the birth of A2K, I have found many
advantages that suit me... but what about you? Please understand, this
thread is not an open invitation to bash ADP's (let's try something new),
but rather a constructive way to figure out what tricks people are using
that make them "valuable" - ok? So, without further delay... here is one
innovative trick out of my bag in which I have kept for myself for some
time... yours for free if it's new to you...

Note that ADP's connect to Sql Server using an OLEDB connection. This type
of connection can and will definitely span further than the local network,
in fact it can span to any IP Address that has port 1433 (Sql Server) open
and Sql Server/MSDE running. Perhaps the strongest advantage ADP's have in
my business is that I can deploy them to multiple locations while hosting
the database. For a monthly fee, customers get a managed database and do
not incur the expense of a server. This model simply fits the small branch
office scenario "like a glove". Then, using bound forms and intelligent
Stored Procedure design, I have a very tight, fast performing (by keeping
records down), rich, and believe it or not... a secure delivered ADE
application.

How many people have a website that uses a Sql Server database? If so, did
you know you can more than likely connect to it with Access as a Project and
do whatever you want with the database... as long as you have the
credentials? Most, if not all ISP's who host Sql Databases allow you to
connect with Enterprise Manager - you can use those same credentials to
connect your copy of Access. Work with Data, build new queries, build some
forms for maintaining data, deploy them as an alternative to building
complex, yet featureless web page based maintenance systems.

I believe this is new with AccessXP, but did you know that you can now link
many flavors of tables to ADP's? Yes, Paradox, dBase, MDB's, and any ODBC
or OLEDB accessible engine... to name a few. Look into it if you have a
need for this. You could always link some of these from Sql Server, but now
it's a right click and link option in table view.

Anyway - I hope this thread stays positive and uncovers some really good
features that ADP's provide to working developers.

Professional replies are appreciated.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

[Rick Brandt]

"Jerry Boone" <je***@antech.b iz.killspam> wrote in message
news:f0******** ***********@new ssvr11.news.pro digy.com...

A long time ago Larry Linson (MVP contributor to this group) made a point to me that mdb's could do anything ADP's (Access Projects) could by using
linked tables to leverage Sql server databases. It's been years since then, and I still sometimes think about that statement when evaluating the
usefulness of the ADP's I build. Larry and I discussed this technique and he found it "Credible" (hope you don't mind me quoting you sir) so what the hell... here it goes - this is for all you die-hard ADP guys (and gals of
course). I know there have been long, drawn out debates over the very
subject at hand, some I have read and been a participant... and perhaps this recommendation of usage has even came to surface - however, I felt it had
not been mentioned before by the way Larry responded.

After working with ADP's since the birth of A2K, I have found many
advantages that suit me... but what about you? Please understand, this
thread is not an open invitation to bash ADP's (let's try something new),
but rather a constructive way to figure out what tricks people are using
that make them "valuable" - ok? So, without further delay... here is one
innovative trick out of my bag in which I have kept for myself for some
time... yours for free if it's new to you...

Note that ADP's connect to Sql Server using an OLEDB connection. This type of connection can and will definitely span further than the local network, in fact it can span to any IP Address that has port 1433 (Sql Server) open and Sql Server/MSDE running. Perhaps the strongest advantage ADP's have in my business is that I can deploy them to multiple locations while hosting
the database. For a monthly fee, customers get a managed database and do
not incur the expense of a server. This model simply fits the small branch office scenario "like a glove". Then, using bound forms and intelligent
Stored Procedure design, I have a very tight, fast performing (by keeping
records down), rich, and believe it or not... a secure delivered ADE
application.

How many people have a website that uses a Sql Server database? If so, did you know you can more than likely connect to it with Access as a Project and do whatever you want with the database... as long as you have the
credentials? Most, if not all ISP's who host Sql Databases allow you to
connect with Enterprise Manager - you can use those same credentials to
connect your copy of Access. Work with Data, build new queries, build some forms for maintaining data, deploy them as an alternative to building
complex, yet featureless web page based maintenance systems.

I believe this is new with AccessXP, but did you know that you can now link many flavors of tables to ADP's? Yes, Paradox, dBase, MDB's, and any ODBC or OLEDB accessible engine... to name a few. Look into it if you have a
need for this. You could always link some of these from Sql Server, but now it's a right click and link option in table view.

Anyway - I hope this thread stays positive and uncovers some really good
features that ADP's provide to working developers.

Professional replies are appreciated.

Two points:

My intent is not to "bash ADPs" and I don't doubt that many features of an
ADP make connection to a server over the internet easier, but you seem to
be saying that only ADPs have this ability. Everything you're describing
about connecting to a server over the internet can also be done with ODBC
and an MDB file.

One is not limited to Jet queries over linked tables just because they are
using an ODBC/MDB file. I had an app in use for several years that
connected to our SQL Server over the internet and found that even with a
dial-up connection that it was quite usable. That was an app that lent
itself to small data transactions and nearly all of it was done using
Stored Procedures and Pass-Throughs.

Point two is concerned with why I had to completely redesign that same app
a couple of years ago to get away from ODBC and switch to using HTTP
requests talking to Java servlets. The reason being that I was informed by
our networking admin that leaving port 1433 open to the internet was a huge
security problem. This opinion was nearly unanimously agreed upon when I
made some Newsgroup inquiries about it. If that is in fact true then I
wonder what benefit there is to what you're describing if it will by
definition be lacking proper security.

I agree that the ability in newer versions to link to sources other than
SQL Server is a big plus. That was the primary reason I had little
interest in them when I first looked at them in A2000.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Jerry Boone]

Thanks Rick - very professional. My answers to your respectable points are
these...

Connecting and deploying with ADP's...
File-Connection-Fill in parameters
At this point all Sql objects are local to the IDE for design change and
will reflect design changes made to the database without updating the ADE.
The database name can be changed any time to connect to a different database
using the connection parameters. For instance, the same accounting system
has 4 different companies with identical database structure, the user can
pick which company by changing this one option and the ADP will adhere
everything across the board.

Connecting and deploying with Jet...
Devise ini or reg entry to create dsn in ODBC with parameters
Start Linking tables
At this point linked tables are there, but the tables must be relinked and
possibly re-deployment of the mdb (remote location and options are limited)
if updates are done on the tables. The database name needs to be fixed, or
else more code and efforts must be installed.

My intent is to make nothing local but the code and Access objects. Also...
local storage, I use local xml and atdg files that update once on app start
for that - works great.

Security - *"A number of industry pundits (and I) feel that Jet security is
an oxymoron. It seems that there are dozens of ways to crack Jet database
security -- there are even some Web sites that will do it for a small fee.
If you're concerned with security, switch to MSDE or SQL Server where you
will be confronted with another whole set of security issues, but where it
is a whole lot easier to maintain overall security of your database." The
issues he mentions are concerning the overwhelming amount of choices. By
what he has written, I would say that using Jet makes SQL Server less
secure.

Sql Server port 1433 has had one security incident that was made known. My
systems were never effected by it for two reasons -- I only permit 1433 to
clients with static ip's (reputable businesses) and enter them into my
firewall. On more secure systems, I shift the port as well as work the
firewall specifics. Security is not for the novice - it's an evolutionary
phenomenon that changes as technology does. What was a unsafe yesterday, is
safe or at least safer today. These questions should be evaluated
periodically, not just once.

The linking of databases was possible before within Sql Server in 7.0.
* From William Vaughn's latest book on ADO best practices - page 41, last
paragraph.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:bt******** ****@ID-98015.news.uni-berlin.de...

"Jerry Boone" <je***@antech.b iz.killspam> wrote in message
news:f0******** ***********@new ssvr11.news.pro digy.com...

A long time ago Larry Linson (MVP contributor to this group) made a point

to

me that mdb's could do anything ADP's (Access Projects) could by using
linked tables to leverage Sql server databases. It's been years since then,

and I still sometimes think about that statement when evaluating the
usefulness of the ADP's I build. Larry and I discussed this technique

and

he found it "Credible" (hope you don't mind me quoting you sir) so what

the

hell... here it goes - this is for all you die-hard ADP guys (and gals

of course). I know there have been long, drawn out debates over the very
subject at hand, some I have read and been a participant... and perhaps

this

recommendation of usage has even came to surface - however, I felt it had not been mentioned before by the way Larry responded.

After working with ADP's since the birth of A2K, I have found many
advantages that suit me... but what about you? Please understand, this
thread is not an open invitation to bash ADP's (let's try something new), but rather a constructive way to figure out what tricks people are using
that make them "valuable" - ok? So, without further delay... here is one innovative trick out of my bag in which I have kept for myself for some
time... yours for free if it's new to you...

Note that ADP's connect to Sql Server using an OLEDB connection. This

type

of connection can and will definitely span further than the local

network,

in fact it can span to any IP Address that has port 1433 (Sql Server)

open

and Sql Server/MSDE running. Perhaps the strongest advantage ADP's have

in

my business is that I can deploy them to multiple locations while hosting the database. For a monthly fee, customers get a managed database and do not incur the expense of a server. This model simply fits the small

branch

office scenario "like a glove". Then, using bound forms and intelligent
Stored Procedure design, I have a very tight, fast performing (by keeping records down), rich, and believe it or not... a secure delivered ADE
application.

How many people have a website that uses a Sql Server database? If so,

did

you know you can more than likely connect to it with Access as a Project

and

do whatever you want with the database... as long as you have the
credentials? Most, if not all ISP's who host Sql Databases allow you to
connect with Enterprise Manager - you can use those same credentials to
connect your copy of Access. Work with Data, build new queries, build

some

forms for maintaining data, deploy them as an alternative to building
complex, yet featureless web page based maintenance systems.

I believe this is new with AccessXP, but did you know that you can now

link

many flavors of tables to ADP's? Yes, Paradox, dBase, MDB's, and any

ODBC

or OLEDB accessible engine... to name a few. Look into it if you have a
need for this. You could always link some of these from Sql Server, but

now

it's a right click and link option in table view.

Anyway - I hope this thread stays positive and uncovers some really good
features that ADP's provide to working developers.

Professional replies are appreciated.

Two points:

My intent is not to "bash ADPs" and I don't doubt that many features of an
ADP make connection to a server over the internet easier, but you seem to
be saying that only ADPs have this ability. Everything you're describing
about connecting to a server over the internet can also be done with ODBC
and an MDB file.

One is not limited to Jet queries over linked tables just because they are
using an ODBC/MDB file. I had an app in use for several years that
connected to our SQL Server over the internet and found that even with a
dial-up connection that it was quite usable. That was an app that lent
itself to small data transactions and nearly all of it was done using
Stored Procedures and Pass-Throughs.

Point two is concerned with why I had to completely redesign that same app
a couple of years ago to get away from ODBC and switch to using HTTP
requests talking to Java servlets. The reason being that I was informed

by our networking admin that leaving port 1433 open to the internet was a huge security problem. This opinion was nearly unanimously agreed upon when I
made some Newsgroup inquiries about it. If that is in fact true then I
wonder what benefit there is to what you're describing if it will by
definition be lacking proper security.

I agree that the ability in newer versions to link to sources other than
SQL Server is a big plus. That was the primary reason I had little
interest in them when I first looked at them in A2000.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Larry Linson]

"Jerry Boone" wrote

A long time ago Larry Linson ... (hope you
don't mind me quoting you sir)

No, I don't mind at all, Jerry... I'm happy to see an enlightening technical
discussion.

I'm wondering how this fits with the Microsoft insiders' apparent current
agreement that "MDB-ODBC beats ADP-ADODB" -- maybe one of those insiders
will join in this discussion.

As Rick said, I knew you _could_ connect via the Internet with MDB-ODBC, but
I never thought it was practical from either a performance or a security
point of view. An open port to the Internet strikes me, as it apparently
struck Rick's admin friend, as a lot riskier than an open port on a LAN/WAN.

In my experience, Access clients using MDB-Jet-ODBC do not rely on Jet
security to any degree; it is the server database that provides the
security. A major app on which I worked off and on for several years had an
Access 2.0 client -- no MDE and security with a hole big enough to drive a
freight train through -- but the Informix server security was pretty good.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

bo*****@localho st.not (Larry Linson) wrote in
<Xc************ *******@nwrddc0 2.gnilink.net>:

"Jerry Boone" wrote

A long time ago Larry Linson ... (hope you
don't mind me quoting you sir)

No, I don't mind at all, Jerry... I'm happy to see an enlightening
technical discussion.

I'm wondering how this fits with the Microsoft insiders' apparent
current agreement that "MDB-ODBC beats ADP-ADODB" -- maybe one of
those insiders will join in this discussion.

As Rick said, I knew you _could_ connect via the Internet with
MDB-ODBC, but I never thought it was practical from either a
performance or a security point of view. An open port to the
Internet strikes me, as it apparently struck Rick's admin friend,
as a lot riskier than an open port on a LAN/WAN.

Well, wouldn't a VPN eliminate that issue? Why would anyone deploy
such a solution in any other fashion than with a VPN?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

"Rick Brandt" <ri*********@ho tmail.com> wrote in news:btcmfr$5mk g2$1@ID-
98015.news.uni-berlin.de:

Point two is concerned with why I had to completely redesign that same app
a couple of years ago to get away from ODBC and switch to using HTTP
requests talking to Java servlets. The reason being that I was informed by
our networking admin that leaving port 1433 open to the internet was a huge
security problem. This opinion was nearly unanimously agreed upon when I
made some Newsgroup inquiries about it. If that is in fact true then I
wonder what benefit there is to what you're describing if it will by
definition be lacking proper security.

The server administrator can set the port which the SQL server uses to
something other than the default 1433.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Rick Brandt]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .90...

bo*****@localho st.not (Larry Linson) wrote in
<Xc************ *******@nwrddc0 2.gnilink.net>:

"Jerry Boone" wrote

A long time ago Larry Linson ... (hope you
don't mind me quoting you sir)

No, I don't mind at all, Jerry... I'm happy to see an enlightening
technical discussion.

I'm wondering how this fits with the Microsoft insiders' apparent
current agreement that "MDB-ODBC beats ADP-ADODB" -- maybe one of
those insiders will join in this discussion.

As Rick said, I knew you _could_ connect via the Internet with
MDB-ODBC, but I never thought it was practical from either a
performance or a security point of view. An open port to the
Internet strikes me, as it apparently struck Rick's admin friend,
as a lot riskier than an open port on a LAN/WAN.

Well, wouldn't a VPN eliminate that issue? Why would anyone deploy
such a solution in any other fashion than with a VPN?

On our LAN we only allow VPNs when used with SecuRemote software and whatever
you call that little device that changes pass numbers every 60 seconds. This is
limited to a handful of power users so they can do some work remotely. It would
have been a nightmare to set that up for the few hundred remote users that
connect to the database (I have my hands full enough just getting an Access app
installed and running properly). As these remote users are independently
contracted, not employees, I'm not sure that setting them up with a network
profile would ever have been given serious consideration.

I was surprised by the security issue myself when it was raised. I had always
figured that SQL Server had "real" security and therefore could be trusted. The
fact that people think it is not safe when exposed to the internet implies to me
that it is not secure from users on our LAN either.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Albert D. Kallal]

"Jerry Boone" <je***@antech.b iz.killspam> wrote in message
news:1D******** *********@newss vr24.news.prodi gy.com...

Connecting and deploying with Jet...
Devise ini or reg entry to create dsn in ODBC with parameters
Start Linking tables At this point linked tables are there, but the tables must be relinked and
possibly re-deployment of the mdb
No, you don't need to re-deploy the application at all.

And good developer will ALWAYS use a dsn-less connection.

In fact a few software places have the
question of how to make DSN less connection on the test
when they are hiring you for a VB, or ms-access
position!

Anyway, here is a nice piece of free code by Mr. Steele
that simply lets you enter the server name, and the database
name, and it re-links. So, you don't have to re-deploy
or do anything:

http://members.rogers.com/douglas.j....LessLinks.html

Now, the fact that we can set up code to link, and allow the database
and server name to be easily changed does not in any throw out your
point that it is easier to change the connection to a database
with a adp project. However, you most certainly can build a
JET to sql application, and have a means to easily change the
server name and database name.
Security - *"A number of industry pundits (and I) feel that Jet security is an oxymoron.
Sure, but both of us are not talking about using JET to store the data. We
are talking about placing data on properly secured sql server. The debate
is here not that a sever is more secure then is JET, but the debate is do
you get more security from a adp project to a sql server then do you with
JET to that same sql server?

I have not seen any real augments that would make a adp project more secure
then a JET odbc to that same sql server.
By what he has written, I would say that using Jet makes SQL Server less
secure.

Gee, I can't really say there any difference here. There is perhaps a few
issues in terms of where/how the table links to the server are stored,
but I would certainly need more debate on this one.

As mentioned, I do think for new projects that are destined to run with sql
server, a adp project is a great idea. It also forces all tables and data
to be on the server (this is *usually* a good thing!) With a JET based
system,
it is somewhat easer to put data on the client side! (so, forced all data
to be kept server side is not ALWAYS the ticket)!

In some discussions with developers I have seen some ultra low bandwidth
requirements for adp projects. I cannot say the same low bandwidth results
would be achieved with jet/odbc, but then again, I don't see why not.

Without question, sql server gives you MUCH better connectivity here.
However,
that ability extends to both JET/odbc and adp projects. In fact, using sql
server can be cheaper then using a Terminal Services to a jet file share on
a server! (so, in fact, usng sql server and a thick client (either adp, or
jet/odbc) can be used in place of termial services)).

If you are writing from day one with sql server in mind, or need correct
security,
then I do think that using a adp project makes sense. adp's also do as
you mention are more seamless with sql server.

After taking to some very happy developers who use adp projects, I in no way
want to discourage the use of adp projects . On the other hand, few of us
are giving up our lovely little JET engine either!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
No************@ msn.com
http://www.attcanada.net/~kallal.msn

[Albert D. Kallal]

"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:btdc5a$60s km$1@ID-

On our LAN we only allow VPNs
As David mentioned, for any type of connection to anything, a VPN quite
standard. Throw in the server authentication stuff, and it is quite good.

The fact that people think it is not safe when exposed to the internet
implies to me that it is not secure from users on our LAN either.

Well, the one big advantage of sql server is that it is integrated with
windows security. That means the same permissions that the domain server has
can also be used by sql server.

So, while that port is opened, the security settings do apply (well,
actually it
depends on how the sql server is setup, but sql server can use the users
logon).

However, this whole post of mine does bring up the point is a oleDB
connection to sql server (via a adp project) more secure then is jet/odbc
connection?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
No************@ msn.com
http://www.attcanada.net/~kallal.msn

[Jerry Boone]

DSN-less strings -- yes, good, I use them every time I open a connection.
Except with ADP's in which the current project connection can be reused.
The article you present is a nice way to re-link tables if the db name
changes, I had not entertained that thought. Still, as you point out - it's
not simply taken care of for you. The need for me to deploy mdb's no longer
exists, so there are some things that I am not razor sharp on.

On the security item - I will try to come up with some good technical info,
but I think you starting to ask the same questions I am after reading your
more recent post.

Starting from day one, with a Sql Server is the place for adp's -- however,
it's one handy tool in systems that have sql server and you just need to
quickly browse some data. I frequently have visited customer sites with Sql
accounting and mfg systems that have vb or c front ends. Nothing... makes
it easier to view and analyze the data than using a adp. It also is
invaluable when planning and building web applications from existing sql
server databases. The same applies of course to existing sql server web
databases located at the clients ISP. No linking required.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Albert D. Kallal" <pl************ ********@msn.co m> wrote in message
news:t7vKb.9472 $hk.20@pd7tw1no ...

"Jerry Boone" <je***@antech.b iz.killspam> wrote in message
news:1D******** *********@newss vr24.news.prodi gy.com...

Connecting and deploying with Jet...
Devise ini or reg entry to create dsn in ODBC with parameters
Start Linking tables

At this point linked tables are there, but the tables must be relinked and possibly re-deployment of the mdb

No, you don't need to re-deploy the application at all.

And good developer will ALWAYS use a dsn-less connection.

In fact a few software places have the
question of how to make DSN less connection on the test
when they are hiring you for a VB, or ms-access
position!

Anyway, here is a nice piece of free code by Mr. Steele
that simply lets you enter the server name, and the database
name, and it re-links. So, you don't have to re-deploy
or do anything:

http://members.rogers.com/douglas.j....LessLinks.html

Now, the fact that we can set up code to link, and allow the database
and server name to be easily changed does not in any throw out your
point that it is easier to change the connection to a database
with a adp project. However, you most certainly can build a
JET to sql application, and have a means to easily change the
server name and database name.

Security - *"A number of industry pundits (and I) feel that Jet security

is

an oxymoron.

Sure, but both of us are not talking about using JET to store the data. We
are talking about placing data on properly secured sql server. The debate
is here not that a sever is more secure then is JET, but the debate is do
you get more security from a adp project to a sql server then do you with
JET to that same sql server?

I have not seen any real augments that would make a adp project more

secure then a JET odbc to that same sql server.

By what he has written, I would say that using Jet makes SQL Server less
secure.
Gee, I can't really say there any difference here. There is perhaps a few
issues in terms of where/how the table links to the server are stored,
but I would certainly need more debate on this one.

As mentioned, I do think for new projects that are destined to run with

sql server, a adp project is a great idea. It also forces all tables and data
to be on the server (this is *usually* a good thing!) With a JET based
system,
it is somewhat easer to put data on the client side! (so, forced all data
to be kept server side is not ALWAYS the ticket)!

In some discussions with developers I have seen some ultra low bandwidth
requirements for adp projects. I cannot say the same low bandwidth results
would be achieved with jet/odbc, but then again, I don't see why not.

Without question, sql server gives you MUCH better connectivity here.
However,
that ability extends to both JET/odbc and adp projects. In fact, using sql
server can be cheaper then using a Terminal Services to a jet file share on a server! (so, in fact, usng sql server and a thick client (either adp, or
jet/odbc) can be used in place of termial services)).

If you are writing from day one with sql server in mind, or need correct
security,
then I do think that using a adp project makes sense. adp's also do as
you mention are more seamless with sql server.

After taking to some very happy developers who use adp projects, I in no way want to discourage the use of adp projects . On the other hand, few of us
are giving up our lovely little JET engine either!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
No************@ msn.com
http://www.attcanada.net/~kallal.msn