Access Web Application

[Ariel Dugan]

Hi,

I am beginning a project developing an Access Database. I am starting the
development with VBA, however I will be obtaining VSTO in the near future.

My two questions are...

1. Can I set up the app to be accessible via the Web using ASP.NET?

2. Can I set it up so that app can be updated over the web, as well as viewed?

3. Will the users accessing the app over the web be required to have Access
on their machines, or will they just need Internet Explorer.

The app will be accessing external data from an SQL source on the same
network on which the app will be running; just mentioning this in case it
further complicates the questions above.

Thanks in advance. I know I am asking for a lot of info.

Ariel Dugan.

 

[Rick Brandt]

Hi,

I am beginning a project developing an Access Database. I am
starting the development with VBA, however I will be obtaining VSTO
in the near future.

My two questions are...

1. Can I set up the app to be accessible via the Web using ASP.NET?

2. Can I set it up so that app can be updated over the web, as well
as viewed?

3. Will the users accessing the app over the web be required to have
Access on their machines, or will they just need Internet Explorer.

The app will be accessing external data from an SQL source on the same
network on which the app will be running; just mentioning this in
case it further complicates the questions above.

Thanks in advance. I know I am asking for a lot of info.

Ariel Dugan.

You cannot run Access in a web page. You could build a web app using
ASP.Net that web users could run *instead of* your Access app, but you would
be building everything over again from scratch. There is nothing in your
Access app that you can simply convert to run in a web page.

If the user audience is low you could host the Access app on a terminal
server and have them VPN into that over the internet. Then they would be
remotely running your unmodified Access app and ASP.Net is not in the
picture at all. In that scenario they would not need Access installed, but
they would be required to hold licenses for it (as well as licenses to
connect to Terminal Server). The Access licenses would not be required if
the Terminal Server used the Accesss runtime.

 

[Ariel Dugan]

Thanks a lot for your reply. The advice is greatly appreciated.

1. Are your suggesting I create a Data Base with ASP.NET? I'd like to do
it this way, to make my app as usable as possible. Where exactly would I
create the tables?

2. If I just went ahead and made it using the access tools, I suppose I
could still post reports to web pages. Thats right, isn't it?; and the users
could open those reports without Access?

Ariel

 

[Jeff Conrad [MSFT]]

Hi Ariel,

I'm interested in hearing about your scenario. Can you describe in a little
more detail what you hope to accomplish? Can you describe your application a
little bit? Size, complexity, business need, etc?

In what way would you like Access to help you here? How do you envision your
users using this application? How many users?

Thanks,
--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Rick Brandt]

Thanks a lot for your reply. The advice is greatly appreciated.

1. Are your suggesting I create a Data Base with ASP.NET? I'd like
to do it this way, to make my app as usable as possible. Where
exactly would I create the tables?

No. You would use a database engine for the database and only interact with it
from a web application. You could do that in ASP.Net or in any other platform
that can be used to build web applications. I only mentioned ASP.Net because
you did. The database could be Jet (mdb file) but many would use a server
engine like SQL Server if they expected much traffic.

2. If I just went ahead and made it using the access tools, I
suppose I could still post reports to web pages. Thats right, isn't
it?; and the users could open those reports without Access?

If you created HTML versions of the reports sure. But they would be static.

 

[Ariel Dugan]

Hi Jeff,

Thanks for your reply, I look forward to your insights.

We currently have all of our records in an SQL Data Base. (We're a natural
foods retailer, but the way, so these records are for our point of sale
system.)

I just wrote out my statement of purpose today, so I'll paste it below.
Perhaps a bit too much info, maybe you would only want to read the header
section.

Our produce operation will only ever have about 600 item records, but our
entire database with all of our items is getting close to 30,000.

I envision them using it mostly on the Network where they are working. The
web access part could be mostly, or all for viewing data, although I'd like
it to be interactive. There would be about 3 - 5 people using it all the
time, and up to 50 that would view the data regularly. The 3 - 5 would be
entering data into the app.


Access Data Base Statement of Purpose

I. Facilitate Produce / Other Dept Ordering.
II. Facilitate Produce Price changes.
III. Provide Delivery Documentation
IV. Track Purchases and Invoice Payment
V. Weekly Reporting of all Shipping and Billing Data.
VI. Application to be Accessible via the Internet.


I. Facilitate Ordering
a. Import function to get regular updated data from CP SQL server for each
dept table included. (or have function to pull data during each need for
data required, similar to how all of our excel sheets work)
b. Maintain Backorder data with adjustable rule till backorder expires.
(Back order will expire in “x†days.)
II. Produce Price Changes
a. All invoice pricing to be entered from invoices as is done presently.
b. Margin achieved to be calculated by comparing cost to actual retail in CP
SQL server. (Presently we are comparing the cost to the suggested retail,
which is regularly different than the actual retail for ½ of the week.)
c. Produce data for price changes, to be copied and pasted into existing
price change sheet.
III. Delivery Documentation
a. Provide separate Delivery Paper for each location, with total charges,
and also the current retail shown.
i. To include total case count.
IV. Track Purchases and Invoice Payment
a. Replace purchase log.
b. P.O.# generated for each order.
c. Form for entering invoices by matching to P.O.#
d. Create an interactive form / report for P.O.’s with no paid invoice
associated with them. User could enter an amount in an “Estimated Fieldâ€
until actual invoice is present.
V. Weekly Reporting of all Shipping and Billing Data.
a. Weekly Transfer Report
b. Weekly Cube Rate
c. Weekly Fill Rate by Location
d. Full Detailed Transfer Reports for All locations by time period specified
by the user.
VI. Application to be Accessible via the Internet.
a. Final Stage to be implemented when all error handling is apparently
complete.
b. Provide user interface accessible over the internet. Logins would be
user specific, some allowing updates, while others only allowing viewing and
printing of report data.

 

[Jeff Conrad [MSFT]]

Hi Ariel,

Thank you very much for the detailed scenario specifics.

I have some follow-up questions for you.

Concerning this:

Our produce operation will only ever have about 600 item records, but our
entire database with all of our items is getting close to 30,000.

When you say "entire database" does the 30,000 records come from all tables or would at least one
table have 30,000 records? If one table has 30,000 records, about how many records are you adding in
during a month's time?

Concerning this:

I envision them using it mostly on the Network where they are working. The
web access part could be mostly, or all for viewing data, although I'd like
it to be interactive. There would be about 3 - 5 people using it all the
time, and up to 50 that would view the data regularly. The 3 - 5 would be
entering data into the app.

You said you'd like the web part to also be interactive. Would it still be only about 3-5 people
adding/editing/deleting records or would that increase to the 50 you mentioned?

Concerning this:

VI. Application to be Accessible via the Internet.
a. Final Stage to be implemented when all error handling is apparently
complete.
b. Provide user interface accessible over the internet. Logins would be
user specific, some allowing updates, while others only allowing viewing and
printing of report data.

Would the Logins be all internal or from external sources such as vendors as well?

Thanks for your time,
--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Ariel Dugan]

Hi Jeff,

Thanks for your continued interest in my project.

The items table in our sql database has about 30,000 records, and there are
many other tables, each with 30,000 records.

1. There are presently 5 locations, so several attributes are in 5 seperate
tables, (namely Price, Bin Location, Inventory Level, Sales Tickets, and some
others that I am neglecting), for a total of 30,000 x 5 each.

2. We are only adding about 15 new item records a month.

3. If I was able to make the app Web Based, the number of people who I
would want to be able to add/delete records would increase to about 50. If
not, then it would stay at 3 - 5. The reason being that I would like those
that are accessing the app from the Web to be able to enter orders into the
system.

4. All the logins would be internal. They would be all of our buyers
throughout the company.

Thanks again,

Ariel

 

[Jeff Conrad [MSFT]]

Hi Ariel,

Thanks again for the continued discussion.

How complex do you envision the front end for the users - number of queries, forms, reports, macros,
modules?

How many people would be adding/editing the database objects?

What is your current environment? Operating systems, server setups, Office versions, etc.?

Thanks!
--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Ariel Dugan]

Hi Jeff,

I hope this isn't too much info...

I would like to make the application fairly complicated over time. I have
really enjoyed making time saving features in our current, Excel based
Produce Ordering system, which has a custom menu, many userforms, and
thousands of lines of VBA. It generates Weekly and Qtrly reports for all key
statistics, and takes a lot of work out of the actualy ordering, saving many
hours of time a month.

I am looking to do something similar, but on a better platform, with the
benefits of relational data. If it works well, I would like to use it for
all of our Departments in the company.

I'm not exactly sure how many forms, reports, etc, but I want to make it
open ended, so that as needs come up, I can add the features. Of course we
will have an initial plan that will attempt to cover all the obvious ones. I
would like to be able to retrieve records, and update records from our SQL
data with this application. I would want it to update inventory, pricing,
etc.

When you ask how many people will working with the Database Objects, if you
meant working on developing the application, that will just be me. If you
were referring to using the application, then 3 - 5.

We are using CounterPoint, (A front end for SQL Data that is designed for
Retail POS (Point of Sale). It holds all items, sales history, etc. The
company we are working with has created an array of interfaces to accomplish
all of our tasks like updating prices, running sales reports, and changing
bin locations, using Excel.
I am not looking to replace any of this, but I am wanting to create an
ordering and distribution system that works along with our current system.

We are using XP Pro, Office 2003. We have a central Server at our corporate
office, and Servers at each of our 4 other locations. Each replicate back to
the central Server every hour with all updates.

Thanks again,

Ariel

 

[Jeff Conrad [MSFT]]

Hi Ariel,

Very interesting; thank you for the additional information.

Is this the system you are currently using?
http://www.synchronics.com/products/cpv7.htm

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Ariel Dugan]

Hi Jeff,

Thats right, same program, just a previous version. We are using version
8.3.4

Thanks
Ariel

 

[Ariel Dugan]

Hi Jeff,

Did you have any suggestions for me on this project?

What approach do you suppose would be appropriate for me to take? I am just
learning, so perhaps a more simplified, Access based program would be the way
to go, although a web based system would be great.

Thanks in advance...

Ariel

 

[Jeff Conrad [MSFT]]

Hi Ariel,

With your current requirements, your options are a little limited. You might
need to follow Rick's recommendations of having the data stored in JET or
SQL Server with a .NET front end or use some kind of Terminal Server
environment.

Thank you for providing some background on your scenario. These comments
help us figure out how customers are currently using our products and also
how they'd like to be using our products.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Ariel Dugan]

Thanks for the reply Jeff,

Sounds like I've got my options well established. Thanks for all your time.

I will look into using ASP.Net for the front end, and our existing sql
database to supply the app with our existing data. I was also going to
create a seperate sql database to hold the tables of data that only relate to
the application, but not to the Counterpoint system.


Let me know if you think this is an unreasonable approach, if not then
thanks again for all your time.

Ariel