Access 2016 User Interface

[jarena]

I have been asked to assist a local company with four separate projects. I will need to create a database for each project (note the company has Access 2016 on their server. I am an old programmer and DBII guy that retired approximately 10 years ago). I understand relational database theory and design, but I have never used Access. I have looked at Access and realize that Forms can be created/used to input data and reports can be created using the Query and Report features. Once I design and create a database, is there a way in Access (without using a programming language i.e., PHP or VB) to provide a clean user interface (i.e., menu driven or GUI) for the company’s employees to enter data and produce reports?

[CJ_London]

Once I design and create a database, is there a way in Access (without using a programming language i.e., PHP or VB) to provide a clean user interface (i.e., menu driven or GUI) for the company’s employees to enter data and produce reports?

you can use the wizards to create forms and macros to go behind buttons and there are 'special' forms for navigation and split view. So I guess technically it can be done, but you will be limiting yourself to a potentially clunky design.

Also the wizards won't necessarily generate efficient forms etc. And you won't find ones for creating some types of forms - a login form for example

But you are going about it the right way, design the tables and relationships first, then design forms and reports.

There are things you need to be aware of:

1. applications should be split (data in tables in the BE, doesn't have to be the ACE db, everything else in the front end)
2. each user needs a valid copy of access on their machine (not server)- you can use the free runtime version but that has its limitations so may require you to do some coding. They should definitely not share the same physical file. The multiuser aspect relates to the BE tables, not the FE forms/reports etc
3. each user has a copy of the FE on their local machine
4. users should have a hard wired connection to the server where the BE is located (not wireless/internet)

there are workarounds but really depends on the application requirements specification as to whether they are relevant or not.

For commercial apps, there are a lot of other considerations to be made - what is the lifeplan for the apps? data security? maintenance? etc

good luck with your projects

[Micron]

To that great advice I would add
- that the major limitation of macros is error handling - there is none. When they fail, the process stops and users are often presented with messages they don't understand
- you can get lots of code from the internet that will often plug in and work as long as you follow the directions (if given). However, code is often posted without telling anyone what library reference is required. That's where this forum can help.
- my own approach is queries after table design, not forms and reports. I don't base forms on tables as a rule, because queries provide more flexibility and can provide more efficient recordsets. So my point is, why would I build a form only to have find that the query I need won't support it? That is rare, but it can happen. Besides, I can use a wizard to create a form from that customized query.
- a word about the Wizard: It is a quick way to build a form/report but I never allow the Access given names of my objects to remain as given. First step is to 'properly' name the controls. However, that may not be so important if you stick with macros.

In the end, it will be a challenge to produce a professional result at a high level without any VBA code. You might want to reflect on whether or not you should take on these tasks. I guess it depends on what level of sophistication they expect, how much pressure you might be under and would failure impact your reputation. Should you decide to take it on, we're here to guide you.

[jarena]

Thanks for the tips Ajax; I will refer to these as I move forward.

[jarena]

Thanks for the advice Micron. Are there references materials, sites, or tutorials that you would recommend?

[CJ_London]

there are plenty about, just google something like 'access tutorials'. Some will be better than others or perhaps to be more precise, some will chime better with you than others. So look around for one that works for you.

With regards books, 'Access for Dummies' used to to be regularly recommended. Otherwise as Micron says, if you get stuck, ask your questions here. If you do, post examples (example data/outcome required/error messages/screenshots of forms etc) rather than just describing the problem. It saves time

[Micron]

Your skill level may be above these but since I have them handy and you asked...
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

There are tons of instructional videos out there. If you prefer books, I have seen pdf copies of Access manuals but can't recall where. Older ones are still of value IMO because the basics don't change. I would advise to not put any stock in any instruction that involves lookup fields in tables. Last but not least, if you intend to utilize the built in split form or navigation form make sure you are aware of their behaviours and limitations.

[jarena]

Thank you both; I appreciate the references. I start the position tomorrow and I will let you know how things progress . . . if I get stuck, I know where to find expert advice!

[Micron]

I start the position tomorrow

I think I'm envious. A PT job in Access might get me off the couch and out of the wood shop in these Covid times.
Good luck!