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!

Sunday, March 19, 2017

A function to return the next work day

I know there are plenty of routines already available to calculate work days, but this topic is not really about adding work days to a date or counting work days between two dates. Rather, it is an extension of the topic for making sure the result from a date calculation falls on a work day.

You are probably aware we can simply use the Weekday() function to make sure a date does not fall on a weekend. For example, the following routine will check if a given date falls on a weekend (Saturday or Sunday); and if so, we simply return the date for the following Monday.

Select Case WeekDay(InputDate)
     Case 1 'Input Date falls on a Sunday
          InputDate = DateAdd("d", 1, InputDate)

     Case 7 'Input Date falls on a Saturday
          InputDate = DateAdd("d", 2, InputDate)

End Select

I say this article is an extension of the above because I was recently asked to incorporate a check for Holidays as a non-work day. Much like the routines available for calculating work days, we need to use a table listing all the Holidays. Once we have this table, we can use the following routine to check if a date falls on a Holiday and simply return the following day.

If DCount("*", "tblHolidays", "HolidayDate=#" & Format(InputDate, "yyyy-mm-dd") & "#") > 0 Then
     InputDate = DateAdd("d", 1, InputDate)
End If

The problem with simply combining the above two subroutines is which one should we perform first? Let us say we decided to check for a Holiday first and then check for a weekend. If the new date falls on a weekend, the final result will return the date for the following Monday, and we're done. But what if the following Monday happens to be a Holiday?

Conversely, if we check for a weekend first and then followed by a check for a Holiday, we would solve the above problem. But what happens if the input date was on a Friday and it was also Holiday? Checking for a weekend first will fail and then checking for a Holiday will result on a weekend date.

So, as you can see, it is somewhat of a Catch-22 situation.

If you have followed my posts on UtterAccess, you might be familiar with how I like to use recursive functions to solve problems like this one. So, the below function is what I ended up using to continually check if a date falls on a Holiday or a weekend and return the next work day.

Public Function GetNextWorkDay(InputDate As Date) As Date
'3/16/2017
'http://thedbguy.blogspot.com
'if input date is on a weekend or a holiday, returns the next work day

On Error GoTo errHandler

'check for holiday
If DCount("*", "tblHolidays", "HolidayDate=#" & Format(InputDate, "yyyy-mm-dd") & "#") > 0 Then
    InputDate = GetNextWorkDay(DateAdd("d", 1, InputDate))
    
End If

'check for weekend
Select Case Weekday(InputDate)
    Case 1 'Input date falls on a Sunday
        InputDate = GetNextWorkDay(DateAdd("d", 1, InputDate))
        
    Case 7 'Input date falls on a Saturday
        InputDate = GetNextWorkDay(DateAdd("d", 2, InputDate))
    
End Select

GetNextWorkDay = InputDate

errExit:
    Exit Function
    
errHandler:
    MsgBox Err.Number & ". " & Err.Description
    Resume errExit
    
End Function
Hope you find it useful. Please let me know if you have any recommendations for improvement. Thank you for reading.

Thursday, December 1, 2016

Import Access 97 MDB Data Using Access 2016

Some people may still be using an Access 97 MDB database and then realize they can no longer open their database when they upgrade to Access 2013 or 2016. According to this MS article, support for Access 97 MDBs was removed in Access 2013. The recommended method for upgrading an Access 97 MDB database to a ACCDB file is to use an earlier version of Access like Access 2007 or 2010. However, if this is not an option for you, i.e. you don't have a copy of Access 2007 or 2010 or do not know anyone with a copy of Access 2007 or 2010, then there may be another solution.

CAVEAT: As the title of this article implies, the following solution only applies to migrating the data from the MDB into a ACCDB.

I came across this solution while trying to help a member at UtterAccess. I was thinking one might be able to trick Access by using Excel to import the old data first before importing it again into Access. Unfortunately, I guess the process of doing so was not as simple as it may seem.

