Looking through an old database I had created I came across some code I had put together a while back to read and write INI files. If you’ve searched online a little, I’m sure you’ve found APIs that can be used to do this, but as much as I can, I try to minimize my use of ActiveX and APIs and this is one case where an API is not required to perform basic text search and writing.
When coding using VBA, as a developer, you have a choice of either using Early Binding or Late Binding. Below I will attempt to explain in plain English what each technique consists of and go over their Pros and Cons. Please keep in mind the advice provided below is all relative to Early and Late Binding, nothing more.
Here’s a quick one minute video demonstration on the main Microsoft Access database tools available for your reference. This video tutorial is based on Access 2010 but it applies to all versions (albeit maybe found in other parts of the application).
Over the years and over the various editions of MS Access I have found ControlTips to be useful, but unreliable. Under various conditions, they don’t seem to always display, or display promptly and then become frustrating. So my basic idea was to replace the built-in ControlTip with a dynamic pop-up form which could display my text.
I’ve been dealing with an ongoing issue with Access 2010 where calculated controls do not display values. When you start Microsoft Access 2010 on a computer that has not been restarted for a long time (for example, 24 days). … fields that are bound to expressions on forms may not be recalculated when the computer has not been restarted for a long time.
I needed to find a way to identify whether or not certain updates had been installed on the server or not and did not have access to the admin console/control panel/etc. Once again, I set out to create a function to ask the system and knew WMI could help! Below is the function I came up with...
When Access 2007 came out Microsoft introduced a new Attachment data type to allow users to insert attachments within their databases. I urge you to NEVER use Attachment data types for a variety of reasons, but mainly: Leads to database bloating Can impact performance You need code to interact and extract the attachments from the database...
Here’s a quick less than three minute video on what’s new with Microsoft Access 2016 to quickly discover if it’s actually worth the upgrade! As the video informs us, there seems to be little difference between the previous version (2013) and not convincing us it’s worth the effort or even an additional slight cost...
In a recent MVP discussion someone asked about a tool to convert a series of mdb databases contained within a directory into accdb file format without having to do so one by one manually. I had put together a primitive tool to do so for my own needs many moons ago and was more than happy to share it with them.
Whenever we see problems that common troubleshooting does seem to resolve, it is quite common to then perform a Repair of MS Office. From my understanding Repair checks various Office files dll/ocx/…, registry keys and the likes to ensure the integrity of all the programs. The entire process take a few minutes – on my PC it took approximately 10 minutes to Repair Microsoft Office Professional Plus 2013.
Probably the most common request is contact management database templates. How to structure the tables and build the various forms. So I thought I provide a basic demo database to help beginners get off to a good start.
In this post I’d like to discuss what steps you can take to try and secure, as best you can, your MS Access Front-End application. MS Access Front-End is not secure and leaves sensitive information completely open to hacking. As such, like your back-end (BE), you should always Encrypt with Password your front-end (FE). This will, in the very least, stop prying eyes from being able to harvest BE passwords allowing anyone to have complete reign on your raw data.
Time Format: hh:mm:ss, returns time values up to 23 hours 59 minutes and 59 seconds (23:59:59) and restarts with the value 00:00:01 for next day, 1 second after midnight 00:00:00. With the built-in Format() Function we cannot format time values beyond 24 hours, like 45 Hours 30 minute and 15 seconds (45:30:15).
As we all know, there are often numerous approaches to accomplish them same things. I was trying to help someone on a forum regarding the number of tables in their database returned by the TableDefs Collection vs. what the visually saw listed in the Nav Pane. This got me to perform a few tests and I discovered a little something which IMHO is a bug with the TableDefs Collection. At the very least it is something to simply be aware of.
I needed to search through the VBA code of a database looking for a given term, where the next line included a specific function. There is simply no way to do this with Mz-Tools, but I added one line to my code below and presto I had my listing to work off of. And let me tell you when you are dealing with complex databases with 100’s of forms modules, reports module, modules, class modules, a routine like the one below can make a drastic improvement!
How can I count the number of time a string/character is found within a string? Access/VBA has built-in function to handle this very easily.
I was asked in a forum how to print/save a word document as a PDF from within Access. Below is a simple procedure to do exactly that using Late Binding (so no reference libraries are required)...
The issue was a denormalized set of data that he wanted to cleanup, but not manually as there were far too many records to process. Thake a simple example, a table Now proper normalization dictates that we have an orders table to in which each order is entered instead of a single text field (OrderNumbers).
Once again this is a little procedure I created for my own purposes when trying to review databases so I can quickly identify certain types of forms. In this specific case it is used to list all the continuous forms.
I’ve recently been working on doing some serious code cleanup in a client database, and also wanted to try and do some optimization when possible. One thing I’ve been curious about was getting record count. The database that I’m working on, the previous developer would check the record count before opening any form or report, so it is checked constantly.