The Win32 API

Introduction

Besides the libraries used in Microsoft Access, the Microsoft Windows operating system provides its own library of functions and objects. This library is called the Win32 Application Programming Interface or Win32 API, or simply Win32. The Win32 library is somehow available to applications but its functions are not directly available for a database.

The Win32 library is made of procedures, functions, and classes (mostly structures) that you can use to complement a project. There are so many of these functions and objects that it is hard to know most or all of them. The best way to get acquainted with them is to check its documentation. To do this, you can visit the MSDN web site. The functions are stored in various sub-libraries called dynamic link libraries (DLLs).

Before using a Win32 function in your code, you must first have two pieces of information: the DLL in which the function was created and the actual name of the desired function in that library. Examples of DLLs are shfolder or Kernel32. Once you know the name of the library and the name of the function you want to use, you must import it in your Visual Basic code. The basic formula to follow is:

Private Declare Function Win32FunctionName Lib "LibraryName"
	Alias "CustomName" (Arguments) As DataType

The Win32FunctionName factor is the name of the function in the Win32 library. The LibraryName is the name of the library. You can create a custom name for the function as the CustomName factor. In the parentheses, you can enter the names and types of the arguments. If the procedure returns a value, you can specify its type after the As keyword.

1. Open the Department of Records and Statistics database you created in the first lesson 
2. In the Database window, click the Forms button and double-click DRS
3. Switch the form to Design View
4. Right-click the Create Database button and click Build Event...
5. In the Choose Builder dialog box, click Code Builder and click OK
6. Change the file as follows:
    

   Option Compare Database Option Explicit Private Const MAX_PATH = 260 Private Const CSIDL_PERSONAL = &H5& Private Const SHGFP_TYPE_CURRENT = 0 ' We will use the Windows API to get the path to My Documents Private Declare Function SHGetFolderPath Lib "shfolder" _ Alias "SHGetFolderPathA" _ (ByVal hwndOwner As Long, ByVal nFolder As Long, _ ByVal hToken As Long, ByVal dwFlags As Long, _ ByVal pszPath As String) As Long Private Sub cmdCreateDatabase_Click() Dim strMyDocuments As String Dim strDbName As String Dim valReturned As Long Dim dbMVD As DAO.Database ' Initialize the string strMyDocuments = String(MAX_PATH, 0) ' Call the Shell API function to get the path to My Documents ' and store it in the strMyDocuments folder valReturned = SHGetFolderPath(0, CSIDL_PERSONAL, _ 0, SHGFP_TYPE_CURRENT, strMyDocuments) ' "Trim" the string strMyDocuments = Left(strMyDocuments, InStr(1, strMyDocuments, Chr(0)) - 1) ' Include the name of the database in the path strDbName = strMyDocuments & "\Motor Vehicle Division.mdb" ' Create the database Set dbMVD = CreateDatabase(strDbName, dbLangGeneral) End Sub

7. Return to Microsoft Access and switch the form to Form View
8. Click the Create Database button and switch the form to Design View
9. Open a My Documents window and notice that a database named Motor Vehicle Division has been created

Introduction to String-Based Functions

Introduction

The string is the most regularly used values of an application. We have seen in the past that a string variable could be declared using the String data type. After declaring a String variable, it is initialized as empty. You can also initialize it with any string of your choice. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
End Sub

This would produce:

The Length of a String

In many operations, you will want to know the number of characters a string consists of. To get the size of a string, you can call the Len() function. Its syntax is:

Len(strValue As String) As Integer