In looking around Microsoft Answers forum threads, I found a post by Kevin M. as quoted below (I hope he doesn't mind me reposting it here):

I have "laundered data" through excel 2013 as follows
open excel and select the "data" tab.
select "from Access" and then copy the path to the data sourse from under the "connection" tab.
Then select the "provider " tab and then select "Microsoft Jet 4.0 OLE db provider"
Then select "Next"  (dont select "OK") and paste the path into the database name field (or browse to it) Then select "OK"
This should get an Import Data dialog box and you can click ok to paste the data into excel
good luck

As verified by the UA member mentioned earlier, although this trick was originally offered for Access 2013 users, it still works with Access 2016.

If anyone has found other ways to accomplish the same thing, I would invite you to share it here by posting a comment.

Thank you!

Sunday, November 27, 2016

Form Data Validation

If you ever need to make sure the user entered valid information on the form before committing the data into the table, then it is considered "best practice" to perform data validation routines using the Form's BeforeUpdate event. In addition, it is common practice to use the Tag property of form controls to indicate which data needs to be validated.

For example, let us say you have a form for a loan application and a complete section must be filled out for a co-borrower information when the customer is applying for a joint loan. Using the Tag property approach, you can enter "co-borrower" in the Tag property of each Textbox representing co-borrower information and then use the following code in the Form's BeforeUpdate event:

If Me.LoanType = "joint" Then
   For Each ctl In Me.Controls
      If ctl.Tag = "co-borrower" Then
         If IsNull(ctl.Value) Then
            Cancel = True
            MsgBox "Please complete co-borrower information.", vbInformation, "Required"
            Exit For
         End If
      End If
   Next
End If

As already mentioned, this approach is common practice and usually applies to probably 99% of situations for data validations. However, someone approached me recently and said this approach of using the Tag property and looping through all the form controls made his form ran very slowly. It turns out his form was very busy with multiple subforms and a lot of unbound controls updated using code to reflect specific information about the current record from other tables in the database.

So, in this case, we want to avoid looping through all form controls, which is a requirement when using the Tag property approach. To do so, we'll need to know the names of the controls to evaluate, so we can simply validate them. One of the main advantages of using the Tag property is it is very easy to add or remove controls to validate without needing to modify the code. If we hard code the names of the controls to validate, we'll need to modify the code each time our requirements change.

As an alternative, we need to somehow store the names of the controls to validate and then go through each of them to check the form's data. There are several options to accomplish this task. For example, we can use a hidden unbound Textbox to store a comma-delimited value of control names to validate, We can then use the Split() function to store the control names in an array for validation.

The approach I will demonstrate will use a hidden unbound Listbox to store the control names. I like using a Listbox because it is very easy to loop through each item in a Listbox when performing our data validation. For this approach, I recommend using a Value List for a Row Source, although using a table will work just fine as well. Assuming the Bound Column of the Listbox is set to the column with the names of the control, here's a modified version of the above code using a Listbox rather than the Tag property:

If Me.LoanType = "joint" Then
   For lngItem = 0 To Me.ListboxName.ListCount - 1
      If IsNull(Me.Controls(Me.ListboxName.ItemData(lngItem))) Then
         Cancel = True
         MsgBox "Please complete co-borrower information.", vbInformation, "Required"
         Exit For
      End If
   Next
End If

As you can see, this alternative approach accomplishes the same task as using the Tag property without affecting the speed of a very busy form. Adding or removing controls to validate is also as simple as updating the Listbox without needing to alter the code. This approach can even be expanded by storing all control names in a table along with the name of the form or forms to which the control validation applies. Doing so will allow the same code to validate multiple forms.

Thursday, September 15, 2016

Collect Signatures in Access Demo

If you create database applications, you are probably happy with storing everything as electronic data. However, there may be occasions when you might need to collect hand-written information.


Some Access database developers like to use tablets or smart phones to make their database applications somewhat portable. And if their business requires a hand-written signature from a client, these devices are already equipped with an input device for collecting the signature.


This demo uses an ActiveX control to allow the user to sign a form. The signature information is then stored with their data and displayed on a form using two different techniques.




You may download the demo file at UtterAccess or My Website.