Access and the Internet

[John G]

Hi all,
I am seeking advice on the following project.

In 2005, I developed a program for farmers to record their production and
commodity activities. I used Access 2003. The database is split and presently
running both FE and BE on private single user machines. The FE is MDE and the
BE is MDB. Each farmer has their own copy of the FE/BE on their own computer.
The users (farmers) never share each other's data. All data is private. A
user's data has nothing to do with any other user. All the users are required
to submit reports to an agency of which they are members. They email the
required reports from the program to the "agency" on regular bases. This
system has worked great now for five years.

However, the "agency" has grown to approx. 2000 members now. The "agency"
has requested that I design the program so it is accessible on-line. They
want to have access to their member's data at anytime without their members
having to email reports.

Searching for answers has lead me to believe I need to convert (upsize) the
BE to SQL Server or equivalent and place it on a server. Below are some
questions I have regarding this situation.

1. If SQL Server is what I need for the BE, then I would set it up on a
private server, right?

2. With the BE on a server, how do I prevent users from seeing each other's
data when they log in, but still allow the agency to view all data?

3. Although it may never occur there is the possibly of all 2000 members
connecting to the BE at the same time. Can I still use the present FE (Access
MDE) to connect to it? Alternatively, should I redesign all my forms and
reports using ASP.Net (or whatever), making it a web application?

4. I do have Access 2007. Would SharePoint be of use to me if I converted
the database to .accde/.accdb?

Thanks in advance for you time. I appreciate it.
John G

[Albert D. Kallal]

Perhaps another solution which would be far more sample from logistics point
of view would be to have the agency have a SQL server system, and have the
members instead of sending a report, press a button and that data that they
have is then uploaded to that central server. Then the central agency can do
all the report's they want.

The beauty of this system is that those farmers perhaps always have a good
Internet connection. Perhaps that not always the case. However these days,
I guess they pretty much assumes that everybody has a good Internet
connection.

So in other words instead of the submit button for an e-mail, have the
submit system connect to the SQL server that the agency owns. You pump up
the data they need for that report. You would of course add to that data an
additional column or something in the data that would identify the person.
Therefore you solve the problem of keeping the data separated for the
report's on the one central system. This setup would allow the agency to do
full summary on the data, or reports on individuals.

Furthermore you don't necessarily have to have the data normalized and
functional Exactly the same way as for the actual application. For just
reporting you just send up the summarized the data into a format that's
useful for reporting and slicing and dicing by the types of grouping that
the agency would require. In fact this is a conceptual summary of idea is
exactly what we call in our industry a data warehouse. A data warehouse is
simply a depositary of the data, often not in the original format, but in a
format that suitable for the needed reporting. So instead of having a
customer and 15 lines of invoice details, you might have a customer and just
a invoice number and the sales total. So you decide ahead of time what level
of details for reporting is needed here. You often wind up with not so many
tables and a considerably simpler data model than what the original
application requires to run.

Post by John G
1. If SQL Server is what I need for the BE, then I would set it up on a
private server, right?

Your choice. You can choose a hosted web provider (often they offer and
allow external applications to connect to sql server). Or you could have sql
server installed at the agency. So you can go with a private server at the
agency, or you can consider a web hosted one. And of course now there's
also a cloud addition of SQL server called Azure, I do believe that
subscription starts at $10.00 a month.

As mentioned, the problem here is is that you'd have to redesign your
program to work correctly for keeping the data separate from each other. If
your original design is not as such, this could turn out to be a fair amount
of work. Another possibility is to simply give each individual user a
separate database on that one instance of SQL server (SQL server works quite
well this way). You then build some type of reporting system that traverses
each database for summary reporting.

However, you likely don't want to make something that causes you a lot of
administrative work for each new person that gets a copy of this
application. You want to be careful here, because for each new deployment
of the application, if it requires human cost and set up on the SQL server
side a new database for EACH new user, then you increase the cost of running
this whole application, and you also increase the hassle for each individual
user to get a copy of this application.

Post by John G
2. With the BE on a server, how do I prevent users from seeing each other's
data when they log in, but still allow the agency to view all data?

