Access to remote SQL server

[Macsmasher]

Hi everyone,

Developing in Access 07. All users will be using Runtime.

I've been developing multi-user apps with Access for many years using Access
as both he front-end and back-end. I also did a stint as a database admin
for a bank, and we used ODBC to connect to our local Microsoft SQL server.
However, I have never created an app to connect to a remote SQL database on a
web server. Couple of questions...

1. Is Access the best solution for the front-end? I developed one app where
the User connected to a remote Access back-end and it was dreadfully slow
even with the server on a T1 and the Users on cable. Will connecting to an
SQL back-end speed things up?

2. If I do stick with Access for the front-end, is ODBC the best (most
efficient) way to connect?

3. And what about using VS to create everything server side with an HTML UI
and not use Access at all?

Thanks in advance for your time and input.

Larry
Maximize Software, Inc.

 

[Banana]

Hi everyone,

Developing in Access 07. All users will be using Runtime.

I've been developing multi-user apps with Access for many years using Access
as both he front-end and back-end. I also did a stint as a database admin
for a bank, and we used ODBC to connect to our local Microsoft SQL server.
However, I have never created an app to connect to a remote SQL database on a
web server. Couple of questions...

1. Is Access the best solution for the front-end? I developed one app where
the User connected to a remote Access back-end and it was dreadfully slow
even with the server on a T1 and the Users on cable. Will connecting to an
SQL back-end speed things up?

Access is a wonderful and powerful front-end that can be used for many
different scenarios, and certainly can be used with a remote server. The
problems you observed with a Access backend does not apply here because
of the architecture differences between Access (rather, JET or ACE, the
engine driving Access) and the server-based RDBMS such as SQL Server,
MySQL and Oracle.

However, a common misconception is that simply by changing backend
performance will magically improve. This is false and in fact may be
opposite. What ultimately matters is the database design. I discuss this
at high level at this: tinyurl.com/ODBCGuide

On aside- I"m a bit concerned... It is not usual for SQL Server to be on
the web server... This sounds like a security risk to me. Usually, when
SQL Server is used behind web server, it's hidden to the public and only
the web server itself can access SQL Server. For serving applications
(e.g. Access front-ends), VPN is usually used so it's never necessary to
expose SQL Server (or any other server-based RDBMS) to the internet.
Maybe I've read into too much and apologies if I did.

2. If I do stick with Access for the front-end, is ODBC the best (most
efficient) way to connect?

Depends on the backend. In case of SQL Server, ODBC is implemented
natively so there is very little difference in whether you use ODBC,
OLEDB, or ADO.NET to connect to SQL Server. Working within Access, you
can only use ODBC via UI, either ODBC or OLEDB via VBA, and ADO.NET is
not an option at all. The only differences is how various
functionalities are exposed.

FWIW- when working with server-based RDBMS backend, I usually use
DAO/ODBC 95% of the time, and in 5% use ADO when I need something like
using stored procedure to bind a form _and_ have it be updatable.

3. And what about using VS to create everything server side with an HTML UI
and not use Access at all?

That's an option, but remember the Access's selling point is rapid
development. Doing this in VS with HTML UI will take more time and
provide less functionality than a rich client. Also, be aware that by
using HTML, you now have to deal with providing not only data but also
the layout content, which makes for more network traffic.

I'm sorry but there's not really enough info or idea of what is the
right tool for your needs. Hopefully what I've given gives you some idea
or at least ask more questions. As I've said, Access is an excellent
tool that has capability to fill many many niches but whether it is of
any worth ultimately depends on your design requirements.

HTH.

 

[David C. Holley]

Access is a wonderful and powerful front-end that can be used for many
different scenarios, and certainly can be used with a remote server. The
problems you observed with a Access backend does not apply here because of
the architecture differences between Access (rather, JET or ACE, the
engine driving Access) and the server-based RDBMS such as SQL Server,
MySQL and Oracle.

However, a common misconception is that simply by changing backend
performance will magically improve. This is false and in fact may be
opposite. What ultimately matters is the database design. I discuss this
at high level at this: tinyurl.com/ODBCGuide

On aside- I"m a bit concerned... It is not usual for SQL Server to be on
the web server... This sounds like a security risk to me. Usually, when
SQL Server is used behind web server, it's hidden to the public and only
the web server itself can access SQL Server. For serving applications
(e.g. Access front-ends), VPN is usually used so it's never necessary to
expose SQL Server (or any other server-based RDBMS) to the internet. Maybe
I've read into too much and apologies if I did.

