Conversion to a Web Based Application

[Frank Wagner]

I am a developer of math software that is used in elementary schools. My
system was created using Access 2000 with the Jet engine as the back end. I
have two groups that want to use the software if it can be converted to a web
based application. I have reviewed some of the previous discussions
regarding this issue, and haven’t been able to clarify my thinking as to what
to do.

My application contains about 130 forms, 40 tables and 30 queries. None of
the tables contain a large amount of data. About 600 records is contained in
the largest table. The system is fairly interactive. If it is web based, mu
guess is that from 500 to 10,000 students could be using the system at any
one point in time.

My question is how best to proceed. If the back-end is changed to something
like SQL Server can this type of application become web-based. My guess is
that I may need to convert to a later version of Access. I probably need a
consultant to help me make the conversion, but I don’t want to start down
that path until I get some general understanding of the issues, and the
feasibility of such a conversion.

Any help would be appreciated.

 

[Tom van Stiphout]

On Sat, 12 Jul 2008 14:33:00 -0700, Frank Wagner

If you want to convert (better: rewrite) your app as a web
application, and you have that many users, indeed SQL Server is a much
better home for the data. The forms could be built using ASP.NET and
the reports using SQL Server Reporting Services. Access will be
completely out of the picture so I don't understand your comment about
"convert to a later version of Access".
If you don't yet have any of these skills, it would be a massive
learning curve. My suggestion would be to start with something less
ambitious.

-Tom.

 

[Albert D. Kallal]

It is not clear in your post if you using MS access for your front end, or
perhaps a different set of tools like vb?

However, at the end of the day you're talking about distributing 10,000
copies of an application with 10,000 different users, 10,000 different logon
names, and 10,000 different passwords. Just the mention of that many users
and environment can be a fulltime job in itself, and we not even begun to
talk about the actual application itself.

MS access is not a web development tool, and has absolutely no relationship
to a web based system in any way shape or form. I can no more write database
applications using auto-cad then I can use MS access for this web
application you need.

It's also not clear if you're planning to have 10,000 copies of this
application used, and each user has just a few records in the application,
or you have a design in which you have 10,000 users, and they're are all
shareing the data at the same time?

MS access is not suited to this type of user model (many users, each with
seperate info). When you started talking about 10,000 users, then I have
little are no idea why MS access was used in the first place here?

A web based application it has nothing to do with MS access, and you're
using the wrong tools. I guess I'm not 100% clear of how the user base
functions now?

How is the current distribution installing of this application handled?
(anyway, I'm probably wasting your time asking for additional information,
since as mentioned with such a large number of users, I think a web based
solution is the ticket here).

If you're talking about a dozen users, or perahps 50 users, then different
solutions and different possibilities are appropriate for your needs. With
such numbers of users, then FAR more care needs to be taken as to what kind
of solution is appropriate here.

 

[Frank Wagner]

Tom:

The application is presently distributed with the Sage distribution system
for use by students on individual computers. I don't know if the terms are
right, but I thought that MS Access could be used as a Front End to create
user Forms, and SQL Server could be used as a Back-End instead of the Jet
Engine to manage the Queries and Tables. Is this correct?

Given such an environment, could the system then be used to handle multiple
users at the same time?

 

[Frank Wagner]

Albert:

The application is presently distributed with the Sage distribution system
for use by students on individual computers. I don't know if the terms are
right, but I thought that MS Access with VBA code could be used as a Front
End to create user Forms, and SQL Server could be used as a Back-End instead
of the Jet Engine to manage the Queries and Tables. Is this correct?

You're not wasting my time by continuing the discussion. That's the only
way I'm going to truely get my questions answered, and it's important to me
that I do get a clear understanding.

Is it the concept of multiple users that is the problem, or the number of
users? Presently if I have 25 students in a computer lab working with the
system, the system is loaded on each computer. Could the Back-End of the
system using something like SQL Server be loaded on a Network server, and
just the Front-End forms on the individual computers? If this is possible,
could the system be accessed over the web in a similar manner? If that is
possible, could the system then be accessed from a number of schools at the
same time with a web server.

I know that this discussion may seem a little far-fetched to you, but I need
someone to bear with me a little bit until I am able to understand.

 

[Tom van Stiphout]

On Sat, 12 Jul 2008 20:42:01 -0700, Frank Wagner

It is indeed possible to have an MsAccess front-end with a SQL Server
back-end. This setup, in the hands of experts, can support more than a
hundred users.
There are essentially two ways to do this: either keep an MDB format
and link to the SQL Server tables using ODBC, or use an ADP format. It
appears that MSFT is phasing out the latter option so I would not use
it for significant new development.