As mentioned, you can on SQL server create a separate database for each user
to log into.

Post by John G
3. Although it may never occur there is the possibly of all 2000 members
connecting to the BE at the same time. Can I still use the present FE (Access
MDE) to connect to it?

You most certainly can. You would have to make sure the server is robust
enough, and has the capacity to handle that many users (perhaps you only
likely have 30-40 users of the same time, and that's not a problem for sql
server).

However keep in mind that your application must be written VERY well to
operate correctly over the Internet. The reason for this is because high
speed Internet is many times slower than what your local computer or local
network runs at speed wise. I speak of this issue here:

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

The above also explains many solutions for working over the Internet with
Access.

Post by John G
Alternatively, should I redesign all my forms and
reports using ASP.Net (or whatever), making it a web application?

If they're giving you the money and budgets to do the above, that's most
certainly a ideal solution. On the other hand you have to ask if you have
the skills and training to build that web stuff, and how long that's going
to take for you to get up to speed to become productive. You'll certainly
not be using ms access at all here.

You also don't mention how complex and how many tables and what kind of
application you have now. Take a look at the following video of mine, and
you can see that Access 2010 does have web creation ability:

So having the agency set up SharePoint 2010 would be a possibility and you
could build a web solution around that, but you don't have access 2010 as of
yet.

Post by John G
4. I do have Access 2007. Would SharePoint be of use to me if I converted
the database to .accde/.accdb?

Depending on your application, yes SharePoint is a possibility, but if your
application has quite a few related tables, I don't recommend SharePoint
2007. SharePoint 2010 is considerably better in this regards and allows
related tables, SharePoint in access 2007 does not support related tables at
all.

Do read the above article on WANs, it will help you quite a bit in this
whole understanding process.

[Armen Stein]

On Thu, 25 Feb 2010 23:14:04 -0700, "Albert D. Kallal"

Post by Albert D. Kallal
As mentioned, the problem here is is that you'd have to redesign your
program to work correctly for keeping the data separate from each other. If
your original design is not as such, this could turn out to be a fair amount
of work.

I agree, and very difficult to ensure no cross-visibility under all
circumstances. I wouldn't try it in Access - it would be a job for
ASP.NET.

Post by Albert D. Kallal
Another possibility is to simply give each individual user a
separate database on that one instance of SQL server (SQL server works quite
well this way). You then build some type of reporting system that traverses
each database for summary reporting.

Yes, much better approach. For the summary reports, I recommend that
each database have a scheduled job that exports data (either directly
to a centralized database, or using XML into a folder) and then the
central database can run summary reports on that data. We do that
with one of our client's systems that has databases all over North
America and a central reporting database also.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[John G]

Thanks for the recommendation, Armen.
I appreciate your time and advice.

John G

Post by Armen Stein
On Thu, 25 Feb 2010 23:14:04 -0700, "Albert D. Kallal"

Post by Albert D. Kallal
As mentioned, the problem here is is that you'd have to redesign your
program to work correctly for keeping the data separate from each other. If
your original design is not as such, this could turn out to be a fair amount
of work.

I agree, and very difficult to ensure no cross-visibility under all
circumstances. I wouldn't try it in Access - it would be a job for
ASP.NET.

Post by Albert D. Kallal
Another possibility is to simply give each individual user a
separate database on that one instance of SQL server (SQL server works quite
well this way). You then build some type of reporting system that traverses
each database for summary reporting.

Yes, much better approach. For the summary reports, I recommend that
each database have a scheduled job that exports data (either directly
to a centralized database, or using XML into a folder) and then the
central database can run summary reports on that data. We do that
with one of our client's systems that has databases all over North
America and a central reporting database also.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
.

[John G]

Albert,

Sorry for not replying sooner but I got busy with another job.

Thanks for all the information and ideas. I had read what you wrote about
operating over the internet at:
http://www.members.shaw.ca/albertkallal/Wan/Wans.html prior to my original
posting. I was still unsure what to do so I thought I might just as well post
my question to gain more insight.

I like your suggestion of uploading just the data required for the
report(s). At present, the farmer's name and membership number are already
included in the report data. I will need to add a column to the report/query
data to create a unique identifier for each record thereby preventing
duplicate records in the server if the farmer uploads the same data more than
once. The database tables all have primary keys, which are auto numbers. I
could just add the membership number to the primary key (auto number) and use
a unique identifier for each report type. There are eight different reports.

For Example:
Primary Key (auto number): 4327
Unique Report Identifier: CP
Membership Number: BR549
The result would be a unique number for each record uploaded something like
this: CP4327BR549
Do you think this is a proper solution to indentify records in the server or
is there a better way?
Do I understand you correctly Albert; I would only require eight tables in
the data server because there are only eight different reports? Each upload
would append report data to the appropriate table.

Again, thank you so much for your time and expertise. I really appreciate
the help.
John G

Post by Albert D. Kallal
Perhaps another solution which would be far more sample from logistics point
of view would be to have the agency have a SQL server system, and have the
members instead of sending a report, press a button and that data that they
have is then uploaded to that central server. Then the central agency can do
all the report's they want.
The beauty of this system is that those farmers perhaps always have a good
Internet connection. Perhaps that not always the case. However these days,
I guess they pretty much assumes that everybody has a good Internet
connection.
So in other words instead of the submit button for an e-mail, have the
submit system connect to the SQL server that the agency owns. You pump up
the data they need for that report. You would of course add to that data an
additional column or something in the data that would identify the person.
Therefore you solve the problem of keeping the data separated for the
report's on the one central system. This setup would allow the agency to do
full summary on the data, or reports on individuals.
Furthermore you don't necessarily have to have the data normalized and
functional Exactly the same way as for the actual application. For just
reporting you just send up the summarized the data into a format that's
useful for reporting and slicing and dicing by the types of grouping that
the agency would require. In fact this is a conceptual summary of idea is
exactly what we call in our industry a data warehouse. A data warehouse is
simply a depositary of the data, often not in the original format, but in a
format that suitable for the needed reporting. So instead of having a
customer and 15 lines of invoice details, you might have a customer and just
a invoice number and the sales total. So you decide ahead of time what level
of details for reporting is needed here. You often wind up with not so many
tables and a considerably simpler data model than what the original
application requires to run.

Post by John G
1. If SQL Server is what I need for the BE, then I would set it up on a
private server, right?

Your choice. You can choose a hosted web provider (often they offer and
allow external applications to connect to sql server). Or you could have sql
server installed at the agency. So you can go with a private server at the
agency, or you can consider a web hosted one. And of course now there's
also a cloud addition of SQL server called Azure, I do believe that
subscription starts at $10.00 a month.
As mentioned, the problem here is is that you'd have to redesign your
program to work correctly for keeping the data separate from each other. If
your original design is not as such, this could turn out to be a fair amount
of work. Another possibility is to simply give each individual user a
separate database on that one instance of SQL server (SQL server works quite
well this way). You then build some type of reporting system that traverses
each database for summary reporting.
However, you likely don't want to make something that causes you a lot of
administrative work for each new person that gets a copy of this
application. You want to be careful here, because for each new deployment
of the application, if it requires human cost and set up on the SQL server
side a new database for EACH new user, then you increase the cost of running
this whole application, and you also increase the hassle for each individual
user to get a copy of this application.

Post by John G
2. With the BE on a server, how do I prevent users from seeing each other's
data when they log in, but still allow the agency to view all data?

As mentioned, you can on SQL server create a separate database for each user
to log into.

Post by John G
3. Although it may never occur there is the possibly of all 2000 members
connecting to the BE at the same time. Can I still use the present FE (Access
MDE) to connect to it?

You most certainly can. You would have to make sure the server is robust
enough, and has the capacity to handle that many users (perhaps you only
likely have 30-40 users of the same time, and that's not a problem for sql
server).
However keep in mind that your application must be written VERY well to
operate correctly over the Internet. The reason for this is because high
speed Internet is many times slower than what your local computer or local
http://www.members.shaw.ca/albertkallal/Wan/Wans.html
The above also explains many solutions for working over the Internet with
Access.

