Microsoft Access vs Web App

I am working with a non profit.

I don’t have a good idea of how Access is used yet, it’s my first day.


Would it be recommended to use a web application instead of Access?

The non profit is a mental health clubhouse. Right now people sign in and out on a piece of paper and it’s manually entered into Access.


It seems like if we used a web app we could allow people to sign in and out digitally and access it automatically.


Additionally we could generate CSV files to import into Excel.


Where should I begin in evaluating this decision?
burnedfacelessAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Would it be recommended to use a web application instead of Access?
Only if you have four or five times the budget for creating the Access app.  Access is a Rapid Application Development tool and in the hands of a competent developer, fully functional applications can be built very quickly.

It seems like if we used a web app we could allow people to sign in and out digitally and access it automatically.
How would people use this app?  Would you provide a computer in your lobby?  If so, that computer can just as easily run an Access app to log people in.

Additionally we could generate CSV files to import into Excel.
If the data is in Access, why is the analysis not also being done in Access?  Access is certainly capable of exporting an .xlsx file.  Just FYI a .csv file is NOT an Excel format.  It is a text file that is comma delimited.  You might as well call it an "Access" file or a "Word" file since they can also read .csv files.

Sounds to me as though you don't like Access.  This is a job you should not have accepted if you are not functional in the tool the client wants you to use.  You are doing the client a disservice by trying to get them to spend more money to develop applications on a more expensive platform because you  don't know enough about Access to know that it is quite able to do the job at hand and do it for a very reasonable price.  Of course, if you don't actually know how to develop Access apps, perhaps you will cause the client extra money anyway.  This is a non-profit.  They don't have money to waste.

If you want to keep your job and you want to work in the client's interest, it's time to put on your Access hat and get with the program.  Crystal has published some excellent articles and videos for beginners.  Look for her YouTube channel.
0
burnedfacelessAuthor Commented:
I've never worked with Access before.

I'm actually a mental health patient myself. I'm not paid for my work but I'm passionate about the opprotunity to grow some IT skills in a buddying non profit.

I'm out of school until next Spring.

I'm a web developer and it would be to my benefit to develop a site, as I would get better at web dev but I'm trying to see what is best for the client.

Is your recommendation to take the Access file home and play around with Access?

It seems like I have the opportunity to learn a lot about this non profit and non profits in general.

They're giving me a lot of responsibility and your answer didn't really answer my question.

It seems like if I know what Access can do I can better asses a web app vs Access.


I am a competent web dev, I'm asking because I know nothing about Access. I think in terms of web standards right now.
0
PatHartmanCommented:
I'm not sure I can summarize in a single sentence what Access can do but here goes.

Access can create any desktop application that needs to process data.  How's that for generic?  Access can link to any database that provides an ODBC interface and update those linked tables.  Access supports forms, reports, queries, and VBA code.  There is a macro capability but if you are capable of coding in another language, you would have no use for them so don't get involved with macros.

Access forms and reports are class objects and support event processing.  Create a form and add a few controls.  Take a look at the property sheet and see what events are offered for the specific control.  Events are raised by controls and by the form/report itself.

The most important event you need to understand for a form is the FORM level BeforeUpdate event.  This is the LAST event that runs before a record gets saved.  There is no way to bypass it so it is the normal event where you would code any validation.  You can then cancel the update, set focus back to the control in question and exit the sub.  Here's an example of the type of code you would see in this event.
If IsDate(Me.StartDate) Then
    If IsDate(Me.EndDate) Then
        If Me.EndDate >= Me.StartDate Then
        Else
            Msgbox "End Date must be >= Start Date.",vbOKOnly
            Cancel = True
            Me.EndDate.SetFocus
            Exit Sub
        End If
    Else
        Msgbox "End Date is required.",vbOKOnly
        Cancel = True
        Me.EndDate.SetFocus
        Exit Sub
    End If
Else
    Msgbox "Start Date is required.",vbOKOnly
    Cancel = true
    Me.StartDate.SetFocus
    Exit Sub
