Cutting and pasting can be especially useful when you are first building your database, especially if you decide to restructure tables that
already have data in them. You can copy more than one cell of data at a time. I find the easiest way to select a few cells of data is to click
at the beginning of the first cell and then Shift-click at the end of the data I want to select. You can also select entire columns or rows by
clicking on the row or column header. And once you have selected the data, you can copy or cut it to the clipboard and copy it to a new
location--even a different table.
Using VBA and type libraries allows you to provide a tremendous amount of flexibility when creating Access 2010 applications.
Sometimes, however, you must communicate directly with the operating system of the computer and other components
(such as when you manage memory or process or work directly with the user interface elements such as windows or controls,
or when modifying the Windows Registry).
In these situations, your best option is to use external functions that are embedded in dynamic-link library (DLL) files.
You accomplish this in VBA by making API calls using Declare statements.
Making API calls is discussed in greater detail in Chapter 9 of this book.
The clipboard can be a useful tool for copying data from another application such as Excel or Word. In the next lesson you will learn about
importing data from another application. You may find that you do not need all the bells and whistles offered by the import feature--you can
get by simply copying the data you need to the clipboard and then pasting it into an Access table.
Microsoft provides a Win32API.txt file that contains approximately 1,500 Declare statements plus a tool for cutting and pasting the
Declare statement into your code.
Unfortunately, these statements are for 32-bit systems.
You will need to convert any of these statements to 64-bit versions before using them in your 64-bit application.
Declare statements are used to incorporate API calls into your code. They will take on one of two types:
A subroutine cannot provide a return value when called, but a function may provide a return value when called.
The following is an example of incorporating an API element of either type:
- subroutine or
[Public|Private] Declare [Function|Sub] Name Lib `` LibraryName Alias `` AliasName _(argument list)
Replace the placeholder (Name) with the actual name of the procedure from the DLL library and replace the placeholder LibraryName with the name of the DLL
file that contains the sub or function you wish to incorporate.
The Alias argument is optional and allows you to assign your own name to the sub or function for use later in your application.
It is useful to use an alias to prevent possible confusion in your code. The (argument list) must contain the parameters and data types
that are to be passed to the procedure.
Here is an example of incorporating an API function for opening and replacing a subkey in the
In Microsoft Visual C or Visual C++ the previous example will compile correctly for both 32-bit and 64-bit systems.
This is because HKey is defined as a pointer and its size directly reflects the platform it is compiled on.
Prior versions of VBA, however, do not have a pointer data type so the long data type was used.
Because the long data type is always 32-bit, it would break on systems using 64-bit memory space.
The upper 32 bits of memory space would either be truncated or would overwrite other memory addresses.
This behavior would cause unpredictable results or even system crashes.
Declare Function RegOpenKeyA Lib
advapi32.dll (ByVal KEY AS Long, ByVal SubKey _As String, NewKey As Long) As Long