Language: Deutsch English















Last Update: 2024 - 01 - 25








MS Access

Table of Contents

  • All columns in your database
  • How to create an AutoNumber-Column with VBA
  • DAO - Check if a field exists in a table
  • What is the /decompile switch for Microsoft Access?
  • How to use transactions in Microsoft Access VBA
  • How to output the values from a column as a comma-separated string
  • 3 tools I would not want to work without in Microsoft Access
  • VBA - Handling events from an indefinite number of event sources
  • Access/VBA - Event handling Tutorial (Part 1) - First steps for beginners
  • Making an Access AccDE (MDE / ADE) by code (VBA/VB-Script)
  • Access SQL - Select Top X records - With no ties
  • 7 reasons to use source code control for Microsoft Access development
  • DCount vs. SELECT COUNT performance
  • How to change the RecordSource of an Access 2010 Form in PivotChart-View with VBA
  • Sort and index binary data
  • Logical numerals sorting in Access with VBA
  • Let external users review and edit data
  • Queue based applications - Key issues and solutions
  • Databases in OneDrive, DropBox or Offline-Folders
  • How to create a calculated field in a Microsoft Access table
  • Calculated Fields in an Access Query
  • Issues with the Hyperlink data type in Microsoft Access
  • Digitally signing VBA Code in .accdb databases
  • Export a filtered Access Report to PDF using VBA
  • The Date/Time Data Type in VBA and Access
  • Validation Rules and Check Constraints in Access
  • The GDPR for Microsoft Access developers
  • Microsoft Access - Error 3048 - Cannot open any more databases - after Office Update 2201
  • The /LARGEADDRESSAWARE (LAA) flag demystified
  • Access and VBA Book Recommendations
  • How to check if an Access Form is open in dialog mode
  • Aggreated news on Access 2019 and Office 2019
  • Problems with DFirst/DLast and (wrongly) Sorted Result Sets
  • The Microsoft TreeView Control in 64bit Access
  • Gallery PDF for Office imageMso icons
  • Microsoft Access developer conferences around the world.
  • How to format Date/Time for a specific language and country
  • "Query is corrupt"-error after Microsoft Access November 2019 Update
  • Resize an Access DataSheet Form to Fit all Columns
  • Recap of Access news in 2020 and a Peek at 2021
  • Query Performance Tuning - Basic Universal Rules
  • Update Parent Form with Sub Form References
  • Warnings and Trust - Trust Center Settings in Microsoft Office



  • All columns in your database

    This is a small sample of a function that prints the names of all the columns of all the tables in your database to the direct window of the VBA environment.

    Sub allColumns() Dim x As Integer Dim y As Integer Dim db As DAO.Database Set db = CurrentDb For x = 0 To db.TableDefs.Count - 1 Debug.Print db.TableDefs(x).name For y = 0 To CurrentDb.TableDefs(x).Fields.Count - 1 Debug.Print CurrentDb.TableDefs(x).Fields(y).name Next Next db.Close Set db = Nothing End Sub


    Back to the TOC

    How to create an AutoNumber-Column with VBA

    Sometimes you need to create a new table in your database with VBA. You can achieve that with a DDL-SQL-Statement, but another option is to use the DAO Object Library. Using the TableDef-Object that is pretty straight forward.

    The only issue that may arise is how to create an AutoNumber-Column in your table. There is no specific constant for an Auto Number in the DataTypeEnum.

    Long Integer Auto Number

    If you want to create a Long Integer Auto Number with code, you create a new Field of type dbLong. Then you set the dbAutoIncrField Attribute for this column.

    This sample demonstrates how you create a Long Integer AutoNumber-Column with VBA using the DAO object library.

    Sub createAutoIncrField() Dim td As DAO.TableDef Dim db As DAO.Database Dim fld As DAO.Field Set db = CurrentDb Set td = db.TableDefs("tblYourTable") Set fld = td.CreateField("AutoNumberColumn", dbLong) fld.Attributes = fld.Attributes Or dbAutoIncrField td.Fields.Append fld End Sub

    GUID Auto Number (Replication ID)

    In the design view of a table you can also create an Auto Number field of type Replication ID.

    A Replication ID is a GUID (Globally Unique Identifier), a 128 bit number, usually displayed in hexadecimal notation. Example: 6706EF5C-C09E-48E4-A710-DA100C5B1674.

    Usually GUIDs are created randomly. The creating algorithm should however try to make it extremely unlikely that one and the same GUID is created twice.

    Access is having some difficulties with GUIDs. Therefor you should consider carefully if you really want to use a GUID in your database.

    The results when creating a GUID with VBA-Code are not exactly identical to creating on manually. Still, I think they are close enough.

    You need to use the TableDef-Object to create a new Field of type dbGUID. Then you assign the function GenGUID as DefaultValue to this field. GenGUID is a pseudo-function, known only to the Jet-/ACE-Database-Engine internally. It can be used only for this specific purpose and cannot be called directly.

    Here is the code to create a Replication ID Auto Number.

    Public Sub CreateGUIDAutoNumberColumn() Dim td As DAO.TableDef Dim db As DAO.Database Dim fi As DAO.Field Set db = CurrentDb Set td = db.TableDefs("tblYourTable") Set fi = td.CreateField("YourGUIDColumn", dbGUID) fi.DefaultValue = "GenGUID()" td.Fields.Append fi Set td = Nothing Set db = Nothing End Sub

    The field is not identical to a manually created Field. Instead of (New) a new record will display #Name? in the Auto Number column. In table design the column is of type Number and not of Auto Number.

    Access Table datasheet, comparing GUID columns Access Table designer, comparing GUID columns

    I could not recognize any differences in behavior between a manually create GUID Auto Number and one created by VBA-Code.



    Back to the TOC

    DAO - Check if a field exists in a table

    Sometimes you want to check if a field in a table exists. Unfortunately there is no method to check this built into the DAO Library.

    Here is a function that simply tries to assign the field in question to DAO.Field variable. If the field does not exist, this will result in a runtime error and execution of the function will conclude with the fieldExists_Exit jump label, thus returning false.

    This function is quick and dirty, but it solves the problem.

    Public Function FieldExists(tableName As String, fieldName As String) As Boolean On Error GoTo fieldExists_Exit Dim fld As dao.Field FieldExists = False Set fld = CurrentDb.TableDefs(tableName).Fields(fieldName) FieldExists = True fieldExists_Exit: Set fld = Nothing End Function

    I published the above solution more than ten years ago in German on this website and it is included in my standard library I use in all my projects. It works without a problem.

    But, now as I translate it to English, I cannot suppress my aversion against provoking runtime errors with the "normal" execution of my code.

    So I wrote another implementation of a FieldExists-Function, which loops through all the fields in the table and exits the loop if a fields with the searched for field name. Here is the other implementation.

    Public Function FieldExists(tableName As String, fieldName As String) As Boolean Dim db As dao.Database Dim td As dao.TableDef Dim fld As dao.Field Dim i As Long FieldExists = False Set db = CurrentDb Set td = db.TableDefs(tableName) For i = 0 To td.Fields.Count - 1 If td.Fields(i).Name = fieldName Then FieldExists = True Exit For End If Next End Function

    I like this code much better. - Now I am curious how both of them compare regarding performance. I expect the function looping through all the fields to be significantly slower, if the field does not exist in the table and the loop runs to the end of the Fields-collection.

    So set up a little performance comparison experiment. I called each variant of the FieldExists-Function for a table with 60 fields looking for a non-existing field in a loop. – The surprising insight is that there hardly is a difference between the two implementations performance wise. I need to raise the number of executions of the loop to more 100,000 to measure it at all. Finally with 500,000 executions my assumption is clearly confirmed.

    Implementation 1 (with catching the error) takes 2 seconds to run 500,000 times. Implementation 2 (with the loop through the fields) takes 11 seconds.

    So the conclusion is, while there is a performance difference between both implementations, it is absolutely negligible in all real world scenarios I can imagine for this function. So go ahead and use the implementation you personally like best.



    Back to the TOC

    What is the /decompile switch for Microsoft Access?

    The /decompile switch for Microsoft Access is a proven yet somewhat mysterious tool to remedy corruption in the VBA project of an Access database. So, what is /decompile about?



    Back to the TOC

    How to use transactions in Microsoft Access VBA

    Transactions can be used to group several queries into a single unit of work. Read how to use transactions in VBA with a Microsoft Access database to do that.



    Back to the TOC

    How to output the values from a column as a comma-separated string

    Here is a detailed explanation of how to output the values from a column as a comma-separated string using VBA code and a DAO Recordset in Microsoft Access.



    Back to the TOC

    3 tools I would not want to work without in Microsoft Access

    This is a pointer to an article about my favourite tools (add-ins) for development work in Microsoft Access and VBA.



    Back to the TOC

    VBA - Handling events from an indefinite number of event sources

    Inspired by a question on a forum I wrote an extensive article on how to handle events from a whole collection of objects in VBA. The article is much(!) to long for this page, so please follow the link to read it.



    Back to the TOC

    Access/VBA - Event handling Tutorial (Part 1) - First steps for beginners

    The first part in the VBA event handling seriens is an extensive step-by-step tutorial on event handling for beginners. This is the longest article on this whole site so far, so is to long for this page, so please follow the link to read it.



    Back to the TOC

    Making an Access AccDE (MDE / ADE) by code (VBA/VB-Script)

    An article on an issue I encountered when I wrote a VB-Script to make an AccDE/ADE/MDE file by code. The problems arises from using the undocumented Action 603 for the SysCmd-Function and the solution illustrates two lessons every developer should learn.



    Back to the TOC

    Access SQL - Select Top X records - With no ties

    If you try to query the TOP x records from a tables or query in Access you might get more than the expected number of results if there are equal values in the column you order by. This article describes how to query exactly X records with plain sql in multiple scenarios.



    Back to the TOC

    7 reasons to use source code control for Microsoft Access development

    In this article I explain the 7 most important reasons I use source code control in all my Access development projects. Even if you know and use source code control already, you might find it interesting to read my point of view and maybe you still learn something new.



    Back to the TOC

    DCount vs. SELECT COUNT performance

    An article comparing the performance of DCount and SELECT COUNT to count records in a table of a Microsoft Access Database.



    Back to the TOC

    How to change the RecordSource of an Access 2010 Form in PivotChart-View with VBA

    A short introduction into how to programm an Access 2010 Form in PivotChart-View with VBA. Particularly, how to change the RecordSource at runtime with VBA.



    Back to the TOC

    Sort and index binary data

    Do you know the Binary data type in Access? It might be rarely used, but if you ever come across a situation where you need it, this article on sorting and indexing binary data in Access is probably invaluable.



    Back to the TOC

    Logical numerals sorting in Access with VBA

    Did you notice the logical sort order of files containing numbers in Windows Explorer? Wouldn’t it be user friendly to use the same sorting algorithm in your Access application? Here is how you can use logical numeral sorting in Access.



    Back to the TOC

    Let external users review and edit data

    Occasinally you would like to have external users review and edit data from your Access database without giving them direct access to the main database. Here is a purely theoretical article discussing three approaches to allow external users indicrect access to the data in your db.



    Back to the TOC

    Queue based applications - Key issues and solutions

    If you don’t consider the possible issues you can easily create a queue application that exhibits frustrating, hard-to-debug errors in a production environment. In this article I describe key strategies to implement a solid queue management to prevent common errors and issues.



    Back to the TOC

    Databases in OneDrive, DropBox or Offline-Folders

    DropBox, OneDrive and Offline-Folders are modern technologies to access files from anywhere, even completey offline, edit those files and have the changes synchronized everywhere. You can even share files with other users and have theri changes synced as well. However, if you try that with an Access database the results will most likely not be what you expected. Read this article to learn why DropBox, OneDrive and Offline-Folders will destroy your Access database.



    Back to the TOC

    How to create a calculated field in a Microsoft Access table

    In Access 2010 Microsoft introduced calculated fields in Access databases. This article is the most in depth decription of this feature available on the web today. It discusses all aspects of these new fields including when to use them at all. It shows a step-by-step practical example of how to copute the quarter of date in a calculated table field. Including sample code to create such a field with VBA.



    Back to the TOC

    Calculated Fields in an Access Query

    Other than in Excel you do not calculate in Access Tables. Still, Access supports simple mathematical calculations as well as the use of complex custom VBA functions. - The results can be displayed in calculated fields in an Access Select Query.



    Back to the TOC

    Issues with the Hyperlink data type in Microsoft Access

    The Hyperlink data type in Access looks like a convenient solution to get the functionality of hyperlinks for any text field in your database with just a single selection in the table design. - Don't do that lighheartedly! This datatype can create much more problems, than it solves. Here is an article showing solutions to the issues with the Hyperlink data type in Access.

    Back to the TOC


    Digitally signing VBA Code in .accdb databases

    Signing executable code is a good idea. I absolutely support this in general. - But signing VBA Code in .accdb databases will not deliver what you expect.



    Back to the TOC

    Export a filtered Access Report to PDF using VBA

    Opening an Access report with dynamic filter criteria is pretty simple. Exporting an Access report to a PDF file is simple as well. Combining the two operations into one might look like a challenge. - It isn't if you know how to export a dynamically filtered report to PDF.



    Back to the TOC

    The Date/Time Data Type in VBA and Access

    While dates in Access and VBA seem to be simple and straightforward at first sight, there are actually quite a few pitfalls connected to handling dates. I provide comprehensive background information and give you some guidance to avoid common problems when working with dates in Access and VBA.



    Back to the TOC

    Validation Rules and Check Constraints in Access

    They work almost invisibly in the background, nevertheless they are an important tool to ensure data integrity in your Access Applications. Therefor I wrote this summary about Validation Rules and Check Constraints in Access tables. It includes an advanced techique you most likely never heard of.



    Back to the TOC

    The GDPR for Microsoft Access developers

    The GDPR (General Data Protection Regulation) - Burden or boon? - Am opinionated text on why adopting the GDPR is an advantage for your software developer business and a slighly less opnionated guiding whitepaper on how to do so.



    Back to the TOC

    Microsoft Access - Error 3048 - Cannot open any more databases - after Office Update 2201

    Microsoft did it again. After the January 26th 2022 Microsoft Office Update 2201 (Build 14827.20158) there are numerous reports on an error Error 3048 - "Cannot open any more databases".



    Back to the TOC

    How to check if an Access Form is open in dialog mode

    An Access form can be opened normal or as dialog. How to find out if a form is open in dialog mode?

    .

    Back to the TOC

    The /LARGEADDRESSAWARE (LAA) flag demystified

    When simplifying your application to prevent out-of-memory errors is not feasible or does no longer help, there is one more option. Applying the /LARGEADDRESSAWARE (LAA) flag to the MsAccess.exe can help to get rid of those errors.



    Back to the TOC

    Aggreated news on Access 2019 and Office 2019

    Access 2019 will be released later this year. Some information is publicly available already. I assembled the official anouncements about Office and Access 2019. That page will be updated with additional information when it becomes available.



    Back to the TOC

    Access and VBA Book Recommendations

    I just did not feel qualified to give book recommendations for Access and VBA programming because I haven't read many books on this topic. But, after thinking about this, I realized that I "rejected" quite a huge amounts books as "not good" over the last 15 years or so. So, the few I actually read, at least partially, should be good enough to be recommended.

    Based on that realization, I sat down today and wrote a list of Book Recommendation for Access and VBA programming.



    Back to the TOC

    Problems with DFirst/DLast and (wrongly) Sorted Result Sets

    Sometimes your data is not sorted the way you want it to be. Sometimes DFirst or DLast returns unexpected results. - Maybe you missed some fundamentals on how query results are sorted in Microsoft Access.



    Back to the TOC

    The Microsoft TreeView Control in 64bit Access

    There is a 64bit TreeView available for Access since 2017. - As there still is ongoing confusion on how to install and use the 64bit Edition of the Microsoft Common Controls in Access, I decided to investigate the issues and write down my findings.



    Back to the TOC

    Gallery PDF for Office imageMso icons

    The icons included with Microsoft Office are high quality icons you can use in your own projects. But how to find a suitable icon for a particular feature in your project?



    Back to the TOC

    Microsoft Access developer conferences around the world.

    While it is easy to participate in Access developer communities online, it is much more social and fun to meet the some of the worlds well known Access experts in person at a conference. So, I compile a list of regular Microsoft Access conferences with internation significance.



    Back to the TOC

    How to format Date/Time for a specific language and country

    In general I strongly advise to just use generic Access/VBA named formats for date formatting. However, when you need to format a date according to the rules of a specific target locale (language and country) this text will help you.



    Back to the TOC

    "Query is corrupt"-error after Microsoft Access November 2019 Update

    With the recent Microsoft Access security updates released on November 12, 2019 came a new bug. Update queries will fail with the error "Query is corrupt" (VBA runtime error 3340).



    Back to the TOC

    Resize an Access DataSheet Form to Fit all Columns

    Resizing an Access datasheet form to fit all its columns should have been straightforward. Nevertheless I encoutered and solved an unexpected problem.



    Back to the TOC

    Recap of Access news in 2020 and a Peek at 2021

    The Access Team at Microsoft was busy and delivered a couple of small, new features this year. I summarize the new features released for Access 365 in 2020. It includes an outlook to the features planned for 2021.



    Back to the TOC

    Query Performance Tuning - Basic Universal Rules

    Query performance tuning is sometimes difficult and complex. But there are a few simple and universal rules to improve query performance.. I compiled a list of those in this article.



    Back to the TOC

    Update Parent Form with Sub Form References

    I discovered a dirty little trick to make Access update content in a parent form when data in a sub form was changed. It requires nothing more than some creatively crafted expressions in the Controlssources of the controls in the parent form.



    Back to the TOC

    Warnings and Trust - Trust Center Settings in Microsoft Office

    You can adjust the Microsoft Office Trust Center settings with a single click and without knowing about their existence. This might be good enought for the average Office user, but a professional Access/VBA developer should know more details on security warnings and Trust Center Settings in Microsoft Access and Office in general.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.
    This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

    Benefits of the newsletter subscription



    © 1999 - 2024 by Philipp Stiefel - Privacy Policiy