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.

http://www.accessmvp.com/thedbguy/demos/listfiles.asp
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.

Cheers!



Thursday, December 7, 2017

A Recordset Bug

While trying to help a MSDN Forum user recently, I came across a potential bug when using a Recordset to loop through the records in a query. If you are interested in reading the discussion thread, you can find it using this link: https://social.msdn.microsoft.com/Forums/office/en-US/c9b98d99-083a-49fc-859a-43b9dcbd263f/combine-multiple-rows-from-a-query-into-1-row-string?forum=accessdev

The poster was trying to build a Text file from the table records for an external application. His requirement was to combine all the field values and all the records into one long text data. Naturally, when someone asks to combine all table records, I typically recommend this SimpleCSV() function. Unfortunately, the function did not work for him as it produced weird characters in the output string. See image below...

String result with some weird characters.

I have never run into this issue before, so I started doing some research. It turns out, when you use a Recordset with a calculated column where multiple fields are concatenated to combine the data, the calculated column only looks normal if the resulting value is less than 255 characters.

To demonstrate this issue, I created a demo file, which you can download from my website.

In the demo, I created a table with two Text fields, both containing exactly 255 characters. When we use a Recordset to pull just Field1, the result looks okay. But when we pull both Field1 and Field2 into one column in a Recordset, then we run into this issue. Interestingly enough, I also found out that using DLookup() does not produce a problem.

Demo

I brought this issue to Microsoft's attention, and it was confirmed by the Access Team. They also promised to work on a fix for future release. In the meantime, if you must combine multiple fields using a Recordset, I recommend pulling all the fields individually when you create the Recordset and then just loop through the fields and concatenate them from within your procedure.

I hope you find this article helpful. As always, any comment is welcome.

Thank you very much for reading.

Monday, September 25, 2017

Get PDF Form Field Names

Some of you may be familiar with my Fillable PDF Demos, which allow the user to fill a PDF form with data from an Access database. The demos use two different approaches for accomplishing this. One solution uses an XML approach, which works even if users only has a PDF reader installed on their machines. The other solution requires users to have a full version of Adobe Acrobat because it uses the Acrobat API to fill the form and also save it using a different name.

The hardest part of using these demos is determining the names of the fields to fill out in the PDF document, especially if the PDF form was created by someone else. To help with this, the demo page includes a link to a website where one can upload a fillable PDF file, and a list of all form fields used in the PDF file is presented in return.

Although having a website to help determine PDF form field names is convenient, some readers have asked me if there is a way to duplicate this functionality using VBA. As a result, I have updated the demo page and added a third demo file to demonstrate exactly how to get the names of PDF form fields using VBA. However, this solution uses Acrobat API and JavaScript object, so a full version of Adobe Acrobat is required to use this demo.

To try out this demo, click on the image below to reach my demo page and select the third download link on the left navigation bar.


I hope you find this demo useful and please let me know if you have any questions or run into any issues using it.

Cheers!

Sunday, August 27, 2017

Execute Excel Functions in Access

As a database developer, I sometimes help people convert their data from Excel into Access. Although both applications can store data in a table format, they are built for different purposes. Excel is a little bit easier to learn so a lot of people tend to use it first. Eventually, when the data gets bigger or more complicated for Excel, they find it is time to migrate their application into Access. Access is a lot better in handling relational data than Excel. However, Excel users sometimes find it frustrating when they realize some of the functions they typically use in Excel are not available in Access.

A good example of this is the NETWORKDAYS() function in Excel. This function returns the number of working days between two dates. This is a common requirement whether the data is in Excel or Access. Unfortunately, Access does not have an equivalent function for the same purpose. Instead, Access users are forced to create custom functions to do the same thing.

If you have not tried it before, creating a custom function in Access to mimic the functionality of the NETWORKDAYS() Excel function is not easy. There are a number of Access NETWORKDAYS() code examples available online. As it turns out, creating a duplicate Excel function in Access is not always necessary. We can execute some Excel functions through automation. The following code example shows how we can execute the Excel NETWOKDAYS() function from within Access.

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
'8/3/2017
'thedbguy@gmail.com
'Uses Excel's NetWorkdays() function

Dim xl As Object

Set xl = CreateObject("Excel.Application")