Post by John G
Alternatively, should I redesign all my forms and
reports using ASP.Net (or whatever), making it a web application?

If they're giving you the money and budgets to do the above, that's most
certainly a ideal solution. On the other hand you have to ask if you have
the skills and training to build that web stuff, and how long that's going
to take for you to get up to speed to become productive. You'll certainly
not be using ms access at all here.
You also don't mention how complex and how many tables and what kind of
application you have now. Take a look at the following video of mine, and
http://youtu.be/AU4mH0jPntI
So having the agency set up SharePoint 2010 would be a possibility and you
could build a web solution around that, but you don't have access 2010 as of
yet.

Post by John G
4. I do have Access 2007. Would SharePoint be of use to me if I converted
the database to .accde/.accdb?

Depending on your application, yes SharePoint is a possibility, but if your
application has quite a few related tables, I don't recommend SharePoint
2007. SharePoint 2010 is considerably better in this regards and allows
related tables, SharePoint in access 2007 does not support related tables at
all.
Do read the above article on WANs, it will help you quite a bit in this
whole understanding process.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

[Albert D. Kallal]

Post by John G
I
could just add the membership number to the primary key (auto number) and use
a unique identifier for each report type. There are eight different reports.
Do you think this is a proper solution to indentify records in the server or
is there a better way?

Sure, that sounds good. Remember, you can some what modify your original
data + tables to that
so called "data warehouse" format you plan.

Post by John G
Do I understand you correctly Albert; I would only require eight tables in
the data server because there are only eight different reports? Each upload
would append report data to the appropriate table.

Well, actually, if 4 of the reports are based on the same data or table,
then you would only
need 1 table for those 4 different reports.

It is a assumed that you build those reports on the sql server side using
sql server reporting services or
even better supply a access database that reports on the data you upload to
that sql server.

So I'm just pointing out, because you have a 8 reports, it doesn't
necessarily mean you're going to wind up with 8 tables on the resulting
server, but it is a possibility.

As mentioned, keep in mind that you going to de-normalize a lot of your
data. In my typical applications darn near everything is some type of
related table or driven by some other table.

For example take a look of the following row from one of my reports:

Bus# Trip Date City Hotel Group Bus Guide

So, the above row is likely 4 or 5 related tables (hotel, Group name, bus
company, guide name etc are all from related tables).

However, we can de-normalize the above into one row and one table when we
upload so you don't need a corresponding 4 to 5 tables on the end server
system. So the above example, I likely don't need a hotel table on the
reporting server side. However, depending on how complex or how easy the
reporting system is going to be, if a lot of the report's are going to
prompt that user for what hotel to report on, then you might very much so
want to keep a hotel active list in a separate table.

So, you might need 8 tables, you might need more. However, the main idea
here is to de-normalize the desktop data down to a reduction of details to
what you need. So, in the above I'm still betting that I could likely not
need a separate hotel table, and thus when I upload this data, I'll not
up-load the hotel ID, but upload the actual text description name of the
hotel. This is what we mean by de-normalize in the data, you can eliminate a
lot of a required related tables in this fashion.

As mentioned, about the only advice in this type of data warehousing project
is is to not reduce the detail so much that you can't do the reporting you
need any more.

So for example, lets assume we ONLY need customer sales by month. This is
great as we could generate one ine of data in a table that summarizes all of
the customer sales for the one month. We then upload that ONE row to the so
called reporting server. Thus, you not need to up-load very much data. So,
we not need to upload every single invoice and every sale value for each
customer into that reporting database. On the other hand, if after running
it for couple of months the boss then comes back and says they want to
report daily sales, you're in big big trouble. And perhaps they want
particular sales of a particular product by a particular location.

So the concept or idea here is to ensure that you think ahead far enough to
ask or think about the types of questions and reporting that they might need
on the summary data that you're up loading.

So while you you're only up-loading the summary data, don't summarize too
far as then you'll not be able to answer new questions or new reports that
they need.

