Friday, September 28, 2018

Copy Folder Structure

Hello there! First, I must apologize for the title of this article. I chose it because I think most Internet searches use a phrase similar to it, and I wanted to make sure people get to see and consider this information in their projects.

So, I am not really going to discuss the steps on how to duplicate a folder structure using VBA because there are already plenty of articles available on how to do it. Instead, I am just going to share a little bit of information I discovered yesterday as I was working on a utility for backing up my files from one drive to another.

In this utility, I needed a way to keep the folder structure the same on both the source and the target drives, so I can automatically compare the files between them and make sure I have the latest copy in the backup location. Listing the files and comparing them was easy enough to do. It was when I have a new source file to copy to the backup location where I ran into the issue of having to create a folder or subfolder on the other drive. (I might have a new source file located in a folder a few levels deep because I don't necessarily do a backup at a regular interval.)

We all know we can use the VBA MkDiR() function to create a folder or the CreateFolder method of the File System Object (FSO) to do the same. However, both of these methods suffer from the same issue; which is, they can only create a folder one level deep at a time. For example, using MkDir("C:\MyNewFolder") will create a folder called "MyNewFolder" under the root folder of the C: drive. Issuing the following command: MkDir("C:\MyOtherFolder\MySubFolder") will get a runtime error "76: Path not found" if C:\MyOtherFolder does not yet exist.

As a result, most articles on copying a folder structure use a recursive approach where the procedure walks the directory tree, one level at a time, creating each branch on its way to the end. So, in the above example, a recursive function might first check if C:\MyOtherFolder exists and creates it if necessary. Then, it will go down and check for the existence of the MySubFolder folder and then create it as needed. Both the VBA and FSO methods can be used in this recursive approach. If you want to see an example of a recursive approach for copying a folder structure, please let me know in the comments below, and I will post some links to a few articles showing how to do it (or I might post an example code).

So, back to the topic I wanted to share... Although there is nothing wrong with using a recursive approach to create a folder structure (I like using recursion, myself, on some of my projects), I thought it would have been nice to create the entire tree (actually, just one branch, but all the way to the leaf) in one instance (as in, one command or one line of code). This is exactly what I discovered yesterday and wanted to share with all of you.

As it turns out, the DOS command MKDIR or MD can do exactly what I was looking for. So, if you open up a DOS prompt and enter the command MD C:\Folder1\Folder2\Folder3, DOS will create this folder structure, if it doesn't already exist. I thought, how cool was that? So, how can I use this in VBA? I'm glad you asked. Here's the one line command I ended up using in my backup utility.

ws.Run "cmd /c mkdir """ & strTargetFolder & """"

where ws is a Shell object I created at the beginning of my procedure as follows:

Set ws = CreateObject("WScript.Shell")

and strTargetFolder is the folder structure for the new file. For example, "C:\MyFiles\ProjectName\Phase1\StatusUpdates\"

So, although recursion is a perfectly valid approach for copying a folder structure, I was just happy to find out another way to do the same without recursion and wanted to share it with everyone, in case some of you might also find it interesting and decide to use this approach in your projects.

Of course, we can discuss the pros and cons of each approach, but that's what the comments section is for. So, please let me know your thoughts, and I will be happy to discuss them below.

As always, thank you for reading, and I hope some of you will find this article useful.


Sunday, August 26, 2018

RegEx vs Character Loop

As is often the case with database management, we invariably have to clean up the data at some point. This demo was inspired by a thread discussion at UtterAccess where the original poster was looking for a way to remove unwanted characters from table records. (Here's the link to the UA thread, for additional information on the requirement.)

So, the requirement is easy enough, and as we all know in Access, there's always more than one way to reach a solution for any particular problem. That was also the case in this situation.

The two approaches I am comparing in this demo for removing unwanted characters from a string field in a table are (a) using regular expressions and (b) looping through the characters of the string. Both approaches will perform the job as required, but I was just curious if one particular approach would be faster than the other.
(click on the above image to download the demo file)

There may be other ways to test this theory other than the way I did it in this demo. For example, we could probably test the amount of time it would take to create a new table based on the result of cleaning up the data. As it stands right now, I just simply open and close a query containing a function call to each approach.

I encourage you to download the test file (it's only 526 KB) and try it out for yourself. Different configurations should give us different results. Please share your results if you do decide to give it a try and give us your conclusions as well.

I hope someone could find this little experiment somewhat useful in your projects or learning experience.

Thank you for reading...

I wasn't sure if simply opening and closing the queries calling each function is enough to test for speed, so I added a couple more processes within each approach. You can download v1.1 using this link, if you want to see if it makes any difference in your outcomes. Cheers!

Monday, June 18, 2018

Retrieve Database (Extended) Properties

If you've ever wondered how to programmatically retrieve the information from the Summary tab when you click on "View and edit database properties" from the Info tab in Backstage View, then continue reading.

An example of the Summary tab information is shown in the image below.

If you try to loop through the CurrentDb.Properties collection, these particular properties are not present. For this reason, I consider them to be as "extended" database properties. To retrieve these extended properties, we can use a DAO object.

Here's an example code for specifically retrieving the extended properties found in the Summary tab.

Public Function GetSummaryInfo() As String

Dim db As DAO.Database
Dim dbContainer As DAO.Container
Dim dbDocument As DAO.Document
Dim dbProperty As DAO.Property

Set db = CurrentDb()
Set dbContainer = db.Containers("Databases")
Set dbDocument = dbContainer("SummaryInfo")

For Each dbProperty In dbDocument.Properties
    Debug.Print dbProperty.Name & ": " & dbProperty.Value

Set dbDocument = Nothing
Set dbContainer = Nothing
Set db = Nothing

End Function

The above technique can also be applied to retrieve some of the extended properties found in the other tabs (like Contents, for example). To see a demo file using this technique to examine the database properties for any Access database file, click the image below.
click on the above image to download the demo

I hope you find this article useful. Please let me know if you find any bugs in the demo.

Wednesday, May 23, 2018

Backstage View

A while back, I posted a demo showing how to hide the Backstage View in Access 2010 (click here to see the original article). This particular technique allows developers to somewhat restrict users from making unwanted modifications to their database application.

Unbeknownst to me, Microsoft made some changes to the Ribbon schema in Access 2013 and 2016. Although the reference to the schema is the same for all versions since Access 2010, which is

<customUI xmlns="">

it seems there were some changes made to the schema document for newer versions of Access. Fellow MVPs Daniel Pineault and Tom Wickerath brought this issue to my attention and provided a simple technique for identifying errors in the Ribbon XML at runtime. If you go to the Options settings, you can select Client Settings and put a check mark in the box for "Show add-in user interface errors" under the General heading (see image below).

When I did this, I started seeing the problems in my Ribbon XML (see image below for an example).

Just as a reminder, here's the Ribbon XML I used in Access 2010.

  <tab idMso ="TabInfo" visible="false"/>  
  <tab idMso ="TabRecent" visible="false"/>
  <tab idMso ="TabNew" visible="false"/>
  <tab idMso ="TabPrint" visible="false"/>
  <tab idMso ="TabShare" visible="false"/>
  <tab idMso ="TabHelp" visible="false"/>
  <button idMso="FileSave" visible="false"/>
  <button idMso="SaveObjectAs" visible="false"/>
  <button idMso="FileSaveAsCurrentFileFormat" visible="false"/>
  <button idMso="FileOpen" visible="false"/>
  <button idMso="FileCloseDatabase" visible="false"/>
  <button idMso="ApplicationOptionsDialog" visible="false"/>
  <button idMso="FileExit" visible="true"/>

As a result of this revelation, I have decided to update my demo and also include a separate version of the demo for use with Access 2013 and 2016. The new Ribbon XML I ended up using for Access 2013 and 2016 looks like this:

  <tab idMso ="TabInfo" visible="false"/> 
  <tab idMso="TabOfficeStart" visible="false"/>
  <tab idMso ="TabRecent" visible="false"/>
  <tab idMso ="TabSave" visible="false"/>
  <tab idMso ="TabPrint" visible="false"/>
  <tab idMso ="TabHelp" visible="false"/>
  <button idMso="FileSave" visible="false"/>
  <button idMso="ApplicationOptionsDialog" visible="false"/>
  <button idMso="FileCloseDatabase" visible="false"/>
  <button id="exit" label="Exit" onAction="=RibbonAction('exit')"/>

As you can see from the above updated XML, I had to remove some of the old controls because they caused errors in 2013 and 2016. Hopefully, this article can help you update your Backstage Ribbon XML for use with Access 2013 and 2016. You can download the updated demos from my website.

Please note failing to update the Backstage Ribbon XML will not render your customized Access 2010 database application useless when opened using Access 2013 or 2016. All the incompatible controls will just be ignored by Access when rendering the Backstage View. Furthermore, if the user does not even have the Client Settings set to show user interface errors, then no warnings will show up at all to indicate there's any problem.

Monday, March 26, 2018

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files in a folder or directory, I thought this was challenging enough for me to give it a try.

The original poster's request was to allow the user to search the folder for files matching a keyword and a list of specific file extensions. Also, the search should include all files inside subfolders.

Many of the examples I have seen use a loop to go through the folder structure and list all the files in them. Since I am a bit fascinated with recursion, I thought I would give it a try using a different approach. As a result of my attempt, I have created this demo.
Click on the above image to download the demo.
To use this demo, the user selects a starting folder path and enter an optional search keyword or list of file extensions to search. The search keyword is used to match the file names, while the list of file extensions will return only the files with matching file extensions.

The demo presents the result of the search using a Listbox. Double-clicking on an item in the Listbox will attempt to open the file for viewing. This version of the demo (v1.0) currently uses a Value List in the Row Source of the Listbox, which limits the number of files allowed to be displayed. The demo can be modified to store the file names in a table to allow the Listbox to display more items. I might do this in future versions of the demo.

In the meantime, I hope you find this demo useful, or at least, find the approach a little interesting.

As usual, I would appreciate any comments or feedback regarding this demo.