NetWorkdays = xl.WorksheetFunction.NETWORKDAYS(Format(StartDate, "yyyy-mm-dd"), _
    Format(EndDate, "yyyy-mm-dd"))

Set xl = Nothing

End Function

What the above code does is instantiate an Excel object and then use the WorksheetFunction method to execute the Excel NETWORKDAYS() function.

Users can use this technique to execute some built-in Excel functions if they can't find an equivalent Access function to do the same thing. I hope you find it useful.

Thursday, June 15, 2017

How to Retrieve the Hard Disk's Serial Number

I found myself looking for a way to retrieve the serial number of the computer's hard drive as a way of preventing unauthorized copies of an Access app. I have done it before but couldn't remember how. To my surprise, I found several ways to accomplish this task. I decided to post a couple of those techniques here to help anyone else who may find himself or herself looking to do the same thing in the future.

Before we start, the first thing we need to realize is hard disk drives have more than one serial numbers. One of the serial numbers we can retrieve is the one assigned by the hard disk manufacturer. This serial number should stay consistent throughout the life of the equipment. The other serial number available to us is the logical serial number assigned by the operating system when a disk is formatted. The value for the logical serial number may change if the disk is reformatted. Each technique presented below depends on which serial number you are interested in retrieving.

Physical Disk Drive Serial Numbers


The following function uses Windows Management Instrumentation (WMI) to create a connection to the local computer. The "WinMgmts" moniker is used to create a WMI object. Once a WMI object is instantiated, we can use the InstancesOf method to query the machine for system information.

Public Function HDSerial() As String
'6/14/2017
'thedbguy@gmail.com
'Returns the hard disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objPM As Object
Dim strSN As String

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_PhysicalMedia")

For Each objPM In objWin32
    strSN = strSN & (";" + objPM.SerialNumber)

Next

HDSerial = Trim(Mid(strSN, 2))

errExit:
    Set objPM = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

Logicl Disk Drive Serial Numbers


The above function used the "Win32_PhysicalMedia" collection to reference all the physical drives connected to the computer. We can now modify the above function using "Win32_LogicalDisk" to get a collection of all logical or mapped drives connected to the computer for the current user.

Public Function LDSerialWMI(Optional DriveLetter As Variant) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objWMI As Object
Dim objWin32 As Object
Dim objLD As Object
Dim strSN As Variant

Set objWMI = GetObject("WinMgmts:")
Set objWin32 = objWMI.InstancesOf("Win32_LogicalDisk")

If IsMissing(DriveLetter) Then
    For Each objLD In objWin32
        DriveLetter = objLD.DeviceID
        strSN = strSN & (";" + DriveLetter + objLD.VolumeSerialNumber)

    Next

Else
    For Each objLD In objWin32
        DriveLetter = Left(DriveLetter,1) & ":"
        If DriveLetter = objLD.DeviceID Then
            strSN = ";" & objLD.VolumeSerialNumber

        End If    
    Next

End If

LDSerialWMI = Trim(Mid(strSN, 2))

errExit:
    Set objLD = Nothing
    Set objWin32 = Nothing
    Set objWMI = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function

However, there is a more straightforward way to get the serial number of a specific logical disk drive. The following function uses the File System Object.

Public Function LDSerialFSO(DriveLetter As String) As Variant
'6/14/2017
'thedbguy@gmail.com
'Returns the logical disk drive serial number
'You are free to use this code in your applications
'provided this copyright notice is left unchanged

On Error GoTo errHandler

Dim objFSO As Object
Dim objDrv As Object
Dim strSN As Variant

DriveLetter = Left(DriveLetter,1) & ":"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDrv = objFSO.GetDrive(DriveLetter)

If objDrv.IsReady Then
    strSN = objDrv.SerialNumber
Else
    strSN = Null
End If
    
LDSerialFSO = strSN

errExit:
    Set objFSO = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function
There is an important difference between the two functions for logical disk drive presented above. Using WMI returns the serial number as HEX; whereas, using FSO returns the serial number as a Long Integer. However, you can use the Hex() function to either convert the return value from the LDSerialFSO() function to HEX or modify the LDSerialFSO() function to return the serial number as HEX.

I hope you find this post helpful. As usual, please feel free to submit your comments to let me know how I can improve these functions. Thank you!