Is Access the right tool?

[MLK]

Hello,

I'm more familiar with Excel than Access and need some input. I recently
took some Access courses to get me started, but not yet sure if the
application can do what I want.

I have just taken over an application inventory processs. Over the past 3
years, users previously had to complete application inventory surveys (in
Word) and then someone would manually re-enter the results into Excel (this
was not MY process).

My goal is to automate this as much as possible.

Here's what I would like to be able to do:
1) Post the form as a webpage
2) Users should be able to bring up prior year application survey info NOTE:
If there is a new application, then users should be able to create a new
record
3) Users should be able to edit their record and apply any changes to it for
the current year (I will need to add a field called "year" to identify which
year the survey was completed for)
4) Users should only be able to pull up the selected application or new
record (I don't want users being able to scroll through all the applications)
5) And finally, users should be able to save their data via the webpage

Is the above possible in Access?
The courses I took were VERY basic and it appeared like Access only expected
one person to enter data... however in my case I need to let about 400 users
enter data.

Thanks, Mary-Lou

 

[scubadiver]

Via a webpage you will need ASP or some other server based database
technology (which I am not familiar with unfortunately). With 400 users I
think a webpage would be more appropriate!

 

[MLK]

I'm not familiar with that either. Do you know where I can find out more
information on doing this?

 

[MLK]

I forgot to mention that I looked into Access originally because I read that
you could load the forms as a webpage. In my course, as long as the
database is on a server that everyone could access, it should be ok.

Aside from using ASP, I would prefer to maintain control of the forms and
data myselft without having to get one of the other technical groups
involved... ASP would require other groups and then I would need to fill out
change request forms all the time to get any changes completed and get put
into the work queue... this could take days and/or weeks each time.

Any other suggestions or ideas?

 

[Scott McDaniel]

Hello,

I'm more familiar with Excel than Access and need some input. I recently
took some Access courses to get me started, but not yet sure if the
application can do what I want.

I have just taken over an application inventory processs. Over the past 3
years, users previously had to complete application inventory surveys (in
Word) and then someone would manually re-enter the results into Excel (this
was not MY process).

My goal is to automate this as much as possible.

Here's what I would like to be able to do:
1) Post the form as a webpage
2) Users should be able to bring up prior year application survey info NOTE:
If there is a new application, then users should be able to create a new
record
3) Users should be able to edit their record and apply any changes to it for
the current year (I will need to add a field called "year" to identify which
year the survey was completed for)
4) Users should only be able to pull up the selected application or new
record (I don't want users being able to scroll through all the applications)
5) And finally, users should be able to save their data via the webpage

Is the above possible in Access?

Not really ... Access requires that each user have a copy of Access (retail or runtime) in order to use the Forms you
create. Your "form" can't really be moved to a website, and used as is - it would require some from of web-based
language (like ASP, PHP, ASP.NET, etc) in order to be able to "talk" to the database.

Access does provide Data Access Pages, which are capable of being viewed in a web page. This technology was never really
fully advanced, however, and developers quickly realized that to write robust web-based applications required that they
indulge in learning one of the web languages.

To sum it up: You can't really write a desktop-based Access application that can be view through a web browser.

One alternative is to use Terminal Server, or some other remoting technology. This can be an expensive option, however.

The courses I took were VERY basic and it appeared like Access only expected
one person to enter data... however in my case I need to let about 400 users
enter data.

Access can only support 255 concurrent users, and if you get anywhere even close to that number you'll likely be very
displeased with the performance ... general consensus is that when you hit around 25 or so it's time to consider moving
the database engine to a server engine (like MySQL, SQL Server, etc).

Thanks, Mary-Lou

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[MLK]

Thanks for the update Scott... looks like I will need to research another
avenue.

 

[John W. Vinson]

I forgot to mention that I looked into Access originally because I read that
you could load the forms as a webpage.

Wherever you read that... it's WRONG.

Access Data Projects (ADP) files, using SQL/Server as a backend, yes - but
Microsoft is going to be dropping support for this option

DAP (Data Access Projects) were another failed (by most accounts) attempt to
provide that capability; I would not recommend them.

Access .mdb forms *cannot* be used on webpages.

John W. Vinson [MVP]

 

[david12]

I forgot to mention that I looked intoAccessoriginally because I read that
you could load the forms as a webpage. In my course, as long as the
database is on a server that everyone couldaccess, it should be ok.

Aside from using ASP, I would prefer to maintain control of the forms and
data myselft without having to get one of the other technical groups
involved... ASP would require other groups and then I would need to fill out
change request forms all the time to get any changes completed and get put
into the work queue... this could take days and/or weeks each time.

Any other suggestions or ideas?

How about this solution -
Taking advantage of your Excel knowledge and everyone can run Excel:

1. User goes to website and selects either "New" or the application
survey based on whatever criteria you need that the user enters.
2. If existing application survey, an Excel file is generated that
holds the required survey data that's held in an Access database. An
Access query exported as an Excel file handles this.
3. User can then download either the generated Excel file or, if
"new", an Excel file that's a template for a new survey.
4. With the downloaded Excel file, user enters data as needed on their
own (separate from website).
5. When ready, user goes to website and chooses "Upload" to return a
copy of the edited .xls file.
6. That data is then appended to the Access database using an import
from Excel file that happens immediately (I'm not sure how to trigger
it) or in an overnight batch run.

I don't know how the website portion would work but exporting/
importing data to/from an Excel file is easy with Access.
This also gives you control over how the spreadsheet is designed for
either new or existing data.
Dave