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

    Software Bugs in MS Access

    All,

    This is for those of you out there that are unaware of the many low-level shortcomings of Access. Unfortunately, many people rely on this program for database needs, but the low-level programming done by Microsoft engineers (which continues to be relied upon by the general public) is not properly done (nor has it probably ever been). This thread outlines some of the major inconsistencies/bugs with Microsoft Access that I've found (tested with 2007 and 2010 only):


    1) Querying/Storing [Blanks] in Tables
    • Both through the interface and with code, querying a value that is comprised completely of blanks/spaces (regardless of the amount of blank space used in the value) can be specified with any length of "blank value" criteria, including a zero-length string.

      For instance, Access will return a value of " " (3 blank spaces) with any of the following criteria listed:

      Code:
      SELECT * FROM [table] WHERE [field] = " "
      Code:
      SELECT * FROM [table] WHERE [field] = ""
      Code:
      SELECT * FROM [table] WHERE [field] = "            "
      Conversely, the program will also return inconsistent value strings like this if the actual value is a zero-length string but the query asks for values with any number of blank spaces.



    2) Deleting Values in Tables With Keyboard VS. Update Queries
    • Manual deletions/blanking of values in tables with the keyboard automatically result in the value being updated to NULL (this includes using the Find/Replace feature). Conversely, Access regards update queries as LITERAL. For instance, this update query will produce a zero-length string value in a field:

      Code:
      UPDATE [table] SET [table].[field] = ""
      But oops, the next time you query that field with something like " " in the criteria, you'll get your zero-length string record along with the records you do actually want.



    3) Program-Generated Query Aliases Named "Expr#"
    • This is simply a consequence of very poor attention to detail on Microsoft's part. I've seen query statements that have been automatically manipulated by Access in a variety of scenarios. And most of them result in the following happening to an Access query statement (or to a portion of the statement):


      • Query fields are automatically updated with "Expr#" alias names. In other words, every field in the SELECT statement portion of the query has a " AS [Expr#]" string appended to the end of it.


      Finding the cause of this stuff is probably impossible, because more than likely there is more than one cause. But in short, it's reasonable to say that a change in table definition will result in high risk that this will happen to a query that's requesting data from that table. From my experience, these are the things not to do if you want to minimizing the risk of being infected by this "alias field name" bug:


      • Dynamically deleting a table and then re-creating it from a [code] source/application outside of Access (eg. - vb6/vb.net/sql server).
      • Delete a field in a table (that is needed by an Access query) from a [code] source/application outside of Access.
      • Creating queries or updates to query statements in an Access database that are based on non-existent tables at the point in time that the query is actually created/updated.
      • Appending data from a temp table to a source table that is the basis for a query.
      • Dynamically creating/updating queries or query statements with code, the source being either Access VBA or from an outside interfacing application like vb.net.



    4) Switching From SQL-View to Design-View in the QBE


    • These 2 views of the query builder operate in completely different manners. From my experience anyway, the primary difference can basically be understood by accepting the fact that SQL-view is completely unregulated by Access. In other words, the SQL you manually write in SQL-view will be literally executed by Access without any automatic program/SQL syntax manipulation.

      The problem with this comes into play if a developer makes that drastic switch from SQL-view to Design-view in the query builder. When this happens, Access automatically adds any manipulation (usually syntax-based) to the query statement that it needs to satisfy itself (probably object-oriented referencing needs of somekind).

      The most common mishap that I've seen is the automatic "Expr#" alias names that appear with selected fields that access cannot internally reference. For instance, assume you're selecting [field1] from [table1] but [field1] is created dynamically at runtime instead of being immediately present in the table. Now assume that you have a stacked query (query 2) that needs query 1's dynamically-created field. Well, query 2 will be looking for [fieldName] but in query 1 the name will actually be [Expr#]. Access just manipulated query 1's sql statement that will result in a runtime error during execution.

      Another common issue that results from this "switching-of-views" (or another sequence of developer actions which is probably still unknown) is when 2 query fields end up with the same "Expr#" alias name. And obviously what follows during execution is this error:

      Duplicate output alias 'Expr 1'
      I believe that this specific change has more than one cause. The lowest-level reason behind this error, when it happens due to sql manipulation by Access itself, is probably still unknown. I've yet to see a believable answer to this on the web anywhere.



    If you have anything to add to this list, please do so and help out other developers. Or if my observations are not completely correct, please say something. There is nothing that personally irritates me more than spending any amount of time discovering sloppy mistakes made by software manufacturers that cost other people money. There is no doubt that from this point on, the inconsistencies between versions of Microsoft Office software releases will get worse, or at the very least will be more abundant. That's simply Microsoft's legacy. No reason for them to stop now!

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    I have two to add..

    You cannot change printer settings on a report in VBA if that report has any code behind it. I do have a work around though: http://techie-tid-bits.blogspot.com/...to-access.html

    Second, if no ribbon is assigned to your start up form, there is a chance (not always) that all ribbons will disappear on the rest of your application, even when one is specifically assigned to a report or form.

  3. #3
    MercyBrew is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2015
    Posts
    1

    Fields With Trailing Spaces Are Affected Too

    Quote Originally Posted by ajetrumpet View Post
    All,

    1) Querying/Storing [Blanks] in Tables

    • Both through the interface and with code, querying a value that is comprised completely of blanks/spaces (regardless of the amount of blank space used in the value) can be specified with any length of "blank value" criteria, including a zero-length string.

      For instance, Access will return a value of " " (3 blank spaces) with any of the following criteria listed:

      Code:
      SELECT * FROM [table] WHERE [field] = " "
      Code:
      SELECT * FROM [table] WHERE [field] = ""
      Code:
      SELECT * FROM [table] WHERE [field] = "            "
      Conversely, the program will also return inconsistent value strings like this if the actual value is a zero-length string but the query asks for values with any number of blank spaces.
    This problems also affects text fields with values that have trailing spaces. For a given text criteria, Access will return results that have trailing spaces with your criteria. So a criteria of say "Bug" will return results containing "Bug ", "Bug ", etc. I have avoided this bug by using the Len() function with the criteria as follows:

    Code:
    WHERE ((([Field])="Criteria") AND ((Len([Field]))=Len("Criteria")));

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

Similar Threads

  1. Software Table
    By jenncivello in forum Access
    Replies: 3
    Last Post: 11-18-2011, 03:07 PM
  2. VBA/Access form Detect return of software
    By superfury in forum Programming
    Replies: 1
    Last Post: 07-06-2011, 07:52 AM
  3. Replies: 0
    Last Post: 08-17-2010, 12:44 PM
  4. Training and software
    By fsmikwen in forum Access
    Replies: 1
    Last Post: 12-21-2009, 06:58 PM
  5. Bugs, errors in 2007
    By Canadiangal in forum Access
    Replies: 3
    Last Post: 09-17-2009, 11:25 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