In your original post you were speaking of a web-based system. Have
you changed your mind in the last few hours?

-Tom.

 

[Rick Brandt]

Tom:

The application is presently distributed with the Sage distribution
system for use by students on individual computers. I don't know if
the terms are right, but I thought that MS Access could be used as a
Front End to create user Forms, and SQL Server could be used as a
Back-End instead of the Jet Engine to manage the Queries and Tables.
Is this correct?

Given such an environment, could the system then be used to handle
multiple users at the same time?

Access is automatically multi-user capable. Always has been.

Making the back end a SQL Server database enhances the multi-user capability
a LOT compared to using a Jet back end.

Neither of these ideas though has anything to do with making a web
application. A web application would not be created using Access at all.
It would be created using tools for creating web applications (which Access
is not).

Consider the question "How do I make my Excel file into a web application?".
The answer would be to use web page building tools to create a web page that
mimics what the Excel file does. Excel would not be involved at all in that
process. The same applies to web applications and Access.

 

[Frank Wagner]

To all who have responded:

Thanks for responding. I’ve not necessarily changed my mind, but I have a
number of options that I need to explore.

I have a fairly successful interactive application that I developed using
Microsoft Access to help elementary students practice math. It is presently
being used in about 30 schools in my local area. In the individual
classrooms it is loaded on the 2 or 3 computers available, and the students
practice math in the corner. In the computer labs, it is loaded onto each
individual computer, and 25 to 30 students bang away during 30-minute lab
sessions. Students are also allowed to take the system home and practice on
their home computers using the same software they use at school - which can
be a great help.

Larger schools and school districts are becoming interested in the software,
but would like to use in on their internal networks or as a web-based
application. I’m not sure how to proceed. My understanding is that it can
be converted to a network application by separating it into Front-End and
Back-End applications, and using SQL Server or the limited version that has
been built into more recent versions of Access as the Back-End. I don’t know
how many users this can serve. Most schools have one computer lab with
around 25 computers, and 4 to 16 individual elementary school classrooms with
typically 2 computers each.

School systems that are quite large, would like to see it as Web-Based
application that could be accessed over the internet, or distributed to their
individual schools using some form of internal network. If you have 100
schools, with 40 computers each this suddenly becomes 4,000 potential users.
I chose Access because I was familiar with it, and it has broad world-wide
usage. So far it has worked very well for me in these early stages. Going
forward I’m faced with the need to have it work in at least environments: 1)
in a single school network with about 40 computers, 2) on a school system
network with up to 4,000 computers, and 3) the internet with who knows how
many users. I hope this helps clarify my dilemma.

I need help, and appreciate any and all thoughts

Thanks

 

[Rick Brandt]

Going forward I'm faced with the need
to have it work in at least environments: 1) in a single school
network with about 40 computers, 2) on a school system network with
up to 4,000 computers, and 3) the internet with who knows how many
users. I hope this helps clarify my dilemma.

I need help, and appreciate any and all thoughts

Well if you need (3) for the largest schools then you might as well use the
web-application approach for all situations. The difficulty in the web
application approach is that you have to design and build a web application.
Once that is done it can just as easily be used by small organizations as by
large ones.

If you have some organizations where the web application will not work
because they have no internet access (getting pretty rare these days) then
you have no choice but to support two completely separate applications. One
that uses Access/SQL Server to run on a local network and another that is a
web application.

Access simply is not a path to a web application other than to prototype
what you want the app to do. Once you decide that you need a web app, then
you have to start all over and build that using development tools
appropriate for building web apps.

All that you really have now is a good idea for a web app. The fact that
you have a completely working model of that app already built in Access does
not get you one bit closer to a web app than just having the idea does.

 

[Frank Wagner]

Rick:

Thanks for the response. It’s starting to clarify things a bit for me.

It sounds like the Web Based application is beyond my sights for the moment,
so maybe I'd better focus on adding the ability to have the system also work
as a network application. This would probably satisfy the large schools
systems if I could get it to work on their networks. A few follow up
discussions along those lines would help.

How large of a network can Access handle? One school with 40 computers
would be the typical network. I suppose more than one school could be on a
network, but seeing they’re at different locations, I’m not sure that is a
realistic scenario.

What would be the best way to convert my current application to a network
application? I presently use Access 2000 because a number of the schools
still have Windows 98 systems, and packages created by Access 2000 can still
be loaded on Windows 98 computers. Since the application will still need to
be used by students in their homes, I will probably need to retain a Jet
engine version for home use. If this is the case, I could advance to a later
version of Access for the network version, and still keep a Jet engine
version using Access 2000.