Access connecting to a SQLServer DB on a server running IIS doesn't present
any [additional] security issues than if the DB is on a dedicated server and
not running IIS. The security issues don't compound simply because Access is
connecting to a DB that's the backend for a website. However, that should
not be taken to presume that the security measures that would be implemented
for a server running IIS would be the same as those not running IIS or would
superceded them. Conceptually, IIS and the pages that run under it are
nothing more than just another front-end just like Access.

Depends on the backend. In case of SQL Server, ODBC is implemented
natively so there is very little difference in whether you use ODBC,
OLEDB, or ADO.NET to connect to SQL Server. Working within Access, you can
only use ODBC via UI, either ODBC or OLEDB via VBA, and ADO.NET is not an
option at all. The only differences is how various functionalities are
exposed.

FWIW- when working with server-based RDBMS backend, I usually use DAO/ODBC
95% of the time, and in 5% use ADO when I need something like using stored
procedure to bind a form _and_ have it be updatable.


That's an option, but remember the Access's selling point is rapid
development. Doing this in VS with HTML UI will take more time and provide
less functionality than a rich client. Also, be aware that by using HTML,
you now have to deal with providing not only data but also the layout
content, which makes for more network traffic.

Yes, Access's selling point is rapid development, however there are things
that you can do with ASP.NET that you can't do with Access. It can be just
as functional as an Access front end if not moreso - for example processing
credit card payments or working with API's from various well known sites
that are out there. Any additional network traffic due to delivering the
HTML to the client would be negligble overall even if there's a high volumne
of network traffic, the impact shouldn't be such as to disqualify ASP.NET
across the board. Also, with the advent of AJAX, its now possible to create
web pages that don't flicker when a post back occurrs creating an experience
comprable to a Windows app. The other obvious advantage is that if you have
to update a page its just a matter of dropping it onto the production
server, unlike Access which requires infrastructure to distribute the
update.

It should be noted that ASP.NET has many controls that are simply drag and
drop allowing simple web sites to be created quickly.

 

[Banana]

Access connecting to a SQLServer DB on a server running IIS doesn't present
any [additional] security issues than if the DB is on a dedicated server and
not running IIS. The security issues don't compound simply because Access is
connecting to a DB that's the backend for a website. However, that should
not be taken to presume that the security measures that would be implemented
for a server running IIS would be the same as those not running IIS or would
superceded them. Conceptually, IIS and the pages that run under it are
nothing more than just another front-end just like Access.

FWIW, I've heard recommendations that database be kept separate from web
server to minimize exposure should the web server be breached. Maybe
this is no longer true, and to be clear, we're in agreement that
IIS/ASP.NET and Access are just two different front-end to the SQL
Server, but the security measures are different and would be even more
risky if they were all on a single server because once a point is
breached, the other access points could then turn into more vectors to
breach even deeper into the infrastructure. If I'm mistaken about this,
I'd love to know more.

Yes, Access's selling point is rapid development, however there are things
that you can do with ASP.NET that you can't do with Access. It can be just
as functional as an Access front end if not moreso - for example processing
credit card payments or working with API's from various well known sites
that are out there.