The Len() function takes one argument, which is the string you are considering. The function returns the number of characters of the string. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    Dim iLen As Integer
    
    strValue = "République d'Afrique du Sud"
    iLen = Len(strValue)
    MsgBox "The length of """ & strValue & """ Is " & _
            CStr(iLen) & " characters."
End Sub

This would produce:

The String() Function

One way you can initialize a string is to fill it up with a certain character of your choice. To do this, you can call the String() function. Its syntax is:

String(number, character) As String

The second argument to this function is a character you want to repeat in the string. The first argument to this function is the number of occurrences you want the second argument to be repeated in the string. The String() function returns a String value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = String(18, "#")
    MsgBox strValue
End Sub

This would produce:

The String Object

Strings and Empty Spaces

The simplest string is probably one that you declared and initialized. In some other cases, you may work with a string that you must first examine. For example, for some reason, a string may contain an empty space to its left or to its right. If you simply start perform a certain operation on it, the operation may fail. One of the first actions you can take on a string would consist of deleting the empty space(s), if any on its sides.

To remove all empty spaces from the left side of a string, you can call the LTrim() function. Its syntax is:

LTrim(string) As String

To remove all empty spaces from the right side of a string, you can call the RTrim() function. Its syntax is:

RTrim(string) As String

To remove the empty spaces from both sides of a string, you can call the Trim() function. Its syntax is:

Trim(string) As String

On the other hand, if you want to include or add empty spaces in a string, you can call the Space() function. Its syntax is:

Space(number) As String

Because all these functions return a string, they can be written as LTrim$, RTrim$, Trim$, and Space$.

Strings Comparisons

To compare two strings, you can call the StrCmp() function. Its syntax is:

StrComp(string1, string2, compare)

The first and the second arguments to this function are strings and both are required. After the function has performed the comparison, it returns

• -1 if string1 is less than string2
• 0 if string1 and string2 are equal
• 1 if string1 is greater than string2

The third argument is allows you to specify the comparison in binary or text format. This argument can have one of the following values:

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue1 As String
    Dim strValue2 As String
    Dim iComparisonValue
    
    strValue1 = "République d'Afrique du Sud"
    strValue2 = "Republic of South Africa"
    iComparisonValue = StrComp(strValue1, strValue2, vbTextCompare)
    
    MsgBox "Comparing """ & strValue1 & """ with """ & _
           strValue2 & """ produces " & CStr(iComparisonValue)
End Sub

This would produce:

Reversing a String

Once a string has been initialized, one of the operations you can perform on it consists of reversing it. To do this, you can call the StrReverse() function. Its syntax is:

StrReverse(string) As String

This function takes as argument the string that needs to be reversed. After performing its operation, the function returns a new string made of characters in reverse order.  Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strRev As String
    
    strValue = "République d'Afrique du Sud"
    strRev = StrReverse(strValue)
    
    MsgBox strValue
    MsgBox strRev
End Sub

This would produce:

Because the StrReverse() function returns a string, you can write it as StrReverse$. The $ symbol indicates that the function returns a string.

The Character Cases of a String

Introduction

Each alphabetic character in the English language has two representations: lowercase and uppercase. Characters in lowercase are: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, and z. Their equivalent characters in uppercase are represented as A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, and Z. Characters used for counting are called numeric characters; each one of them is called a digit. They are 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. There are other characters used to represent things in computer applications, mathematics, and others. Some of these characters, also called symbols are ~ , ! @ # $ % ^ & * ( ) _ + { } ` | = [ ] \ : " ; ' < > ? , . / These characters are used for various reasons and under different circumstances. For example, some of them are used as operators in Mathematics or in computer programming. Regardless of whether a character is easily identifiable or not, all these symbols are character types.

Converting a String to Uppercase

To convert a lowercase character to uppercase, you can use the UCase() function. Its syntax is:

UCase(strValue As String) As String

This function simply takes one argument, the string that needs to be converted. Each letter of the string that is already in uppercase would remain in uppercase. Each letter of the string that is in lowercase would be converted to uppercase. Each non-letter character of the argument would not be converted. This function returns a string value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
    strValue = UCase(strValue)
    MsgBox strValue
End Sub

This would produce:

Because the UCase() function returns a string, you can also write it as UCase$.

Converting a String to Lowercase

To convert a string to lowercase, you can call the LCase() function. Its syntax is:

LCase(strValue As String) As String

This takes one argument as the string to convert. Any letter in lowercase in the argument would remain in lowercase. Any letter in uppercase would be converted to lowercase. The non-letter characters of the argument would be kept “as-is”.

Since the LCase() function produces a string, you can also write it as LCase$.

Conversion to Appropriate Case

The LCase$() and the UCase$() functions are used to process the whole string. An alternative to these two functions consists of calling the StrConv() function. Its syntax is:

StrConv(string, conversion As vbStrConv, LocalID As Long) As String

This function takes two required arguments. The first argument is the string that will be considered for conversion. The second argument allows you to specify how the conversion would be performed. This argument can have one of the following values:

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strConversion As String
    
    strValue = "republic of south africa"
    strConversion = StrConv(strValue, vbProperCase)
    
    MsgBox strValue
    MsgBox strConversion
End Sub

This would produce:

Sub-Strings

Introduction

Finding a Character or a Sub-String in a String

If you have a string and want to find a character or a sub-string in it, you can call the InStr() function. Its syntax is:

InStr([start, ]string1, string2[, compare])

The first argument specifies the position from the string where to start looking for. This first argument is not required. The second argument is required and specifies the string to examine. The third argument specifies the character or the string to look for in the second argument. The fourth argument, which is optional, specifies whether the criterion would be binary or text-based.

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim iPos As Integer
    
    strValue = "Republic of South Africa"
    iPos = InStr(1, strValue, "South")
    
    MsgBox "In """ & strValue & """, " & " South can be found at " & CStr(iPos)
End Sub

This would produce:

The InStr() function works from the left side of the considered string. If you want to find an occurrence of one or more characters from the right side side of a string, you can use the InStrRev() function instead.

Replacing Occurrences in a String

After finding a character or a sub-string in an existing string, one of the operations you can perform would consist of replacing that character or that sub-string with another character or a sub-string. To do this, you can call the Replace() function. Its syntax is:

Replace(expression, find, replace[, start[, count[, compare]]])

The first argument to this function, expression, is required. It holds the string that will be considered.

The second argument, find, also required, is the character or the sub-string to look for in the expression.

The third argument also is required. It also is passed as a string. If the find string is found in expression, it would be replaced with the replace string.

When you call the Replace() function with the three required arguments, it would proceed from the most left character of the expression string. Instead of start with the first character, you can specify another starting position of your choice within expresion. The fourth argument, which is optional, allows you to pass this factor as a constant integer.

The fifth argument also is optional. If you omit it, every time find would be found in expression, it would be replaced with replace, as many times as possible. If you want, you can limit the number of times this replacement should occur even if find is found more than once. To specify this factor, pass the fifth argument as a constant integer.

The compare argument, also optional, allows you specify whether the comparison would be carried in text or binary format. This argument can be passed as Text or Binary.

Practical Learning: Replacing Occurrences in a String