Results 1 to 4 of 4
  1. #1
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    Access tidbits of knowledge

    Since I like teaching so much, here are some good little pieces of knowledge to have, if you don't already have them:


    Recordsets
    *** When you open a recordset in VBA code, looping cannot be done unless you run the following code on the recordset first:

    Code:
    recordset.movelast
    recordset.movelast
    There is no reason for this, except that it's another oversight by Microsoft that still hasn't been fixed. One of the very few things you can do however, without running the above code, is get the recordcount of the recordset by just opening it. That property is one of few that is correctly returned on open.

    *** If you want the actual record number from a recordset in vba code, you need to use:

    Code:
    recordset.absoluteposition + 1
    Absoluteposition returns the record's index, not it's sequential numbering assignment. Recordset indexes have a base of 0.

    *** You can do just about anything with recordsets and code. There's always a way to do what you want using these two tools. So it can always be a backup plan. Data can always be manipulated this way (unless other factors are preventing it).


    Queries
    *** Stacked queries are underrated. As a matter of fact 3 stacked queries that run mathematical operations on 4 joined tables, the largest one having more than 100,000 records, doesn't even increase a database file size by 100KB (tested on my sample databases). It shouldn't really though. Query sizes are simply equivalent to the object sizes. The data that they query has very little effect, if any, on this size of the object (that I know of). Stacked queries can save you time, and more importantly it can save your brain from having to think too much. Personally, I like to make it easy on myself whenever possible.

    *** The "Unmatched Query" under the query wizard dialog finds records that are broken by a Left Join. You can use it to produce automatic results for you for any situation that's similar to finding Customers (parent table) that have no Orders (child table) records.


    Macros
    *** If you want to learn the code that runs when you ask for a macro action, highlight your macro name in the database window and click the "convert macros to visual basic" button in the ribbon/menus. Most macro actions end up being converted to vba one-liners, so it's a really easy way to learn what the program is doing for each macro that you've created (and an easy way to learn some coding).


    Miscellaneous
    *** There is no point in password-protecting an Access database (that I know of). There are tools on the web to recover passwords. Here is one site: http://www.nirsoft.net/utils/accesspv.html. That only recovers .mdb file passwords, but you can bet there have been updates to this by someone else that extracts the same information from .accdb files and/or the ACE database engine.

    *** If you're having a difficult time tracking what queries pull from what tables (and other issues), check out the "Object Dependencies" button in the ribbon (possibly in the menus in pre-07 versions). This shows you a list of your data, where it comes from and what objects in your database depend on the sources of that data to execute themselves.

    *** The "Database Documenter" button in the ribbon does some pretty useful things. It can basically give you a print out of object features in your database. It should be recognized as a useful tool.


    Forms


    *** Unlike stacked queries, forms are extremely overrated (IMO). Personally, if Access had no form objects I would be in heaven. These are the most tedious objects to work with and develop. There really is no way around it. You can't automate it (without many headaches) or speed up the process of developing them. The only other interface forms out there (that I know of) that are easier to work with are perhaps internet forms as they only require HTML knowledge to develop them, and they certainly don't have as many complications and trinkets accompanying them as Access forms do!

    *** Developing your own forms does take a lot of time, and there are endless amounts of templates out there (designs). It might be worth the time to use someone else's ideas for your database instead of wasting time reinventing the wheel. Or even semi-reinventing the wheel.


    Lookup Functions
    *** These are extremely slow. Avoid them at all costs. I'm not really sure why functions like DLookup() are so slow but I would venture to guess that it's due to the fact that more resources are required by the database engine. For instance, DLookup() requires it to request a full domain (a dataset) and then find a specification in that domain. So that's like running a full query through the interface.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,688
    Quote Originally Posted by ajetrumpet View Post
    Recordsets
    *** When you open a recordset in VBA code, looping cannot be done unless you run the following code on the recordset first:

    Code:
    recordset.movelast
    recordset.movelast
    First, I assume you meant the second line to be a MoveFirst. In any case, in my experience this is not true (with DAO anyway, which is what I typically use). Here's code out of a customer's db I'm working on at this moment:

    Code:
        ...
        Set rsInvoices = db.OpenRecordset(strSQL, dbOpenDynaset)
    
        Do While Not rsInvoices.EOF
         ...
         rsInvoices.MoveNext
        Loop
    No moves through the recordset are required for the loop to work correctly, and I have never included them.

    I would further disagree with "One of the very few things you can do however, without running the above code, is get the recordcount of the recordset by just opening it". In my experience, you have to move to the end of the recordset to get an accurate record count. A quick test:

    Code:
       ...
      strSQL = "SELECT res_num FROM tblDispReser"
      Set rs =  db.OpenRecordset(strSQL, dbOpenDynaset)
      Debug.Print "records before  movelast: " & rs.RecordCount
      rs.MoveLast
      Debug.Print "records  after movelast: " & rs.RecordCount
       ...
    results in:

    records before movelast: 1
    records after movelast: 417648
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    And I concur with everything Paul wrote. In addition:

    Quote Originally Posted by ajetrumpet
    Macros
    *** If you want to learn the code that runs when you ask for a macro action, highlight your macro name in the database window and click the "convert macros to visual basic" button in the ribbon/menus. Most macro actions end up being converted to vba one-liners, so it's a really easy way to learn what the program is doing for each macro that you've created (and an easy way to learn some coding).
    If you are using Access 2010 (but not SP1) then you are out of luck with the convert embedded macro to VBA as that is a bug that I am not sure if it is fixed in SP1 or not (I've not installed SP1 due to the ADO compile issue that it introduces)

  4. #4
    rollinshultz is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Oct 2014
    Posts
    3
    Personally, I like forms and I like creating them. To develop styles of forms I often use my CAD programs to develop images and backgrounds, as I hate any kind of plain Jane looking forms. If a user must spend all their time looking at them, and they must if you intend to protect the DB from them, then their should be a certain appeal just like you wouldn't want to surf shabby websites. Most form design can be quick and easy if you plan the look and function before you create any controls. This way you can format one label and copy it, one textbox and copy it, and one button and copy it, just work out the entire format before you make the first of each.
    Strong form development, means a strong and appealing UI and a safe DB, and that endears you to customers. It would be nice to have some XAML capability in Access someday and some silverlight as well, then form design can take off.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-02-2010, 05:01 AM

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 - Senior Forums