Do I need to create the back end using SQL Server, or should I use one of
the scaled down versions of SQL Server that are included with later versions
of Access, such as Access 2002? Are there any utilities to help convert the
Back-End to a network version? Any good written instruction books for making
such a conversion?

This looks like the path I need to take for the moment. Any thoughts along
this line would be appreciated

Thanks

 

[Albert D. Kallal]

Albert:

The application is presently distributed with the Sage distribution system
for use by students on individual computers.

Ah, ok..then you taken "serious" steps in distribution the software
That helps a LOT in terms of Clarifying your situation.

I don't know if the terms are
right, but I thought that MS Access with VBA code could be used as a Front
End to create user Forms, and SQL Server could be used as a Back-End
instead
of the Jet Engine to manage the Queries and Tables. Is this correct?

Yes, and that's is a good use of sql server. This would mean then all of the
data between each user is going to be shared. If that is your design goal,
then this is indeed a possible solution. if you're looking for design that
shares the the information, but keeps the information separate from each
user, then you'll need some redesigns in your application.

You're not wasting my time by continuing the discussion. That's the only
way I'm going to truely get my questions answered, and it's important to
me
that I do get a clear understanding.

OK, excellent.... then we are on the same train then!

Is it the concept of multiple users that is the problem, or the number of
users?

Certainly the concept of multi user and moving the back end data to SQL
server is a good solution, and is not a problem.

However the number of users is a significant issue, and it really comes down
to a question how many of those 10,000 people will on at one given time
sharing and using the application at the same time. There's no question that
SQL server can scale tools significantly large number of users, but I
certainly have no experience with 10,000 users on the same system at the
same time. You might be able to expand on this issue as to how many users
you expect to be on at a give time.

Presently if I have 25 students in a computer lab working with the
system, the system is loaded on each computer. Could the Back-End of the
system using something like SQL Server be loaded on a Network server, and
just the Front-End forms on the individual computers? If this is
possible,
could the system be accessed over the web in a similar manner? If that is
possible, could the system then be accessed from a number of schools at
the
same time with a web server.

The above is an excellent thought, and the answer is absolutely yes, SQL
server can be used over the Internet, and functions quite well in this type
of scenario. as a general rule you want to design your application to
minimize the amount of network traffic, and most of the good design tips
used in a access "file share" system as you mention with a 25 users, the
same good design approach has worked much the same when you use SQL server .
In a nutshell simply means that your designs need to limit the amount of
records transferred into a form, and thus you never just simply open a large
form attached to a table without some type of where clause to restrict the
data transferred (this issue becomes significantly important as the scale to
more users, and is also significantly important when using SQL server).

It is quite common for people to move the backend from being a shared "mdb"
file to that of using SQL server is a replacement (your tables in the front
end are then linked to sql server). Using SQL server allows you to run more
users then a file share. And, with careful designs, you can also run them
over Internet connections.

The only significant issue will have to be aware of here in this case is
setting up a secure access to that server. In other words you want everybody
to be able use data on the server, from anywhere, but it also means that
you're opening up that server to the Internet wild.

So, you need some good advice on the networking side to make this secure. In
a nutshell it means that you're going to have to give all users access to
that particular network that has the sql server on it. You also might need a
way to remove access from a particular user also. So, a bit of an issue here
with regard to managing the security issue, as I said the large number of
users creates a larger problem here.

Some type of logon system and the implementation of IDs for those people
likey will have to setup.

I know that this discussion may seem a little far-fetched to you, but I
need
someone to bear with me a little bit until I am able to understand.

Your additional information and clarifications were a big help on this
matter. The problem still remains the large user base, however if the number
of users at any given time is not going to be too high, say maybe 500, or
thousand users, then the setup is certainly doable. I would think that a web
based solutions a far better approach here however, as you'll imitate all
the issues of the distribution of the software side, and you will also
eliminate all the issues of having to set up a secure connection for your
software to connect to SQL server.

 

[Rick Brandt]

Rick:

Thanks for the response. It's starting to clarify things a bit for
me.

It sounds like the Web Based application is beyond my sights for the
moment, so maybe I'd better focus on adding the ability to have the
system also work as a network application. This would probably
satisfy the large schools systems if I could get it to work on their
networks. A few follow up discussions along those lines would help.

How large of a network can Access handle? One school with 40
computers would be the typical network. I suppose more than one
school could be on a network, but seeing they're at different
locations, I'm not sure that is a realistic scenario.

With a SQL Server back end you can have hundreds (or even thousands) of
simultaneous users. It's really just a matter of using a server with enough
horsepower. The fact that you are using Access as the front end really
doesn't enter into it. You would need to host it on a PC with a real server
operating system or else you would only have a 10 user limit on connections.

What would be the best way to convert my current application to a
network application?

As stated previously Access is multi-user and therefore IS a network
application right out of the box. Splitting into a front end and back end
and giving each user a separate copy of the FE is considered a
"requirement", by most everyone with experience, but with a SQL Server back
end you automatically have a split application.

I presently use Access 2000 because a number of
the schools still have Windows 98 systems, and packages created by
Access 2000 can still be loaded on Windows 98 computers. Since the
application will still need to be used by students in their homes, I
will probably need to retain a Jet engine version for home use. If
this is the case, I could advance to a later version of Access for
the network version, and still keep a Jet engine version using Access
2000.

Moving to a network (SQL Server) application places no special requirements
on the version. I deploy multi-hundred user apps with Access 97 against SQL
Server with no problems at all.

Do I need to create the back end using SQL Server, or should I use
one of the scaled down versions of SQL Server that are included with
later versions of Access, such as Access 2002?

The free versions of SQL Server have the same functionality as the standard
version except for a 4GB size limit and a few other things like not
supporting multiple processors . That part really makes little difference
unless you need the specifications of the full versions. The earlier free
versions had limits on the number of concurrent connections, but I think the
newer versions no longer have that.

Are there any
utilities to help convert the Back-End to a network version? Any
good written instruction books for making such a conversion?

There is an upsizing wizard, but I have little experience with them. I
prefer to build the tables on the SQL Server myself.

This looks like the path I need to take for the moment. Any thoughts
along this line would be appreciated

You can certainly experiment with the free version that is already on your
Office CD. That can easily be converted to other versions of SQL Server if
needed.

 

[David W. Fenton]

I have a fairly successful interactive application that I
developed using Microsoft Access to help elementary students
practice math. It is presently being used in about 30 schools in
my local area. In the individual classrooms it is loaded on the 2
or 3 computers available, and the students practice math in the
corner. In the computer labs, it is loaded onto each individual
computer, and 25 to 30 students bang away during 30-minute lab
sessions. Students are also allowed to take the system home and
practice on their home computers using the same software they use
at school - which can be a great help.

It doesn't sound to me like the type of application that should be
built using Access. So far as I can tell, the only database-related
functions would be the storage of the questions and the storage of
results.

I would say this is the kind of app that could very easily be
converted to a web application, since the Access version couldn't
possibly be very complex.

 

[Frank Wagner]

Rick:

Thanks for the latest response.

With regard to a network version that uses SQL Server as the Back-End, since
I presently use the Access 2000 Developers Edition, it might make sense to
use the SQL Server 7.0 that came with it. Does that seem reasonable? I also
have the regular version of Access 2002 that has a limited version of SQL
Server built into it, along with a conversion wizard that may be of some
help. If I rebuild the application with that limited version of SQL Server
in Access 2002, do you know if I can then convert if back to an Access
2000/SQL Server application?

With regard to the Web application, the main difficulty would be recreating
the approximately 130 forms that drive the present system along with the
sizable amount of VBA code behind them. If I have to start that over again
from scratch using some other system or language, my guess is that I probably
will never do it. Does converting the application to an Access/SQL Server
create anything that is usable for a web application?

Again your help is truly appreciated. It’s starting to help me sort through
the issues I face.

 

[Frank Wagner]

David:

I built the application using Access because I was familiar with Access and
VBA from previous work I did on medium sized marketing databases that
involved up to 200M names and addresses. Also I was very satisfied with the
tools that Access provides to easily build forms that students could work
with interactively. The application contains about 130 forms that cover the
wide range of math issues that student need to learn between the 1st and 8th
grades. You’re right, the database needs are merely storing data for
problems and recording results for teachers and students to review.

I’m trying to find out how difficult it would be to convert it to a web
application. The biggest issue is the sheer number of forms and the sizeable
VBA code behind them to make the forms work. If the forms and the code could
somehow be incorporated into the web application, that would save the bulk of
the work. From all I’ve heard so far, I’m not sure if that is possible. It
looks like I definitely need to convert the Back-End to SQL Server to make
the system work on the networks that many larger schools have. Does that
help any in making the application easier to convert to a web application?

I appreciate the help you and others are providing to help me sort through
these issues.

 

[Frank Wagner]

Albert:

Thanks for the help you and others are providing. I’ve got some tough
issues to face, and you’re helping me sort through them.

