Language: Deutsch English















Last Update: 2017 - 07 - 09





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
  • 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
  • How to use transactions in Microsoft Access VBA
  • 7 reasons to use source code control for Microsoft Access development
  • DCount vs. SELECT COUNT performance
  • Sort and index binary data
  • Logical numerals sorting in Access with VBA
  • Let external users review and edit data
  • Databases in OneDrive, DropBox or Offline-Folders
  • How to create a calculated field in a Microsoft Access table
  • How to calculate in a query
  • Issues with the Hyperlink data type in Microsoft Access
  • How to check if an Access Form is open in dialog mode



  • 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

    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

    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

    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

    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

    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

    How to calculate in a 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 columns of 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


    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

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.



    © 1999 - 2017 by Philipp Stiefel