Maybe I'm missing the point, but those doesn't stuck me as something
that Access cannot do. Access certainly can use APIs and with a bit of
grease, interact with web services as well. Indeed, there has been demos
of Access using Google maps for example. Not to say it'd be easy as in
ASP.NET (because I don't know and would not want to presume so).
However, if the requirement is that people must be able to use the
application without downloading anything or maybe that it be available
on any platform, then that's a point where rich clients such as Access
fall short.

Any additional network traffic due to delivering the
HTML to the client would be negligble overall even if there's a high volumne
of network traffic, the impact shouldn't be such as to disqualify ASP.NET
across the board.

I wasn't suggesting that, but this is a consideration to put in the
mind. The OP didn't give enough information about the scope of project
and what users would be doing it and the load so I tried to be complete
as possible in highlighting the difference between a rich client and web
client. Network bandwidth happens to be one of traditional bottlenecks
for any data centric applications so depending on the volume of data and
users we're looking at, this can have important ramifications.

As they say, 'every dog has its day'. Sometimes it's Access. Sometimes
it's ASP.NET. Sometimes it's entirely something else. It would help OP a
lot to get into the functional requirements then it will become clear
whether it makes more sense to use ASP.NET or Access or heck, just a
plain old Excel spreadsheet for all I know!

Also, with the advent of AJAX, its now possible to create
web pages that don't flicker when a post back occurrs creating an experience
comprable to a Windows app. The other obvious advantage is that if you have
to update a page its just a matter of dropping it onto the production
server, unlike Access which requires infrastructure to distribute the
update.

That is very valid point- deployment certainly is much easier with
ASP.NET than with any rich clients.

It should be noted that ASP.NET has many controls that are simply drag and
drop allowing simple web sites to be created quickly.

That is true. If you don't mind me asking- how easy is it write custom
scripts? What about number of events available for the controls?

 

[David C. Holley]

Access connecting to a SQLServer DB on a server running IIS doesn't
present any [additional] security issues than if the DB is on a dedicated
server and not running IIS. The security issues don't compound simply
because Access is connecting to a DB that's the backend for a website.
However, that should not be taken to presume that the security measures
that would be implemented for a server running IIS would be the same as
those not running IIS or would superceded them. Conceptually, IIS and the
pages that run under it are nothing more than just another front-end just
like Access.

FWIW, I've heard recommendations that database be kept separate from web
server to minimize exposure should the web server be breached. Maybe this
is no longer true, and to be clear, we're in agreement that IIS/ASP.NET
and Access are just two different front-end to the SQL Server, but the
security measures are different and would be even more risky if they were
all on a single server because once a point is breached, the other access
points could then turn into more vectors to breach even deeper into the
infrastructure. If I'm mistaken about this, I'd love to know more.

Isolating the web server from the rest of the network is certainly an
intelligent precaution, but at some point you're going to have to access it
in order to get new pages onto the site, let alone if the Admin needs to
work with anything on the server. Also, there's the practical issue of
working with the data. How do you add records to a products table? How do
you pull orders entered on via the site into whichever DB you use to fulfill
them?

And yes, it increases the vectors, but that's where risk analysis is going
to come into play and that $150,000 you're paying your Network Admin to
secure your data. Unfortunately, my worth isn't because I'm a NetAdmin but
on the Business Analyst side of things. (Kneel before your god, and pay head
to all that he decrees! Thou shalt follow the requirements set forth before
you. Thou shalt not cause, encourage, or support scope creep. Thou shalt
deliver thy product on time, on budget and bug free.) But I would suspect
that it comes down to the rights on the dictories and those granted to the
various roles that are out there. I also seem to recall that virtual
directories tend to be on the riskier side of things, for some reason.

Maybe I'm missing the point, but those doesn't stuck me as something that
Access cannot do. Access certainly can use APIs and with a bit of grease,
interact with web services as well. Indeed, there has been demos of Access
using Google maps for example. Not to say it'd be easy as in ASP.NET
(because I don't know and would not want to presume so).

My perception is that getting at the classes and working with them tends to
be easier. Or at least there's ample documentation out there.

However, if the requirement is that people must be able to use the
application without downloading anything or maybe that it be available on
any platform, then that's a point where rich clients such as Access fall
short.


I wasn't suggesting that, but this is a consideration to put in the mind.
The OP didn't give enough information about the scope of project and what
users would be doing it and the load so I tried to be complete as possible
in highlighting the difference between a rich client and web client.
Network bandwidth happens to be one of traditional bottlenecks for any
data centric applications so depending on the volume of data and users
we're looking at, this can have important ramifications.

The trade off though is that the code operating on the data most likely
doesn't have to deal with network traffic as the DB most likely resides on
the same server, although its entirely possible that it could be a dedicated
web server accessing a dedicated server for the DB.

As they say, 'every dog has its day'. Sometimes it's Access. Sometimes
it's ASP.NET. Sometimes it's entirely something else. It would help OP a
lot to get into the functional requirements then it will become clear
whether it makes more sense to use ASP.NET or Access or heck, just a plain
old Excel spreadsheet for all I know!


That is very valid point- deployment certainly is much easier with ASP.NET
than with any rich clients.


That is true. If you don't mind me asking- how easy is it write custom
scripts? What about number of events available for the controls?

The code is code, not scripts, and is full bodied either (VB.NET or C#.NET)
complete with the ever popular Try...Catch...Finally which VBA lacks. When I
started working with ASP.NET a year ago, the biggest issue was learning the
controls and how to manipulate the HTML that the control rendered. For
example, one type of control renders its contents as a HTML table and I
needed to figure out how to create additional rows and cells as well as
accessing values in the cells. C# is the prefered language for the code
behind, however since I was already familar with VBA, I went with VB.NET and
didn't have any issues with the language. The other issue was remembering
that Javascript and DHTML were still valid approaches to accomplishing
something. I have several instances where the code that runs on the server
handles one part and Javascript handles another part of the task. I even
have code on the server that creates Javascript to run on the client.

The events for the common HTML controls on the page are easy to pick up such
as onclick, onmouseover, onmouseout, onchange, onfocus, etc. The ASP.NET
controls have events which run server-side for which you obviously have to
provide code for - just like Access - although the events are different.
Depending on the control, the events will vary.

Sample code

'This code fires on the _ItemDataBound event of control that displays
records in a simple list. This specific code causes the rows to appear with
alternating styles white/light blue. When the mouse moves over a row, its
highlighted in blue. You could use the same technique to apply a unique
color scheme to each row of data similar to FormatConditions, but with
*much* greater control over the conditions and the styles. You can even
dynamically change the image displayed with the record. For example 'new',
'open', 'close', 'void' images for an issue tracking application.

One of the cool things is that creating a ASP.NET page that pages through
data is *extremely* easy. There's one control that has it as a built-in
feature of the control.
Protected Sub RepeaterSelectEmployee_ItemDataBound(ByVal sender As Object,
ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)

Dim ItemCssStyle = "SelectEmployeeRow"

If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType =
ListItemType.AlternatingItem Then

If e.Item.ItemType = ListItemType.AlternatingItem Then

ItemCssStyle = "SelectEmployeeAlternatingRow"

End If

Dim RepeaterDetail As HtmlTable =
CType(e.Item.FindControl("RepeaterDetailSelectEmployee"), HtmlTable)

'Using this.className directly greatly improves the response time of turning
on or off the highlighting as .getElementById

'has to search through each element on a page, the performance drops as the
number of records on the page increases

RepeaterDetail.Attributes.Add("onmouseover",
"this.className='SelectEmployeeRowHighlight';")

RepeaterDetail.Attributes.Add("onmouseout", "this.className='" &
ItemCssStyle & "';")

Dim LinkButtonAddDelegate As LinkButton =
CType(e.Item.FindControl("LinkButtonAddDelegate"), LinkButton)

RepeaterDetail.Attributes.Add("onclick",
Page.ClientScript.GetPostBackClientHyperlink(LinkButtonAddDelegate, ""))

End If

End Sub

RepeaterDetail.Attributes.Add causes the HTML element to be rendered with
event handlers as in

<table onmouseover="this.className='SelectEmployeeRowHighlight'"
onmouseout="this.className='SelectEmployeeAlternatingRow'">

 

[Banana]

Isolating the web server from the rest of the network is certainly an
intelligent precaution, but at some point you're going to have to access it
in order to get new pages onto the site, let alone if the Admin needs to
work with anything on the server. Also, there's the practical issue of
working with the data. How do you add records to a products table? How do
you pull orders entered on via the site into whichever DB you use to fulfill
them?

Yes, those issues will need to be considered, but as an example- the web
server should have a separate service account that is privileged to
access SQL Server database but is not allowed to be remotely logged into
or so, making it unlikely that this particular account will be breached
and if another account ont he web server was breached, the data is still
safe. But if SQL Server is physically on the same server, then it does
not matter which account was breached- a clever hacker can escalate
privileges and gain access to other components. Anyway, with the service
account, you can still write/read data from the database via the web
server unfettered.

And yes, it increases the vectors, but that's where risk analysis is going
to come into play and that $150,000 you're paying your Network Admin to
secure your data.

Unfortunately, I've seen a numbers of posters whom were the de facto
administrators in various capability. I was one for one of my past
employment, with a nonprofit- they had a great IT manager but he was so
overworked so we had to make do. This may be the case with the OP for
all I know, and for that reason he should be cautioned and encouraged to
do his homework in understanding the ramifications of what he is wanting
to do.

(Kneel before your god, and pay head
to all that he decrees! Thou shalt follow the requirements set forth before
you. Thou shalt not cause, encourage, or support scope creep. Thou shalt
deliver thy product on time, on budget and bug free.)

Ay ay, how I wish this was so!

My perception is that getting at the classes and working with them tends to
be easier. Or at least there's ample documentation out there.

I'm not surprised this is the case, as this is probably more common
application- many credit processing application are usually styled as
self-service, though that is not to say there aren't any applications
that are operated by people who take credit cards over phone.

I suppose if one were savvy enough, one could translate all those into
Access/VBA and it'd work just as well.

The trade off though is that the code operating on the data most likely
doesn't have to deal with network traffic as the DB most likely resides on
the same server, although its entirely possible that it could be a dedicated
web server accessing a dedicated server for the DB.

Well, you are forgetting that the data still has to reach the client in
the browsers. Certainly the data access may be local and thus quite
responsive between database and web server but that is no guarantee that
same will be true between the web server and the client browsers. Then
there's still the layout that has to send along with the data, so layout
+ data + scripts is still topheavy compared to Access-ODBC which sends
data only.

The code is code, not scripts, and is full bodied either (VB.NET or C#.NET)
complete with the ever popular Try...Catch...Finally which VBA lacks.

I wasn't suggesting that ASP.NET isn't full-bodied languages, though for
whatever reasons I've heard of it being referred to 'scripting language'
which has a different connotation in web development than it does in
application development as far as I can tell.

When I
started working with ASP.NET a year ago, the biggest issue was learning the
controls and how to manipulate the HTML that the control rendered. For
example, one type of control renders its contents as a HTML table and I
needed to figure out how to create additional rows and cells as well as
accessing values in the cells. C# is the prefered language for the code
behind, however since I was already familar with VBA, I went with VB.NET and
didn't have any issues with the language. The other issue was remembering
that Javascript and DHTML were still valid approaches to accomplishing
something. I have several instances where the code that runs on the server
handles one part and Javascript handles another part of the task. I even
have code on the server that creates Javascript to run on the client.

The events for the common HTML controls on the page are easy to pick up such
as onclick, onmouseover, onmouseout, onchange, onfocus, etc. The ASP.NET
controls have events which run server-side for which you obviously have to
provide code for - just like Access - although the events are different.
Depending on the control, the events will vary.

Yes, I am sure of that, but my question was about numbers of events, not
the types of events. I'm under the impression that the numbers of events
available is somehow limited because of the fact that browser
application is a stateless application. You have to do roundtrips to
perform some tasks (which certainly can be done client-side using
javascripts or AJAX, depending on what the task we need to do). In rich
clients, you can perform many more things and respond to state changes
more readily whereas in web client there is no guarantee that you will
get the changes in state back from the users (e.g. they could open a
page, type something in, then close the windows... No changes are sent)
This is the part I'm especially interested in.


Thanks for sharing your code. This gives some idea of capabilities.

 

[David W. Fenton]

Access connecting to a SQLServer DB on a server running IIS
doesn't present any [additional] security issues than if the DB is
on a dedicated server and not running IIS. The security issues
don't compound simply because Access is connecting to a DB that's
the backend for a website.

But if you're opening up a SQL Server port to the open Internet to
support remote Access users, you're adding exposure to risk, not
because it's Access, but because you're opening the port. That is
not a standard configuration, and I think very few security
consultants would consider it an appropriate setup. The VPN is the
proper way to provide remote users access to resources available on
the internal LAN.

 

[David W. Fenton]

Yes, Access's selling point is rapid development, however there
are things that you can do with ASP.NET that you can't do with
Access. It can be just as functional as an Access front end if not
moreso - for example processing credit card payments or working
with API's from various well known sites that are out there.

Access can consume web services and communicate with web servers via
their exposed APIs, so I don't see the advantage you're asserting.

 

[David W. Fenton]

Isolating the web server from the rest of the network is certainly
an intelligent precaution, but at some point you're going to have
to access it in order to get new pages onto the site, let alone if
the Admin needs to work with anything on the server. Also, there's
the practical issue of working with the data. How do you add
records to a products table? How do you pull orders entered on via
the site into whichever DB you use to fulfill them?

What are you talking about here? The admin will likely have local
access to the web server (i.e., inside the firewall), and when the
admin is working form outside the firewall will either use a VPN
(most likely) to get inside the firewall, or some form of remote
access (either RDP or some form of shell access via SSL or some
other encrypted protocol that will be just as safe as VPN access).
The admin in a well-designed network will likely use one or more of
these protocols.

As to working with the data, it's much easier to control security on
your database when you have a limited number of ports open on the
machine where the database lives, and when you control the access to
web users through your web application. I just don't see how you
could be arguing for exposing a SQL Server port to the public
Internet, except, perhaps, in ignorance of the risks thereof.

 

[David C. Holley]

Yes, those issues will need to be considered, but as an example- the web
server should have a separate service account that is privileged to access
SQL Server database but is not allowed to be remotely logged into or so,
making it unlikely that this particular account will be breached and if
another account ont he web server was breached, the data is still safe.
But if SQL Server is physically on the same server, then it does not
matter which account was breached- a clever hacker can escalate privileges
and gain access to other components. Anyway, with the service account, you
can still write/read data from the database via the web server unfettered.


Unfortunately, I've seen a numbers of posters whom were the de facto
administrators in various capability. I was one for one of my past
employment, with a nonprofit- they had a great IT manager but he was so
overworked so we had to make do. This may be the case with the OP for all
I know, and for that reason he should be cautioned and encouraged to do
his homework in understanding the ramifications of what he is wanting to
do.


Ay ay, how I wish this was so!


I'm not surprised this is the case, as this is probably more common
application- many credit processing application are usually styled as
self-service, though that is not to say there aren't any applications that
are operated by people who take credit cards over phone.

I suppose if one were savvy enough, one could translate all those into
Access/VBA and it'd work just as well.


Well, you are forgetting that the data still has to reach the client in
the browsers. Certainly the data access may be local and thus quite
responsive between database and web server but that is no guarantee that
same will be true between the web server and the client browsers. Then
there's still the layout that has to send along with the data, so layout +
data + scripts is still topheavy compared to Access-ODBC which sends data
only.

In a web app, the only thing sent to the client is the HTML markup which is
simply text that's interpreted by the browser. The 'data' is nothing more
than characters in a string. Yes, if you're viewing 100 records, the markup
will contain more characters and thus take longer to load than a single
record, but when working with Access if you're 100 records will still take
longer to retrieve that 1.

Go to the airtran.com, and request flight information for flights from
Atlanta to New York (Laguaria) when you get the results back view the page
source and you'll see that its all text.

I wasn't suggesting that ASP.NET isn't full-bodied languages, though for
whatever reasons I've heard of it being referred to 'scripting language'
which has a different connotation in web development than it does in
application development as far as I can tell.

Classic ASP did use scripting with VBScript being the most commonly used
language to generate the content server-side. ASP.NET uses the full body
languages.

Yes, I am sure of that, but my question was about numbers of events, not
the types of events. I'm under the impression that the numbers of events
available is somehow limited because of the fact that browser application
is a stateless application.

I've never run into any issues when *not* being able to do something in
ASP.NET that I could in Access. Yes, the total number of events are probably
less, but there are events in ASP.NET that you don't have in Access. For
example, in Access there's no event that fires as Access is building each
row in a continuous form, in ASP.NET there is. Yes, there's no
Form_BeforeUpdate, _AfterUpdate, _BeforeDelConfirm, _AfterDelConfirm,
_BeforeInsert, _AfterInsert, but you're going to have an element on the page
that will generate the postback and trigger the element's server-side event.
If I have a link with the text 'UPDATE', I would then build server side code
that handles clicking on the link. That code would run whatever you would
have in the _BeforeUpdate SUB in Access, then Update the record and then
execute any _AfterUpdate Sub in Access. In that scenario, the absense of
specific events only serves to streamline things.

While it is technically 'stateless', ASP.NET gets around this problem by
using VIEW STATE which stores key information in an encrypted value that is
returned to the server when the page posts back. This allows ASP.NET to
detect changes that have occurred such as a user selecting a different value
from a Drop Down List and fire the _SelectedIndexChanged event of the
control.

(Truncated)
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE"
value="/wEPDwUKMTg2OTk3OTkzMA9kFgJmD2QWAgIDD2QWBAICD2QWAmYPZBYCAgUPEA8WAh4LXyFEYXRhQm91bmRnZA8WCgIBAgICAwIEAgUCBgIHAggCCQIKFgoQBSYoVVRDLTEwKSBIYXdhaWktQWxldXRpYW4gU3RhbmRhcmQgVGltZQUEMTAxMGcQBSAoVVRDLTgpIFVTIFBhY2lmaWMgU3RhbmRhcmQg

If you're validating data, you can do so client side and abort the postback
if needed, you can post back to the server and process the validation there,
or use client side AJAX which requires less coding on your part.

You have to do roundtrips to perform some tasks (which certainly can be
done client-side using javascripts or AJAX, depending on what the task we
need to do). In rich clients, you can perform many more things and respond
to state changes more readily whereas in web client there is no guarantee
that you will get the changes in state back from the users (e.g. they could
open a page, type something in, then close the windows... No changes are
sent) This is the part I'm especially interested in.

Yes, the roundtrips will always be there. I would suggest that there's one a
handful of things that you can do in Access that can't be done with a web
page. Yes, the user can open a page make changes and then close it losing
the changes, but most users know at this point that if there's an UPDATE,
SAVE, etc link, text or button that it needs to be clicked in order to save
the changes. You could argue that if a user opens a Form in Access, makes
changes and then QUITS access, that Access' updating the record without a
confirmation is undesirable. You'd have to explicity code to provide a
confirmation in the Form_Close event to see if there are unsaved changes and
then abort or commit them.

You may want to play around with airtran.com to see what sort of general
response times you get when searching for flights. www.asp.net is all ASP
and might also be a good thing to look at for information as well as
performance with a text-intensive application (for the forums). newegg.com
is an ASP.NET site and I've heard that DELL is as well.

http://www.asp.net/(S(il1c10q03wmehr454jhf3hmr))/get-started/

 

[David C. Holley]

Access connecting to a SQLServer DB on a server running IIS
doesn't present any [additional] security issues than if the DB is
on a dedicated server and not running IIS. The security issues
don't compound simply because Access is connecting to a DB that's
the backend for a website.

But if you're opening up a SQL Server port to the open Internet to
support remote Access users, you're adding exposure to risk, not
because it's Access, but because you're opening the port. That is
not a standard configuration, and I think very few security
consultants would consider it an appropriate setup. The VPN is the
proper way to provide remote users access to resources available on
the internal LAN.

How would someone on the network connect to a SQL Server on web server? I
know that ODBC is invovled, but don't know all of the specifics. Whereas
with Access, its just a matter of Access being able to see the file path to
the back end.

I was assumming that the security measures to lock it down from an internet
perspective would be different from those neccessary to lock it down on the
internal network.

My thought was that there are specific measures that are neccessary to lock
things down when dealing with the relationship between SQL Server and IIS
and that there are different measures neccessary to lock down SQL Server
when its being accessed from within the company network. I was making the
assumption that Access connecting over a company network to a SQL Server
database used for a web site would not create the need for any additional
security measures and that the two scenarios (SQL Server-IIS security, SQL
Server-Access over company network) that I mentioned would cover all of the
bases.

 

[Banana]

In a web app, the only thing sent to the client is the HTML markup which is
simply text that's interpreted by the browser. The 'data' is nothing more
than characters in a string. Yes, if you're viewing 100 records, the markup
will contain more characters and thus take longer to load than a single
record, but when working with Access if you're 100 records will still take
longer to retrieve that 1.

But the point is that when we're using ODBC, it's just binary stream
whereas in web client, everything is a text. Since text is definitely
more expensive than numeric & other data types, the amount that you need
to send will always be more for a web client than a rich client, and
don't forget the layout still has to be given as well whereas with ODBC
connection, it only needs actual data; the rich client takes care of the
layout. Yes, it's true for _both_ clients that 1 records is cheaper than
100 records, but the point is that 1 record via HTML will be more
expensive than 1 record via binary stream.

Classic ASP did use scripting with VBScript being the most commonly used
language to generate the content server-side. ASP.NET uses the full body
languages.

Ah, I see. FWIW, when I read about web development it seems that they
always use 'scripting' in association with various languages such as
PHP, Perl, Python, ASP.NET yet it's full-bodied; all languages can use
APIs for instance.

I've never run into any issues when *not* being able to do something in
ASP.NET that I could in Access. Yes, the total number of events are probably
less, but there are events in ASP.NET that you don't have in Access. For
example, in Access there's no event that fires as Access is building each
row in a continuous form, in ASP.NET there is. Yes, there's no
Form_BeforeUpdate, _AfterUpdate, _BeforeDelConfirm, _AfterDelConfirm,
_BeforeInsert, _AfterInsert, but you're going to have an element on the page
that will generate the postback and trigger the element's server-side event.
If I have a link with the text 'UPDATE', I would then build server side code
that handles clicking on the link. That code would run whatever you would
have in the _BeforeUpdate SUB in Access, then Update the record and then
execute any _AfterUpdate Sub in Access. In that scenario, the absense of
specific events only serves to streamline things.

While it is technically 'stateless', ASP.NET gets around this problem by
using VIEW STATE which stores key information in an encrypted value that is
returned to the server when the page posts back. This allows ASP.NET to
detect changes that have occurred such as a user selecting a different value
from a Drop Down List and fire the _SelectedIndexChanged event of the
control.

Cool. Thanks.

 

[Banana]

My thought was that there are specific measures that are neccessary to lock
things down when dealing with the relationship between SQL Server and IIS
and that there are different measures neccessary to lock down SQL Server
when its being accessed from within the company network. I was making the
assumption that Access connecting over a company network to a SQL Server
database used for a web site would not create the need for any additional
security measures and that the two scenarios (SQL Server-IIS security, SQL
Server-Access over company network) that I mentioned would cover all of the
bases.

FWIW- I don't think it's standard practice to open _any_ RDBMS server to
the internet. It's usually that web server is the client of RDBMS server
which is accessed within the company's network. In case of rich clients,
David Fenton already mentioned using VPN which is the appropriate
solution for remote clients. But the web users would never have direct
access to the RDBMS server, providing the web application is well
designed as well.

Another good reason for using VPN is to secure the data. If the client
were able to connect to the server across the internet, there is no
security, and the data can be sniffed or worse subject to
man-in-the-middle attacks. The scenario where security is of no concern
and public access is desirable would be exceedingly unlikely, and even
so, any good network administrator would not want this anyway because as
explained in my earlier response, a breached server can be turned into a
vector for even deeper breach, so maybe just because this database does
not contain any confidential data worth securing does not mean that the
rest of network needs to be protected as well.

 

[David W. Fenton]

Access connecting to a SQLServer DB on a server running IIS
doesn't present any [additional] security issues than if the DB
is on a dedicated server and not running IIS. The security
issues don't compound simply because Access is connecting to a
DB that's the backend for a website.

But if you're opening up a SQL Server port to the open Internet
to support remote Access users, you're adding exposure to risk,
not because it's Access, but because you're opening the port.
That is not a standard configuration, and I think very few
security consultants would consider it an appropriate setup. The
VPN is the proper way to provide remote users access to resources
available on the internal LAN.

How would someone on the network connect to a SQL Server on web
server?

You specify the name of the SQL Server, and the database you want to
connect to. Since you're inside the firewall, you presumably have
access to that. Whether you need to specify the port used depends on
whether or not the standard SQL Server port is being used. I would
certainly think that nobody contemplating exposing their SQL Server
to the Internet would do anything other than use a non-standard
port.

I
know that ODBC is invovled, but don't know all of the specifics.
Whereas with Access, its just a matter of Access being able to see
the file path to the back end.

Jet communicates with a server process via an open port on the
machine hosting the server (it could also be a proxy server that
forwards connections on to the real server).

I was assumming that the security measures to lock it down from an
internet perspective would be different from those neccessary to
lock it down on the internal network.

In either case, you'd be relying on either SQL Server or Windows
authentication to grant access to users. But if your SQL Server is
exposed to the open Internet, some hacker will do a port scan and
try to see if any of the ports looks like SQL Server, and if so,
then attempt common username/password combinations to attempt a
breakin.

SQL Server has also had some problems with vulnerabilitys in that
environment that do not involve cracking a valid logon.

My thought was that there are specific measures that are
neccessary to lock things down when dealing with the relationship
between SQL Server and IIS and that there are different measures
neccessary to lock down SQL Server when its being accessed from
within the company network.

The security is all in SQL Server.

I was making the
assumption that Access connecting over a company network to a SQL
Server database used for a web site would not create the need for
any additional security measures and that the two scenarios (SQL
Server-IIS security, SQL Server-Access over company network) that
I mentioned would cover all of the bases.

IIS has no security. Its ability to connect to a SQL Server is
entirely dependent on the rights granted within SQL Server to the
user acount under which IIS is running. In short, IIS is just
another user, not something special.

 

[David W. Fenton]

FWIW, when I read about web development it seems that they
always use 'scripting' in association with various languages such
as PHP, Perl, Python, ASP.NET yet it's full-bodied; all languages
can use APIs for instance.

I think this is to distinguish static HTML from "scripts" that run
on server-side and have procedural logic that is processed on the
server to determine what HTML gets sent to the client.

Many people consider VBA in Access to be a scripting language. I
have no idea if that's valid or not (interpreted languages tend to
be referred to that way, even if there are compilers for them).