Learning Access; Effective Database building and data management.

russell664 · 3 May 2015 at 13:15

Learning Access; Effective Database and management.

Hi Guys,

I thought I would post here, and at least ask to see if anyone knows anything about this, or the best way for me to gain some knowledge upon the subject.

I would need to do some serious research upon Access in order for me to work with it due to not having much experience with Access.

So to the question, I have been looking at guides around on how to do this, creating relationships, what tables do, how data is managed etc... But, non of them seem overally in depth either. I would like to understand how it works, and why things do this, and do that kind of thing. But am unsure exactly where to look in terms of content for this.

Do any of you know any places with some comprehensive guides/information on using and understand Access? Even short courses perhaps?
As of now, I have some very basic knowledge on the subject, but would really like to expand up on it, any suggestions oh smart ones?

Skeeter · 3 May 2015 at 13:43

Access can be good to teach you the basics of relational databases, but do not build anything that's ever going to be used by more than 1 user in it.

gpuerrilla · 3 May 2015 at 17:38

That's rubbish. Access can offer some good benefits for small businesses and can support the last time I dabbled in multi-user around 10 users before it starts to strain, just ensure you use the record locking feature.

russell664 · 3 May 2015 at 17:42

Yeah the system would be for 5 people in total, but in terms of building it and gaining the knowledge to do so, where would the best place be?

Most of the things I can find are very vague.

Armageus · 4 May 2015 at 10:43

Skeeter said:
Access can be good to teach you the basics of relational databases, but do not build anything that's ever going to be used by more than 1 user in it.

Absolute rubbish - Access is fine for any number of users (Access' database format on the other hand - normally JET, isn't however and will cause issues with multi-user contention, performance and scalability issues, and potential corruption)

I have 10 Years experience in developing and supporting a bespoke Database application that has approx 80 concurrent users - The majority of our data (100GB+) runs off MySQL tables linked into Access front ends, but we still have some tables stored in Access that I am in the process of slowly migrating.

Access has plenty of limitations, but I still don't think it can be beaten for Rapid development of Database applications, using a combination of Bespoke Forms, Queries and Reporting.

russell664 said:
So to the question, I have been looking at guides around on how to do this, creating relationships, what tables do, how data is managed etc... But, non of them seem overally in depth either. I would like to understand how it works, and why things do this, and do that kind of thing. But am unsure exactly where to look in terms of content for this.

Do any of you know any places with some comprehensive guides/information on using and understand Access? Even short courses perhaps?
As of now, I have some very basic knowledge on the subject, but would really like to expand up on it, any suggestions oh smart ones?

Start with something like this on Database theory/design (aimed at MySQL, but still relevant):
http://en.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html

as it will give you a reasonable background for entities, relationships and keys. Something similar should also be covered in any Database book even those specific e.g. to Access.

With regards to picking up Access, any of the normal book ranges should help, personally I always tend to stick with the "Sams - Teach Yourself" range.

Following on from that - for technical resources for Access, including how to do deal with technical issues, and specific tricky tasks - following sites are best resources I have found:

http://allenbrowne.com/tips.html
http://www.granite.ab.ca/accsmstr.htm
http://access.mvps.org/Access/toc.htm
http://www.fmsinc.com/microsoftaccess/developer/index.html

Hope this helps.

Skeeter · 4 May 2015 at 11:44

An Access front end connected to a MySQL database is not an Access database :rolleyes:

You've dismissed my comment as rubbish and then immediately stated the main reason for my comment. Access DBs don't play well with multiple users.

By all means use Access to build the front end, its pretty good at that. But use something better for the back end. That's what I was getting at with my comment, based on the OPs post mentioning no other technology other than Access.

To the OP, its worth finding out what your company has in terms of licensing for DB products. You never know there may be a DBA lurking away in a corner somewhere who would be willing to help you out too.

Armageus · 4 May 2015 at 12:34

Skeeter said:
An Access front end connected to a MySQL database is not an Access database

Perhaps not in the true sense, but our main "Jobs" table (for want of a better name) is still unfortunately on Access. Before we expanded our system (adding numerous tables for "Job Details" and associated log tables to MySQL), our Access Database .mdb was ~800Mb, and worked fine with 30 Concurrent Users.

Skeeter said:
You've dismissed my comment as rubbish and then immediately stated the main reason for my comment. Access DBs don't play well with multiple users.

It's as much about your Application Design as it is Access - if you leave Access to manage any concurrency issues on it's own, then I 100% agree, it will make a mess of it, allowing Users to overwrite each other's data, and given enough chance Corrupt records or even the entire Database.

Our app prevents users from ever editing the same "Job", and as much work as possible has been done to prevent JET from making edits on the same Database Page (Record-Level Locking, Specific Record Sizing to ensure Record/Page splits, Disabling Oplocks, JET registry tweaks - etc.). Even with our core "Jobs" table still using Access, with 80 Concurrent users, we probably encounter Access' "Could Not Update, Currently Locked" error message, maybe once a fortnight. The plan is to get this and the rest of the non-crucial tables moved across to MySQL as well, but due to some bad design decisions by the person who originally created the Database, I am still having some issues with Date related queries that don't work as expected on MySQL.

Skeeter said:
By all means use Access to build the front end, its pretty good at that. But use something better for the back end. That's what I was getting at with my comment, based on the OPs post mentioning no other technology other than Access.

Access is a Jack of all Trades, but as usual - master of none. It has plenty of limitations, but as long as you are aware of them, you can design around them. No other database gives you everything Access does, out of the box for the price.

gpuerrilla · 4 May 2015 at 18:03

There are some great youtube videos to start you off, and the online courses you can take for a few dollars will also help.

A decent book will be nice to switch it up and is great for bog reading material.

Build yourself a few databases that are for different uses and you should gain some core understanding.

russell664 · 7 May 2015 at 11:38

I have been looking around and a work college has recommended to me Lynda.com, which covers all sorts of Access stuff here: http://www.lynda.com/Access-tutorials

I would have thought that would be the kind of thing that I would be looking for?

gpuerrilla · 7 May 2015 at 21:31

Depends on your ability. Some books are naff explaining the same regurgitated spiel of how to use the wizard to build a form!

You need to delve into more practical things like split forms, designing everything from scratch, bit of SQL and then VBA. Sounds deep but the basics are not too hard to make great features.