I think it is a given that things like part number descriptions or the like
will not be up-loaded as related tables. This will thus eliminate the need
to maintain the equivalent complex look up tables or so called related
tables that are likely so common in well written and properly normalized
database systems. So, do de-normalize your data before you upload into what
is often referred to in our industry as a data warehouse.

[John G]

Thanks so much for the information Albert. You explained it very well!
I sure do appreciate all the time you put into helping me understand my
options.
Thanks again.
I will likely be back before I have this project completed.

John G

Post by Albert D. Kallal

Post by John G
I
could just add the membership number to the primary key (auto number) and use
a unique identifier for each report type. There are eight different reports.
Do you think this is a proper solution to indentify records in the server or
is there a better way?

Sure, that sounds good. Remember, you can some what modify your original
data + tables to that
so called "data warehouse" format you plan.

Post by John G
Do I understand you correctly Albert; I would only require eight tables in
the data server because there are only eight different reports? Each upload
would append report data to the appropriate table.

Well, actually, if 4 of the reports are based on the same data or table,
then you would only
need 1 table for those 4 different reports.
It is a assumed that you build those reports on the sql server side using
sql server reporting services or
even better supply a access database that reports on the data you upload to
that sql server.
So I'm just pointing out, because you have a 8 reports, it doesn't
necessarily mean you're going to wind up with 8 tables on the resulting
server, but it is a possibility.
As mentioned, keep in mind that you going to de-normalize a lot of your
data. In my typical applications darn near everything is some type of
related table or driven by some other table.
Bus# Trip Date City Hotel Group Bus Guide
So, the above row is likely 4 or 5 related tables (hotel, Group name, bus
company, guide name etc are all from related tables).
However, we can de-normalize the above into one row and one table when we
upload so you don't need a corresponding 4 to 5 tables on the end server
system. So the above example, I likely don't need a hotel table on the
reporting server side. However, depending on how complex or how easy the
reporting system is going to be, if a lot of the report's are going to
prompt that user for what hotel to report on, then you might very much so
want to keep a hotel active list in a separate table.
So, you might need 8 tables, you might need more. However, the main idea
here is to de-normalize the desktop data down to a reduction of details to
what you need. So, in the above I'm still betting that I could likely not
need a separate hotel table, and thus when I upload this data, I'll not
up-load the hotel ID, but upload the actual text description name of the
hotel. This is what we mean by de-normalize in the data, you can eliminate a
lot of a required related tables in this fashion.
As mentioned, about the only advice in this type of data warehousing project
is is to not reduce the detail so much that you can't do the reporting you
need any more.
So for example, lets assume we ONLY need customer sales by month. This is
great as we could generate one ine of data in a table that summarizes all of
the customer sales for the one month. We then upload that ONE row to the so
called reporting server. Thus, you not need to up-load very much data. So,
we not need to upload every single invoice and every sale value for each
customer into that reporting database. On the other hand, if after running
it for couple of months the boss then comes back and says they want to
report daily sales, you're in big big trouble. And perhaps they want
particular sales of a particular product by a particular location.
So the concept or idea here is to ensure that you think ahead far enough to
ask or think about the types of questions and reporting that they might need
on the summary data that you're up loading.
So while you you're only up-loading the summary data, don't summarize too
far as then you'll not be able to answer new questions or new reports that
they need.
I think it is a given that things like part number descriptions or the like
will not be up-loaded as related tables. This will thus eliminate the need
to maintain the equivalent complex look up tables or so called related
tables that are likely so common in well written and properly normalized
database systems. So, do de-normalize your data before you upload into what
is often referred to in our industry as a data warehouse.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

[Marco]

Friends and friends around the world.
We are presenting the new messenger.
He will be in the commissioning by the friends that we indicate to use the
system.
Access the system by clicking the link below and register free.

You get something for using orkut?
You get something for using skype?
You gain something by using twiter?
You get algumaocisa for using facebook?

Enjoy this is your time!!

Sign up and join for free.


http://www.sqipcom.com/?ref=webempreendedor

http://stakeholder.sqipcom.com/user/webempreendedor