You mentioned that SQL Server can be used over the internet. Does that mean
that an Access/SQL Application can be converted somehow to an internet
application? I have approximately 130 forms in the present system with a
fairly amount of VBA code that helps to drive them. If I have to recreate
them using some other system or language I probably would never do it. If
some of that could be salvaged somehow it could greatly affect the end result.

With regard to the use of data from tables, it is primarily math problem
data that drives the math practice forms, and results data that captures how
the students are doing. The problem data required for a particular form can
be restricted to 10 records in most situations. The results data presently
goes into a results table common to all students. Teachers can enter a
password and access all the students data, students can only get at their own
results. Not many teachers are accessing all the data, so I think it would
be possible to limit the usage to just students looking at their own results.

Thanks again for any help you can provide. It helps me a great deal.

 

[Rick Brandt]

Rick:

Thanks for the latest response.

With regard to a network version that uses SQL Server as the
Back-End, since I presently use the Access 2000 Developers Edition,
it might make sense to use the SQL Server 7.0 that came with it.
Does that seem reasonable? I also have the regular version of Access
2002 that has a limited version of SQL Server built into it, along
with a conversion wizard that may be of some help. If I rebuild the
application with that limited version of SQL Server in Access 2002,
do you know if I can then convert if back to an Access 2000/SQL
Server application?

When you use Access as the FE to a SQL Server BE the version of SQL Server
makes little difference. As long as you have the appropriate ODBC driver,
Access (any version) can link to any version of SQL Server. In fact you
could build an app that when installed at one location uses SQL Server 7 and
when installed at a different location uses SQL Server 2005 without any
changes at all to the Access FE.

And the term "rebuilding" is a bit overstated in my opinion. For the most
part you remove the tables from your existing Access file and replace them
with links to the tables on the SQL Server. In some cases the app will
immediately and without further modification just work. Often there will be
areas that you must modify a bit and more areas that won't require
modification, but will benefit from it in terms of performance. How much of
this you will actually need is entirely determined by the design of your
application.

With regard to the Web application, the main difficulty would be
recreating the approximately 130 forms that drive the present system
along with the sizable amount of VBA code behind them. If I have to
start that over again from scratch using some other system or
language, my guess is that I probably will never do it. Does
converting the application to an Access/SQL Server create anything
that is usable for a web application?

You can use the ODBC linking described above over an internet connection.
This would mean that your SQL Server is available and has a port opened to
the internet. It would not work "just like" the same server on a LAN, but
if the amount of data traffic is not too high that could work. While it is
sometimes done, not many corporate SQL Servers allow open ports to the
internet for security reasons which is why web applications are
traditionally used.

 

[Frank Wagner]

Rick:

I have now loaded the version of SQL Server 7.0 that came with my Access
2000 Developer Edition so I guess I may be ready to go.

You mentioned the “Appropriate ODBC Driverâ€. I’m not sure what I have, but
it would be whatever came with the Office 2000 Developers Edition . Do you
think that would be OK?

With regard to the Internet Connection, I’m not quite sure what this would
be. Does that mean that the SQL Server Back-End would be available over the
internet, but the Front-End Access forms would still need to be loaded onto
each individual users computer?

Thank again for your help. I think I’m getting a little clearer as we
proceed.

 

[Rick Brandt]

Rick:

I have now loaded the version of SQL Server 7.0 that came with my
Access 2000 Developer Edition so I guess I may be ready to go.

You mentioned the "Appropriate ODBC Driver". I'm not sure what I
have, but it would be whatever came with the Office 2000 Developers
Edition . Do you think that would be OK?

Most likely. If by chance you needed a different version I'm sure it could
be downloaded from Microsoft's site.

With regard to the Internet Connection, I'm not quite sure what this
would be. Does that mean that the SQL Server Back-End would be
available over the internet, but the Front-End Access forms would
still need to be loaded onto each individual users computer?

Yes. I don't have much experience with setting up or using SQL Server in
that manner (I use web requests for all of that), but I'm guessing the
details wouldn't be hard to find out.

 

[Frank Wagner]

Rick:

Thanks for all your help.

I think my thoughts are pretty well set for the moment - thanks to you and
some others.

I've given up on the thought of trying to use the forms I've created using
Access for a web site. The impression I now have is that they're just not
suited for that purpose.

I'm moving ahead on converting the application to one that can be used on a
network with the "Access2000/SQL Server7" tools I have available from my
Office 2000 Developers Edition. This should solve most of the issues I'm
facing with larger school systems.

Let me know if you see any problems with this thinking. Otherwise I'm
signing off on this thread. Thanks for your help and patience along the way.
You've stuck with me, and that means a lot to me.

Thanks Again