Last Update: 2024 - 05 - 21 |
All columns in your databaseThis 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 VBASometimes 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 NumberIf 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. 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 tableSometimes 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 VBATransactions 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 stringHere 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 AccessThis 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 sourcesInspired 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 beginnersThe 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 tiesIf 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 developmentIn 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 performanceAn 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 VBAA 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 dataDo 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 VBADid 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 dataOccasinally 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 solutionsIf 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-FoldersDropBox, 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 tableIn 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 QueryOther 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 AccessThe 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.
Digitally signing VBA Code in .accdb databasesSigning 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 VBAOpening 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 AccessWhile 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 AccessThey 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 developersThe 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 2201Microsoft 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 modeAn 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 demystifiedWhen 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 2019Access 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 RecommendationsI 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 SetsSometimes 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 AccessThere 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 iconsThe 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 countryIn 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 UpdateWith 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 ColumnsResizing 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 2021The 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 RulesQuery 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 ReferencesI 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 OfficeYou 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 Generating PDF/A Compliant PDF Files with Microsoft AccessNowhere mentioned officially, but still simple to implement if you know how. - I exposed some hidden and undocumented features and show you how to export a PDF/A compliant PDF files from Microsoft Access with VBA. Back to the TOC
I will never share your email with anyone. You can unsubscribe any time. © 1999 - 2024 by Philipp Stiefel - Privacy Policiy |