End If

If Me.CustomerName & "" = "" Then
    Msgbox "Customer Name is required.", vbOKOnly
    Cancel = True
    Me.CustomerName.SetFocus
    Exit Sub
End If

If Me.Addr1 & "" = "" Then
    Msgbox "Customer Address 1 is required.",vbOKOnly
    Cancel = True
    Me.Addr1.SetFocus
    Exit Sub
End if

Open in new window


A general bit of advice for someone who can program in a different language.

Access is a RAD tool.  Do NOT try to make it over in your own image.  Learn to work with it the way it is.  You will find it to be amazingly capable and easy to work with if you don't try to fight it.  Do not assume that you need to write code to control Access.  The vast majority of the code you will need is similar to what I posted.  You need to write validation code in forms.  When you find you need to do something start with
1. can I do it with an action query?
2. can I do it by setting properties on forms or reports?
3. OK, I guess I'll have to write code.

ALWAYS use bound forms and reports.  Do not go down the rabbit hole of unbound forms at this juncture.  There are uses for them if you find you need to use Access across the internet but for a desktop application that runs on a LAN, forms that process data should be bound.

The wizards can be helpful in building the initial versions of forms, reports, and queries.  You then pretty them up.

Always us good naming standards for tables, columns, and object names.  Use "programmer" rules -- letters (upper/lower), numbers, and the underscore and that's it!

Keep your names short.  Use meaningful abbreviations.  Consistency is important as it is with ALL development.  If you call a Customer "Cust" in one place, do not switch to "Customer" in others.

The users will always ONLY see forms and reports.  As a developer, you will build an interface for them that allows them to search for data, update it, and report on it.  There is NEVER a reason for them to ever see a query or the navigation pane.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
burnedfacelessAuthor Commented:
This is what I was looking for.

We're still doing a lot of stuff by hand, I was crunching numbers.

But I'll take the database home and start playing with this thanks
0
PatHartmanCommented:
One additional item since you are new to Access.  The proper way to create a multi-user Access application (and you should consider ALL apps you create will eventually require multiple users) is to split them into a Front End (FE) and Back End(BE).  The BE contains ONLY tables - assuming you are using Jet (.mdb) or ACE (.accdb) as your data store.  Otherwise the BE will be SQL Server, ORacle, DB2, or whatever else you have available.  The FE contains all the other object types - Forms, Reports, queries, macros, code and LINKED tables.

When you distribute the application, EVERY user gets a copy of the FE and all the FE's are linked to the shared BE which lives on the server.  It is best if the users run the FE from their C drive.  When you get to the point of distribution, we can discuss how that works.

For testing and initial development, it is not wrong to create a monolithic app where the FE and BE are together but before they go into production, they MUST BE SPLIT.  There is a built in tool that will split an existing app if you go that route.

There are three  important reasons for the split.
1. It minimizes the potential for corruption that can happen if everyone is in the app at the same time and one of the users has a problem that freezes his system.  This has the potential to corrupt the database causing you to loose data.  This is not a risk you need to take.
2. It allows you to take a copy of the FE and BE off line and make changes to the FE.  Once they are tested, you can simply replace the old FE with your modified FE and the users have an updated app.   You don't need to futz with their data.  If you also needed to change the schema in the BE, it is a little more complex to distribute the changes but except for very early on in the development, it is rare to actually have to change the BE.
3. When the app is split, and the FE is linked to a RDBMS BE, the app is infinitely scaleable.  The only limit is the number of seat licenses you have for the RDBMS.

Last but not least.  Access is designed to work with relational databases.  Do not assume because tables in datasheet view look sort of like spreadsheets that there is ANY similarity.  Always normalize the schema as you would for any application.  I have noticed that web developers do not have a clear understanding of normalization so you might actually want to start your studies with that topic.  you will not find any functionality in Access that violates first normal form so no tables with columns named Jan, Feb, Mar, etc or task1, task2, task3.  Those are all repeating groups and repeating groups are a 1-many relationship and must be implemented with two related tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.