Results 1 to 12 of 12
  1. #1
    seamonkey is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2018
    Location
    San Diego
    Posts
    3

    MS Access and MS VBA Courses / Help

    Hello,

    I'm a newcomer to this forum and I am hoping you guys can give me some advice. I just took over responsibility of managing a MS Access database and the person I am taking over from is no longer working where I work, so I am on my own. And there is no other resident MS Access or MS VBA expertise resident here either. I have read the MS Access For Dummies book cover to cover and I had about a day's discussion with the person who just left to understand the design and functionality of the MS Access database. After reading the book, I was at least able to follow along because I could understand the language that he was speaking. I was also able to ask some insightful questions and I took copious notes. The database has a BE and a FE, and also uses some MS VBA programming.

    My aim is to fully understand how and why the database was built as it was (there was no written record of this), be able to modify the functionality of the database (in order to eventually improve upon what was already built - it is by no means a perfect or finished product), be able to navigate (confidently) my way through the database (without fearing that I will screw something up), and to become a resident MS Access / VBA expert.

    My question to the forum are these:

    1. I am interested in taking some MS Access and MS VBA courses - are there any specific companies that you know are reputable and do a good job at teaching MS Access & MS VBA?
    2. Are there any good books that you would recommend as my next step after the MS Access for Dummies book? I have a ton checked out from the library, but not sure which one to crack open next. They are:
    -MS Access 2010 Plain & Simple by Curtis D. Frye
    -MS Access 2010 Step by Step by Joyce Cox and Joan Lambert
    -MS Access 2010 Inside Out by Jeff Conrad and John Viescas
    -MS Access Data Analysis by Larry Rockoff
    (Also, I feel the books teach me the basic funtionality, but my actual database is much more advanced...so the books seem helpful only to a point.)


    3. Do you have any advice on how to map out the database / break it down into it's individual components to understand it better?

    The BE database has 23 tables and the FE database has approx 100 queries, 30 forms, 50 reports, and 6 modules and about 20 VBA codes written.

    Thanks in advance for your assistance! Anything is appreciated.

    Kindly,
    Stephanie

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link that I often provide for posters who are looking for books or info on Access. It is often better to step back and review database concepts before focusing directly on Access. However, since you have an existing database, these background materials may help put things into context.

    Also, since you know:
    The BE database has 23 tables and the FE database has approx 100 queries, 30 forms, 50 reports, and 6 modules and about 20 VBA codes written.
    You might want to review any comments or properties that exist in these objects; then either create or extend/rewrite such materials in your own words (based on your new found knowledge).

    You may find this article useful
    and this one
    Database documentation can reference many aspects.

    There could be a requirements document for what this database was intended to support. That is, the business problem and the pieces to be automated.
    There could be analysis and conceptual design documents to overview findings a put the 30,000 foot picture in front of those who identified the initial need.
    As detailed analysis evolved several documents could be fleshed out to show specifics of design considerations and the rationale for choosing same.
    A system configuration chart/document showing what was implemented and how the pieces fit together in some picture type format. This is typically aligned with more detailed technical documentation that describes each object (much of what you are asking re tables, queries, forms etc)
    There are users who need to know what to do and when to take full advantage of this database application including their roles and responsibilities.
    A system/operations document describing how to start and stop the application; backups; recovery and related information.

    Good luck with your project.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The first thing to learn is database design (applicable to all database systems, not just Access). Once you have a handle on the table structure within your database, how it was designed, what is stored where and why - then you will have a far better understanding once you begin looking at the rest of the database.

    The internet is full of free tutorials and articles to navigate your way thru Access, altho these need specific target areas as opposed to books which cover so much more. The Dummies book is a good place to start, I don't think you should tackle those other books just yet, except maybe the last one if it covers table design.

    If you plan on making extensive changes to this database, then an understanding of table design is a requirement. 23 tables sounds like a huge database!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll bet your supervision is more interested in you maintaining/fixing/embellishing the current db as opposed to you making yourself into the new Access guru. With that in mind, my recommendation would be to print out the existing code (which probably wasn't sufficiently commented so that you could grasp the intent of it) and sit down to read it. Armed with pen, write every question you have about the code, near that code, then explore (Google) your questions. There are TONS of videos and websites out there, which might be more conducive to what I think is your immediate task, since you will be able to focus your research on aspects of your current db. Once management sees you're able to at least provided the necessary coverage to the current db, they should be more receptive to you taking outside courses if that's your wish (which they should pay for). Likely, their issue would be that to make you into the next guru is only good for as long as you remain employed there, a fear which doesn't usually bolster your request.

    While you're learning how to maintain your db, watch for variations on how basic things are done (such as naming things) and add to your question list as you go. I'd advise you to take the approach that your goal is to achieve a level of proficiency that allows maintenance and improvement of the current db, and to be able to suggest new applications to make business processes or decision making more effective after you've learned why things are done a certain way, not just how.

    Access has a database documenter you might want to look at, but I don't think it allows you to print out code. Best advice I can give right now is to play with copies of the database BUT you must understand the way a be and fe is linked. If you copy both to your personal drive and play with the data, guess which one you modify - the main application/production one. In your copies, you must break the be links and relink your copy fe to your copy be.

    BTW, without knowing anything of the business process for the db, it may not be fair to say that the number of queries, forms and reports makes me think that there might be some unnecessary duplication of these objects. If for example, if 2 reports are quite similar in appearance, but are based on different departments, it's likely they don't both need to exist.
    Last edited by Micron; 01-17-2018 at 12:23 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by aytee111 View Post
    23 tables sounds like a huge database!
    Doesn't sound at all huge to me.
    Most of my databases have far more than that. At least one has well over 300 tables

    @seamonkey
    As well as the previous advice, I recommend you watch the series of Access videos by Steve Bishop on You Tube (over 100 in all)
    They cover all stages from basics through intermediate to advanced
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My comment was not about tables. If you disagree with what I gave as an example of my meaning, by all means, let us know.
    I have seen db's with thousands of tables (certainly not Access db's) so I don't think that's relevant to my example.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I was talking from the poster's point of view, not the experienced answerers.

  8. #8
    seamonkey is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2018
    Location
    San Diego
    Posts
    3
    Wow, great feedback!

    @orange: Thank you for the links, I will check them out.
    @aytee111: I currently have a very basic understanding of the database design. I am looking to increase my understanding, but I know it will take time and effort as I work my way through the learning process.
    @Micron: Yes, the priority right now is currently maintaining/fixing/embellishing/operating the db. When you say print out the existing code, are you referring to the VBA code? That's a good idea - to print out the VBA code and make comments on it and track down answers. That will help me keep track of my thoughts/answers. I did also check to see what the Database Documenter gave me - it was over 900 pages. I did not print that out b/c it was so long, but I could save it/work from there. Do you think that would help me - to do the same thing there as I will be doing to the VBA Code? As far as the understanding of the way the BE and FE are linked, it seems pretty simple (although I recognize that I may just be naive in knowledge of what you mean). I'll explain more about what I know about the db below. I do have a personal (experimental) copy on my desktop. I do think it's a good idea to increase my knowledge of the db as well as maybe read some more books, videos, links to get a baseline knowledge before taking any courses. I will tell you, the Dummies book took me from a never before MS Access user to at least a basic working knowledge of everything and being able to ask better questions. So more self-learning first would yield a better return on any future courses I may take. My place of work will be funding the courses, although I have not yet broached the topic with my boss. I think they would be receptive to it, however. You may be right about the duplication. After the 1-day turnover with the person who maintained it before - he mentioned that some ideas were never finished and that it was created as they went along (trial and error).

    The BE has 23 tables. Information is filled out in some of the tables (standard info that doesn't change), while the FE was designed to be a giant input form (with multiple smaller forms) and can generate reports. (I will be the only one inputting info into and maninpulating data in the database.) The database will manage a schedule of events, over a period of approx 2 months (this coming June through August), with many different types of data that need to be captured for each schedule of event line item (over 10,000 in total). The database will work in it's current form, but it needs improvements over the long run. It will need a good "refresh" to improve current functionality, increase functionality, and improve simplicity.

    Oh, I also checked out books on VBA:
    -Microsoft Access VBA Programming 4th ed for the absolute beginner by Michael Vine
    -Sams Teach Yourself Visual Basic in 24 Hrs by James Foxall
    -Microsoft Visual Basic 2010 Step by Step by Michael Halvorson
    -Visual Basic 2010 Unleashed by Alessandro Del Sole

    Wrt your 2nd post - I believe I am on the same page. I was not confused by your first comment.

    @ridders52: Thanks for the rec on Steve Bishop - will check him out.

    Thanks for the input everyone, I really appreciate it! Wow, great feedback so far!

    @orange: Thank you for the links, I will check them out.
    @aytee111: I currently have a very basic understanding of the database design. I am looking to increase my understanding, but I know it will take time and effort as I work my way through the learning process.
    @Micron: Yes, the priority right now is currently maintaining/fixing/embellishing/operating the db. When you say print out the existing code, are you referring to the VBA code? That's a good idea - to print out the VBA code and make comments on it and track down answers. That will help me keep track of my thoughts/answers. I did also check to see what the Database Documenter gave me - it was over 900 pages. I did not print that out b/c it was so long, but I could save it/work from there. Do you think that would help me - to do the same thing there as I will be doing to the VBA Code? As far as the understanding of the way the BE and FE are linked, it seems pretty simple (although I recognize that I may just be naive in knowledge of what you mean). I'll explain more about what I know about the db below. I do have a personal (experimental) copy on my desktop. I do think it's a good idea to increase my knowledge of the db as well as maybe read some more books, videos, links to get a baseline knowledge before taking any courses. I will tell you, the Dummies book took me from a never before MS Access user to at least a basic working knowledge of everything and being able to ask better questions. So more self-learning first would yield a better return on any future courses I may take. My place of work will be funding the courses, although I have not yet broached the topic with my boss. I think they would be receptive to it, however. You may be right about the duplication. After the 1-day turnover with the person who maintained it before - he mentioned that some ideas were never finished and that it was created as they went along (trial and error).

    The BE has 23 tables. Information is filled out in some of the tables (standard info that doesn't change), while the FE was designed to be a giant input form (with multiple smaller forms) and can generate reports. (I will be the only one inputting info into and maninpulating data in the database.) The database will manage a schedule of events, over a period of approx 2 months (this coming June through August), with many different types of data that need to be captured for each schedule of event line item (over 10,000 in total). The database will work in it's current form, but it needs improvements over the long run. It will need a good "refresh" to improve current functionality, increase functionality, and improve simplicity.

    Oh, I also checked out books on VBA:
    -Microsoft Access VBA Programming 4th ed for the absolute beginner by Michael Vine
    -Sams Teach Yourself Visual Basic in 24 Hrs by James Foxall
    -Microsoft Visual Basic 2010 Step by Step by Michael Halvorson
    -Visual Basic 2010 Unleashed by Alessandro Del Sole

    Wrt your 2nd post - I believe I am on the same page. I was not confused by your first comment.

    @ridders52: Thanks for the rec on Steve Bishop - will check him out.

    Thanks for the input everyone, I really appreciate it!

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @micron
    I made no reference to your post...

    @aytee
    Fair enough. I just read it literally rather than from the viewpoint of a newbee...

    If I offended either of you, that wasn't my intention
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ridders, I owe you an apology. I thought your comment was in regard to it may not be fair to say that the number of queries, forms and reports .

    are you referring to the VBA code?
    Yes, you'd have to copy/paste it into a document/text file.
    The db documenter has options. You can pare it down by eliminating those that probably won't help or be as useful. For example, you could not include tables and use a picture of the relationships window instead, but I think the code copy would be more useful right now. I just thought the documenter might provide some useful info about the relevant objects. As for which approach you take in learning, you would be the best judge of your situation and preference of learning styles. Best you can do is consider any advice in your approach.

  11. #11
    seamonkey is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2018
    Location
    San Diego
    Posts
    3
    @orange: For some reason, this section didn't show up when I first viewed your post:

    "Database documentation can reference many aspects.

    There could be a requirements document for what this database was intended to support. That is, the business problem and the pieces to be automated.
    There could be analysis and conceptual design documents to overview findings a put the 30,000 foot picture in front of those who identified the initial need.
    As detailed analysis evolved several documents could be fleshed out to show specifics of design considerations and the rationale for choosing same.
    A system configuration chart/document showing what was implemented and how the pieces fit together in some picture type format. This is typically aligned with more detailed technical documentation that describes each object (much of what you are asking re tables, queries, forms etc)
    There are users who need to know what to do and when to take full advantage of this database application including their roles and responsibilities.
    A system/operations document describing how to start and stop the application; backups; recovery and related information.
    Good luck with your project"

    Thanks for explaining the different types of database documentation - this is helpful.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Training Courses
    By pmangan in forum Access
    Replies: 5
    Last Post: 10-17-2017, 01:49 PM
  2. Compare teachers and their courses
    By cmyers in forum Queries
    Replies: 4
    Last Post: 07-18-2014, 12:06 PM
  3. Comparing Promotion Requirements with courses
    By ahmad_3011 in forum Access
    Replies: 16
    Last Post: 06-19-2013, 04:45 PM
  4. Basic DB to record Students Courses PLEASE HELP
    By littleliz in forum Database Design
    Replies: 5
    Last Post: 09-14-2010, 02:58 PM
  5. courses query
    By lolo in forum Queries
    Replies: 0
    Last Post: 04-23-2010, 01:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums