Visual Basic Q&A

Symptoms
Although Visual Basic includes the SaveSetting and GetSetting functionsto save and retrieve information from the registry, these functions onlyoperate on a specific section of the registry, the Visual Basic and VBAProgram Settings of the HKEY_CURRENT_USER root key.
This article outlines the use of 32-bit Windows API functions, which can beused to set and retrieve values from anywhere in the registry. The topicsand function references in this article can be generalized to program the16-bit registry.
The 32-bit API functions also include support for security, although anoverview of security is outside the scope of this article.
NOTE: The SaveSetting and GetSetting functions are not part of the VBAfunction library. However, the sample code below still applies to 32-bitapplications that implement VBA.
Resolution
General Registry InformationThe registry is used by applications and Windows to store configurationdata. It is a replacement for the large numbers of INI files thatproliferated on Windows 3.x machines and is also used heavily by OLE.
The registry is organized using a hierarchical series of keys and valuesresembling a tree. Each key, beginning with one of the six predefined rootkeys, can have sub-keys and values associated with it. The keys areorganizational and naming units and appear in the Windows Registry Editorsas file folders. Values are data entries and appear as text entries in theright pane of the Registry Editor window. Keys need not have any associatedvalues, but may have many. Each value has an associated data type. The twomost commonly used registry data types are REG_SZ, a null-terminatedstring; and REG_DWORD, a 32-bit number.
The basic process used to write or read from a location in the registry isthe same. To reference any given key or value, you must have a handle tothe key. Once this handle is obtained, values and sub-keys of the key thatthis handle refers to can be read, set, or listed (enumerated).
Given a location in the registry, to obtain a handle to that key, you mustbegin with one of the six predefined keys (HKEY_CLASSES_ROOT,HKEY_CURRENT_USER, HKEY_LOCAL_MACHINE, HKEY_USERS, HKEY_CURRENT_CONFIG, andHKEY_DYN_DATA) and traverse the registry tree until the desired key isreached. User programs most often read and write from HKEY_CURRENT_USER andHKEY_LOCAL_MACHINE. If the keys being traversed exist already, you can usea series of calls to the RegOpenKey or RegOpenKeyEx functions. If the keysneed to be created, the RegCreateKey and RegCreateKeyEx functions do the job.
With the handle to the desired key, the functions used to list, set, andretrieve information can be called. In all cases, the functions with the Exsuffix will work only on 32-bit platforms. Functions without the suffix maywork on both 16-bit and 32-bit versions of Windows. Keep in mind that notall registry functions lacking the ‘Ex’ suffix are functions provided for16-bit compatibility. The Ex suffix was only added when the capabilities of16-bit functions were expanded. Functions that are totally new and specificto 32-bit platforms do not possess an Ex extension.
The RegSetValue and RegSetValueEx functions allow the settings of a valueto be modified, while RegQueryValue and RegQueryValueEx retrieve thecurrent setting of a value. The limitations of the non-Ex, 16-bit versionsof these APIs are very evident here. When using the 16-bit RegSetValuefunction there is no way to name a value, and because of this, RegSetValuecan’t be used to associate more than one value with each key. In addition,all values written with RegSetValue have a data type of REG_SZ. Theselimitations are inherent with the 16-bit Registry. RegSetValueEx allows thecreation of a multiple number of values with any available data type.
How to Write to a Specific Registry LocationAfter determining what functions you will need to use for your project,copy the relevant declares from the code at the end of this article to abasic module. The two Visual Basic procedures included (SetValueEx andQueryValueEx) are wrappers for the RegSetValueEx and RegQueryValueEx APIfunctions and greatly simplify their use. The notes below make use of theseVisual Basic functions; however, you are free to make calls directly to theAPI if you wish.
Creating/Modifying Keys and Values:
With the declarations and procedures available, you can create and openkeys, and add, modify, and read values. The three following sectionsexplain how to create a key, set or modify a value, and query a value.
Creating a New Key:
Creating a new key is as simple as using the following procedure.CreateNewKey takes the name of the key to create, and the constantrepresenting the predefined key to create the key under. The call toRegCreateKeyEx doesn’t take advantage of the security mechanisms allowed,but could be modified to do so. A discussion of Registry security isoutside the scope of this article.

Private Sub CreateNewKey (sNewKeyName As String, lPredefinedKey As Long)Dim hNewKey As Long’handle to the new keyDim lRetVal As Long’result of the RegCreateKeyEx functionlRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, _vbNullString, REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, _0&, hNewKey, lRetVal)RegCloseKey (hNewKey)End Sub
With this procedure a call of:

CreateNewKey “TestKey”, HKEY_LOCAL_MACHINE
will create a key called TestKey immediately under HKEY_LOCAL_MACHINE.
Calling CreateNewKey like this:

CreateNewKey “TestKey\SubKey1\SubKey2″, HKEY_LOCAL_MACHINE
will create three-nested keys beginning with TestKey immediately underHKEY_CURRENT_USER, SubKey1 subordinate to TestKey, and SubKey3 underSubKey2.
Setting/Modifying a Value:
Creating and setting a value of a specified key can be accomplished withthe following short procedure. SetKeyValue takes the key that the valuewill be associated with, the name of the value, the setting of the value,and the type of the value (the SetValueEx function only supports REG_SZ andREG_DWORD, but this can be modified if necessary). Specifying a new valuefor an existing sValueName will modify the current setting of that value.

Private Sub SetKeyValue (sKeyName As String, sValueName As String, _vValueSetting As Variant, lValueType As Long)Dim lRetVal As Long’result of the SetValueEx functionDim hKey As Long’handle of open key’open the specified keylRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, _KEY_SET_VALUE, hKey)lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)RegCloseKey (hKey)End Sub
A call of:

SetKeyValue “TestKey\SubKey1″, “StringValue”, “Hello”, REG_SZ
will create a value of type REG_SZ called “StringValue” with the setting of”Hello.” This value will be associated with the key SubKey1 of “TestKey.”
In this case, “TestKey” is a subkey of HKEY_CURRENT_USER, but this can bemodified by changing the call to RegOpenKeyEx. This call will fail if”TestKey\SubKey1″ does not exist. To avoid this problem, use a call toRegCreateKeyEx instead of a call to RegOpenKeyEx. RegCreateKeyEx will opena specified key if it already exists.
Querying a Value:
The next procedure can be used to ascertain the setting of an existingvalue. QueryValue takes the name of the key and the name of a valueassociated with that key and displays a message box with the correspondingvalue. It uses a call to the QueryValueEx wrapper function defined below,that only supports REG_SZ and REG_DWORD types.

Private Sub QueryValue (sKeyName As String, sValueName As String)Dim lRetVal As Long’result of the API functionsDim hKey As Long’handle of opened keyDim vValue As Variant’setting of queried valuelRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, _KEY_QUERY_VALUE, hKey)lRetVal = QueryValueEx(hKey, sValueName, vValue)MsgBox vValueRegCloseKey (hKey)End Sub
With this procedure, a call of:

QueryValue “TestKey\SubKey1″, “StringValue”
will display a message box with the current setting of the “StringValue”value, and assumes that “StringValue” exists in the “TestKey\SubKey1″ key.
If the Value that you query does not exist then QueryValue will return anerror code of 2 – ‘ERROR_BADKEY’.
Additional Notes:
The above examples use the extended 32-bit versions of the registryfunctions exclusively. These functions allow more than one value to beassociated with each key. As discussed above, the 16-bit RegSetValue andRegQueryValue act on a single value associated with the current key (whichis always of the type REG_SZ). These functions appear in the 32-bitRegistry Editor with a name of <NO NAME>. To set, modify, or query thisspecial associated value, one must use the 16-bit registry functions.Reading and writing from the registry in a 16-bit environment is muchsimpler than in a 32-bit environment. The same basic procedure is followed:open a key and get a handle and then call your modification function withthat handle, but no consideration needs to be made for multiple associatedvalues or for different value data types. A 16-bit application can createand modify keys and values with the declarations of the RegCreateKey,RegOpenKey, RegQueryValue, RegSetValue, and RegCloseKey functions.
In some cases, there is no need for any values to be associated with a key.An application may only need to know if a certain key or value exists, andnot care about the nature of the key’s values. In a situation like this,the RegEnumKey, RegEnumKeyEx, and RegEnumValue functions can be used todetermine whether a certain key or value exists. For more information onthese functions refer to the API Text Viewer and/or Windows API reference.
API Function and Constant Declarations

Option ExplicitPublic Const REG_SZ As Long = 1Public Const REG_DWORD As Long = 4Public Const HKEY_CLASSES_ROOT = &H80000000Public Const HKEY_CURRENT_USER = &H80000001Public Const HKEY_LOCAL_MACHINE = &H80000002Public Const HKEY_USERS = &H80000003Public Const ERROR_NONE = 0Public Const ERROR_BADDB = 1Public Const ERROR_BADKEY = 2Public Const ERROR_CANTOPEN = 3Public Const ERROR_CANTREAD = 4Public Const ERROR_CANTWRITE = 5Public Const ERROR_OUTOFMEMORY = 6Public Const ERROR_ARENA_TRASHED = 7Public Const ERROR_ACCESS_DENIED = 8Public Const ERROR_INVALID_PARAMETERS = 87Public Const ERROR_NO_MORE_ITEMS = 259Public Const KEY_QUERY_VALUE = &H1Public Const KEY_SET_VALUE = &H2Public Const KEY_ALL_ACCESS = &H3FPublic Const REG_OPTION_NON_VOLATILE = 0Declare Function RegCloseKey Lib “advapi32.dll” _(ByVal hKey As Long) As LongDeclare Function RegCreateKeyEx Lib “advapi32.dll” Alias _”RegCreateKeyExA” (ByVal hKey As Long, ByVal lpSubKey As String, _ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes _As Long, phkResult As Long, lpdwDisposition As Long) As LongDeclare Function RegOpenKeyEx Lib “advapi32.dll” Alias _”RegOpenKeyExA” (ByVal hKey As Long, ByVal lpSubKey As String, _ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _Long) As LongDeclare Function RegQueryValueExString Lib “advapi32.dll” Alias _”RegQueryValueExA” (ByVal hKey As Long, ByVal lpValueName As _String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _As String, lpcbData As Long) As LongDeclare Function RegQueryValueExLong Lib “advapi32.dll” Alias _”RegQueryValueExA” (ByVal hKey As Long, ByVal lpValueName As _String, ByVal lpReserved As Long, lpType As Long, lpData As _Long, lpcbData As Long) As LongDeclare Function RegQueryValueExNULL Lib “advapi32.dll” Alias _”RegQueryValueExA” (ByVal hKey As Long, ByVal lpValueName As _String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _As Long, lpcbData As Long) As LongDeclare Function RegSetValueExString Lib “advapi32.dll” Alias _”RegSetValueExA” (ByVal hKey As Long, ByVal lpValueName As String, _ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _String, ByVal cbData As Long) As LongDeclare Function RegSetValueExLong Lib “advapi32.dll” Alias _”RegSetValueExA” (ByVal hKey As Long, ByVal lpValueName As String, _ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _ByVal cbData As Long) As Long
SetValueEx and QueryValueEx Wrapper Functions:

Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _lType As Long, vValue As Variant) As LongDim lValue As LongDim sValue As StringSelect Case lTypeCase REG_SZsValue = vValue & Chr$(0)SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _lType, sValue, Len(sValue))Case REG_DWORDlValue = vValueSetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _lType, lValue, 4)End SelectEnd FunctionFunction QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _String, vValue As Variant) As LongDim cch As LongDim lrc As LongDim lType As LongDim lValue As LongDim sValue As StringOn Error GoTo QueryValueExError’ Determine the size and type of data to be readlrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)If lrc <> ERROR_NONE Then Error 5Select Case lType’ For stringsCase REG_SZ:sValue = String(cch, 0)lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _sValue, cch)If lrc = ERROR_NONE ThenvValue = Left$(sValue, cch-1)ElsevValue = EmptyEnd If’ For DWORDSCase REG_DWORD:lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _lValue, cch)If lrc = ERROR_NONE Then vValue = lValueCase Else’all other data types not supportedlrc = -1End SelectQueryValueExExit:QueryValueEx = lrcExit FunctionQueryValueExError:Resume QueryValueExExitEnd Function

Symptoms
The Registry is used by Windows 95, Windows 98, and Windows Me to determine whatapplication programs and hardware items are installed in the computersystem. This article explains how to retrieve the name of the defaultprinter from the Registry from within a Visual Basic application program.
Resolution
Manipulating the Registry in Visual BasicThe Windows 95/98/Me Registry is a database of information containingconfiguration details about the hardware and software installed in yourcomputer system. Under Windows 3.1, this information is maintained throughinitialization (INI) files.
The Registry is comprised of keys. Each key may contain a specific value orother subkeys that in turn may contain values or other subkeys. You canexamine or modify the contents of the registration database by using theWin32 Registry API functions in a Visual Basic program or by using theRegistry Editor (REGEDIT).
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

The demonstration program below shows how to use the Win32 Registry APIfunctions to retrieve the default printer’s name from the Registry.
The first step to retrieve the printer name is to call the RegOpenKeyExfunction. This function opens the specified key in the registrationdatabase. In our case, we want to open the key that is associated withthe printer. This key is stored in the Registry as:

SystemCurrent Control SetControlPrintPrintersDefault All of the above items are keys and subkeys. We are interested in thePrinters subkey.
We also need to tell the RegOpenKeyEx function that we want to work withthe Default subkey. After calling this function, a value is returnedthat is set to zero if the function was successful.The next step is to retrieve the actual value stored for the key we areinterrogating. Because we want to retrieve the name that is assigned tothe default printer, we want to call the RegQueryValueEx function. Wemust tell this function that we want to retrieve the value that wasgiven to the Default subkey.The last step is mandatory. You must call the RegCloseKey function torelease the handle of the key you have been accessing in theRegistration database. This terminates access to the registrationdatabase and frees the handle for future use by the computer system.
How to Create the Demonstration ProgramThe demonstration program below shows how to retrieve the name of thedefault printer from the Windows 95, Windows 98, or Windows Me Registry.
Create a new project in Visual Basic. Form1 is created by default.Add the following constant and Declare statements to the GeneralDeclarations section of Form1:

Private Declare Function RegOpenKeyEx Lib “advapi32″ Alias _”RegOpenKeyExA” (ByVal hKey As Long, ByVal lpSubKey As String, _ByVal dwReserved As Long, ByVal samDesired As Long, phkResult _As Long) As LongPrivate Declare Function RegQueryValueEx Lib “advapi32″ Alias _”RegQueryValueExA” (ByVal hKey As Long, ByVal lpValueName$, ByVal _lpdwReserved As Long, lpdwType As Long, lpData As Any, lpcbData As _Long) As LongPrivate Declare Function RegCloseKey Lib “advapi32″ (ByVal hKey As _Long) As LongConst HKEY_CURRENT_CONFIG As Long = &H80000005 Add a Text Box control to Form1.Add a Command Button control to Form1.Add the following code to the Click event for Command1:

Private Sub Command1_Click()Dim PName As StringPName = GetCurrPrinter()Text1.Text = PNameEnd Sub Create a new procedure called GetCurrPrinter. Add the following code to this procedure:

Function GetCurrPrinter() As StringGetCurrPrinter = RegGetString$(HKEY_CURRENT_CONFIG, _”System\CurrentControlSet\Control\Print\Printers”, “Default”)End Function Create a new procedure called RegGetString. Add the following code tothis procedure:

Function RegGetString$(hInKey As Long, ByVal subkey$, ByVal valname$)Dim RetVal$, hSubKey As Long, dwType As Long, SZ As LongDim R As LongRetVal$ = “”Const KEY_ALL_ACCESS As Long = &H9F003FConst ERROR_SUCCESS As Long = 0Const REG_SZ As Long = 1R = RegOpenKeyEx(hInKey, subkey$, 0, KEY_ALL_ACCESS, hSubKey)If R <> ERROR_SUCCESS Then GoTo Quit_NowSZ = 256: v$ = String$(SZ, 0)R = RegQueryValueEx(hSubKey, valname$, 0, dwType, ByVal v$, SZ)If R = ERROR_SUCCESS And dwType = REG_SZ ThenRetVal$ = Left$(v$, SZ)ElseRetVal$ = “–Not String–”End IfIf hInKey = 0 Then R = RegCloseKey(hSubKey)Quit_Now:RegGetString$ = RetVal$End Function Execute the demonstration program by pressing the F5 function key. When youclick the Command Button control, the name of your default printer isdisplayed in the Text Box control.

Symptoms
This article describes how to use the RegLoadKey registry function to load a user profile into the registry and, subsequently, how to use RegUnLoadKey to unload the user profile. Because RegLoadKey requires the SE_RESTORE_NAME privilege to be successful, this article also uses the OpenProcessToken, LookupPrivilegeValue, and AdjustTokenPrivileges functions.
Resolution
In part, the registry consists of files that store information about a user profile. When this file is loaded, it maps to the HKEY_USERS or HKEY_LOCAL_MACHINE key, whichever is specified in the call to RegLoadKey.
To retrieve user profile-specific information, you can load the NtUser.dat file that is located in the profile path of the user profile that you want to load. It may be necessary to load a hive (user profile) when you try to provide profile-specific data. For example, either the ImpersonateLoggedOnUser function or the CreateProcessAsUser function is generally used to run under a different security context and does not load the profile of that user.
The following steps illustrate how to load NtUser.dat and unload it when finished. These methods are not a threat to security because they only succeed if the calling process and the impersonated user have sufficient privileges.
Step-by-Step ExampleWARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Create a new Standard EXE project in Visual Basic. Form1 is created by default.Add a TextBox control (Text1) and two CommandButton controls (Command1 and Command2) to Form1.Paste the following code into the General Declarations section of Form1:

Option ExplicitPrivate Type LUIDLowPart As LongHighPart As LongEnd TypePrivate Type LUID_AND_ATTRIBUTESpLuid As LUIDAttributes As LongEnd TypePrivate Type TOKEN_PRIVILEGESPrivilegeCount As LongPrivileges(1) As LUID_AND_ATTRIBUTESEnd TypePrivate Const TOKEN_ADJUST_PRIVLEGES = &H20Private Const TOKEN_QUERY = &H8Private Const SE_PRIVILEGE_ENABLED = &H2Private Const HKEY_USERS = &H80000003Private Const SE_RESTORE_NAME = “SeRestorePrivilege”Private Const SE_BACKUP_NAME = “SeBackupPrivilege”Private Declare Function GetCurrentProcess Lib “kernel32″ () As LongPrivate Declare Function OpenProcessToken Lib “advapi32.dll” _(ByVal ProcessHandle As Long, ByVal DesiredAccess As Long, _TokenHandle As Long) As LongPrivate Declare Function LookupPrivilegeValue Lib “advapi32.dll” Alias _”LookupPrivilegeValueA” (ByVal lpSystemName As String, _ByVal lpName As String, lpLuid As LUID) As LongPrivate Declare Function AdjustTokenPrivileges Lib “advapi32.dll” _(ByVal TokenHandle As Long, ByVal DisableAllPrivileges As Long, _NewState As TOKEN_PRIVILEGES, ByVal BufferLength As Long, _ByVal PreviousState As Long, ByVal ReturnLength As Long) As LongPrivate Declare Function RegLoadKey Lib “advapi32.dll” Alias “RegLoadKeyA” _(ByVal hKey As Long, ByVal lpSubKey As String, ByVal lpFile As String) _As LongPrivate Declare Function RegUnLoadKey Lib “advapi32.dll” Alias “RegUnLoadKeyA” _(ByVal hKey As Long, ByVal lpSubKey As String) As LongPrivate Retval As LongPrivate strKeyName As StringPrivate MyToken As LongPrivate TP As TOKEN_PRIVILEGESPrivate RestoreLuid As LUIDPrivate BackupLuid As LUIDPrivate Sub Form_Load()strKeyName = “keyLoaded”‘ Path to file on Windows NT: C:\WinNT\Profiles\<Profile Name>\NtUser.Dat’ Path to file on Windows 2000: C:\Documents and Settings\<Profile Name>\NtUser.DatText1.Text = “<Path to File>”Command2.Enabled = FalseRetval = OpenProcessToken(GetCurrentProcess, TOKEN_ADJUST_PRIVLEGES _Or TOKEN_QUERY, MyToken)If Retval = 0 Then MsgBox “OpenProcess: ” & Err.LastDllErrorRetval = LookupPrivilegeValue(vbNullString, SE_RESTORE_NAME, _RestoreLuid)If Retval = 0 Then MsgBox “LookupPrivileges: ” & Err.LastDllErrorRetval = LookupPrivilegeValue(vbNullString, SE_BACKUP_NAME, BackupLuid)If Retval = 0 Then MsgBox “LookupPrivileges: ” & RetvalTP.PrivilegeCount = 2TP.Privileges(0).pLuid = RestoreLuidTP.Privileges(0).Attributes = SE_PRIVILEGE_ENABLEDTP.Privileges(1).pLuid = BackupLuidTP.Privileges(1).Attributes = SE_PRIVILEGE_ENABLEDRetval = AdjustTokenPrivileges(MyToken, vbFalse, TP, Len(TP), 0&, 0&)If Retval = 0 Then MsgBox “AdjustTokenPrivileges: ” & Err.LastDllErrorEnd SubPrivate Sub Command1_Click()Retval = RegLoadKey(HKEY_USERS, strKeyName, Text1.Text)If Retval <> 0 Then MsgBox “RegLoadKey: ” & RetvalCommand2.Enabled = TrueEnd SubPrivate Sub Command2_Click()Retval = RegUnLoadKey(HKEY_USERS, strKeyName)If Retval <> 0 Then MsgBox “RegUnloadKey: ” & RetvalEnd SubPrivate Sub Form_Unload(Cancel As Integer)Retval = AdjustTokenPrivileges(MyToken, vbTrue, TP, Len(TP), 0&, 0&)If Retval = 0 Then MsgBox “AdjustTokenPrivileges: ” & Err.LastDllErrorEnd Sub Save the project, and then press the F5 key to run it.Type the path to a specific user profile .dat file, for example:
C:\WinNT\Profiles\Administrator\NtUser.dat and then click Command1.Click Start, click Run, type regedit (on Windows NT) or regedt32 (on Windows 2000), and then click OK.Locate the HKEY_USERS subtree. Notice that this subtree includes the new key, KeyLoaded.In the Visual Basic project, click Command2 to remove this key from the registry.

Symptoms
Microsoft Access allows you to modify an existing field’s data type. To do so programmatically, Microsoft Jet 4.0 introduces the ALTER TABLE ALTER COLUMN DDL statement. However, there is no equivalent for Microsoft Jet 3.5.
This article demonstrates a method to alter a field’s data type using DAO objects.
Resolution
Modifying a field’s data type requires the following steps:Rename the old field.Add a new field.Copying the data from the old field to the new field.Delete the old field.If the table has any indexes or relations, the relationships and indexes must be dropped prior to performing the steps above, then re-established after completion of the steps above.
Microsoft Access handles indexes but not relationships when changing data types.
The Jet 4.0 ALTER TABLE ALTER COLUMN DDL statement has similar limitations.
The sample code provided handles both indexes and relationships.It also contains error handling to roll back the changes and report on any problems.
The main procedure is ChangeFieldType. It takes the following arguments:db – an open Database object where the table resides.TableName – the name of the table where the field resides.FieldName – the name of the field to be changed.NewType – the new data type for the field.NewAllowZeroLength – new value for the AllowZeroLength property.NewAllowNulls – used to set the Required property of the new field.NewAttributes – used to set the Attributes property of the new field.Note: This procedure is for illustration purposes only. For example, the procedure copies only basic field properties. In addition to these basic field properties, other field properties might also have to be copied. These additional field properties include ValidationRule, ValidationText, DecimalPlaces, and others, depending on the field type. In addition, the procedure does not copy user-defined properties.
The other procedures, RecordRelationInfo, RecordIndexInfo, IsField, and MakeArray, are helper procedures used by the main function.
Sample CodeThis sample changes the CustomerID field in the Customers table from a five character field to an eight character field.
The sample uses the Nwind database that comes with Visual Basic.
In Visual Basic, create a new Standard EXE project.
Form1 is created by default.Add a command button to Form1. Command1 is created by default.On the Project menu, select References.
In the References dialog, select the Microsoft DAO Object Library.On the Project menu, select Add Module to add a Code Module.
Module1 is created by default.Paste the following code into the General Declarations section of Module1’s Code Window:

Option Compare TextOption ExplicitConst CFT_Failed As Long = 55555Private Const R_NAME = 0, R_ATTRIBUTES = 1, R_TABLE = 2, R_FOREIGNTABLE = 3, R_FIELD = 4, R_FOREIGNFIELD = 5Private Const I_NAME = 0, I_PRIMARY = 1, I_UNIQUE = 2, I_REQUIRED = 3, I_IGNORENULLS = 4, I_CLUSTERED = 5, I_FIELD = 6, I_FIELDATTRIBUTES = 7Public Sub ChangeFieldType(db As Database, _ByVal TableName As String, _ByVal FieldName As String, _ByVal NewType As Integer, _Optional NewSize As Long, _Optional NewAllowZeroLength As Boolean = False, _Optional NewAllowNulls As Boolean = True, _Optional NewAttributes As Long)’ User-defined properties are not maintainedDim td As TableDef, I As Index, R As Relation, F As Field’ loop iterators for Indexes, Fields, and Relations collections:Dim I1 As Long, F1 As Long, R1 As LongDim colR As Collection, colI As CollectionDim E_Desc As String, Process As String, SubProcess As String, E As ErrorDim TempFieldName As String, Suffix As Long, OldName As StringDim Temp As VariantDim OrdinalPosition As LongSet colI = New CollectionSet colR = New CollectionOn Error GoTo CFT_ErrDBEngine(0).BeginTrans’ Enumerate relations and save/remove themDBEngine(0).BeginTransProcess = “Removing relations on [" & TableName & "]![" & FieldName & "]“SubProcess = “”For R1 = db.Relations.Count – 1 To 0 Step -1Set R = db.Relations(R1)If R.Table = TableName ThenFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)If F.Name = FieldName ThenRecordRelationInfo R, colRSubProcess = “Removing relation ” & R.Namedb.Relations.Delete R.NameExit ForEnd IfNext F1ElseIf R.ForeignTable = TableName ThenFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)If F.ForeignName = FieldName ThenRecordRelationInfo R, colRSubProcess = “Removing relation ” & R.Namedb.Relations.Delete R.NameExit ForEnd IfNext F1End IfNext R1Set F = NothingSet R = NothingDBEngine(0).CommitTrans’ Enumerate indices and save/remove themDBEngine(0).BeginTransProcess = “Removing indexes on [" & TableName & "]![" & FieldName & "]“SubProcess = “”db.TableDefs.RefreshSet td = db(TableName)td.Indexes.RefreshFor I1 = td.Indexes.Count – 1 To 0 Step -1Set I = td.Indexes(I1)If I.Foreign <> True ThenFor F1 = 0 To I.Fields.Count – 1Set F = I.Fields(F1)If F.Name = FieldName ThenRecordIndexInfo I, colISubProcess = “Removing index ” & I.Nametd.Indexes.Delete I.NameExit ForEnd IfNext F1End IfNext I1Set F = NothingSet I = NothingDBEngine(0).CommitTrans’ Rename FieldDBEngine(0).BeginTransProcess = “Renaming field”SubProcess = “”td.Fields.RefreshSet F = td(FieldName)OrdinalPosition = F.OrdinalPosition’ save this value’ determine a field name not in useSuffix = 0DoSuffix = Suffix + 1TempFieldName = “XXX” & SuffixLoop While IsField(td, TempFieldName)’ rename the fieldSubProcess = “to ” & TempFieldNameF.Name = TempFieldNameSet F = NothingDBEngine(0).CommitTrans’ Add new FieldDBEngine(0).BeginTransProcess = “Adding new field”SubProcess = “”td.Fields.RefreshSet F = td.CreateField(FieldName, NewType)If NewSize Then F.Size = NewSizeF.AllowZeroLength = NewAllowZeroLengthF.Required = Not NewAllowNullsF.Attributes = NewAttributesF.OrdinalPosition = OrdinalPositiontd.Fields.Append FSet F = NothingSet td = NothingDBEngine(0).CommitTrans’ Copy dataDBEngine(0).BeginTransProcess = “Copying data from ” & TempFieldName & ” to ” & FieldNameSubProcess = “”db.Execute “UPDATE [" & TableName & "] SET [" & FieldName & "]=[" & _TempFieldName & "]“, dbFailOnErrorDBEngine(0).CommitTrans’ Delete temporary fieldDBEngine(0).BeginTransProcess = “Deleting temporary field ” & TempFieldNameSubProcess = “”Set td = db(TableName)td.Fields.Delete TempFieldNameDBEngine(0).CommitTrans’ Add back IndicesDBEngine(0).BeginTransProcess = “Adding indexes back into table”SubProcess = “”Set td = db(TableName)td.Fields.Refreshtd.Indexes.RefreshOldName = “”Set I = NothingFor Each Temp In colIIf Temp(I_NAME) <> OldName ThenIf Not (I Is Nothing) Then’ handle first time through caseSubProcess = “Adding index ” & I.Nametd.Indexes.Append IEnd IfSet I = td.CreateIndex(Temp(I_NAME))I.Primary = Temp(I_PRIMARY)I.Unique = Temp(I_UNIQUE)I.Required = Temp(I_REQUIRED)I.IgnoreNulls = Temp(I_IGNORENULLS)I.Clustered = Temp(I_CLUSTERED)End IfSet F = I.CreateField(Temp(I_FIELD))F.Attributes = Temp(I_FIELDATTRIBUTES)’ to handle descending indexI.Fields.Append FNext TempIf Not (I Is Nothing) Then’ handle case of no indexesSubProcess = “Adding index ” & I.Nametd.Indexes.Append IEnd IfSet F = NothingSet I = NothingSet td = NothingDBEngine(0).CommitTrans’ Add back relationsDBEngine(0).BeginTransProcess = “Adding relations back into database”SubProcess = “”OldName = “”db.Relations.RefreshSet R = NothingFor Each Temp In colRIf Temp(I_NAME) <> OldName ThenIf Not (R Is Nothing) Then’ handle first time through caseSubProcess = “Adding relation ” & R.Namedb.Relations.Append REnd IfSet R = db.CreateRelation(Temp(R_NAME), Temp(R_TABLE), _Temp(R_FOREIGNTABLE), Temp(R_ATTRIBUTES))End IfSet F = R.CreateField(Temp(R_FIELD))F.ForeignName = Temp(R_FOREIGNFIELD)R.Fields.Append FNext TempIf Not (R Is Nothing) Then’ if there are no indexes…SubProcess = “Adding relation ” & R.Namedb.Relations.Append REnd IfSet F = NothingSet R = NothingDBEngine(0).CommitTrans’ Commit all pending chhangesDBEngine(0).CommitTransExit SubCFT_Abort:On Error Resume NextSet F = NothingSet td = NothingDBEngine(0).RollbackDBEngine(0).RollbackErr.ClearOn Error GoTo 0Err.Raise CFT_Failed, “ChangeFieldType”, E_DescExit SubCFT_Err:E_Desc = “Error ” & ProcessIf SubProcess <> “” Then E_Desc = E_Desc & vbCrLf & SubProcessIf DBEngine.Errors.Count = 0 ThenE_Desc = E_Desc & vbCrLf & “Error ” & Err.Number & ” ” & _Err.DescriptionElseFor Each E In DBEngine.ErrorsE_Desc = E_Desc & vbCrLf & “Error ” & E.Number & ” (” & _E.Source & “) ” & E.DescriptionNext EEnd IfDebug.Print E_DescResume CFT_AbortEnd SubPrivate Sub RecordRelationInfo(ByVal R As Relation, colR As Collection)’ Records information regarding the relationship and its fields’ in the colR collection.Dim F1 As Long, F As FieldFor F1 = 0 To R.Fields.Count – 1Set F = R.Fields(F1)colR.Add MakeArray(R.Name, R.Attributes, R.Table, R.ForeignTable, _F.Name, F.ForeignName)Next F1End SubPrivate Sub RecordIndexInfo(ByVal I As Index, colI As Collection)’ Records information about fields in the index and about the index itself’ into the colI collection.Dim F1 As Long, F As FieldFor F1 = 0 To I.Fields.Count – 1Set F = I.Fields(F1)colI.Add MakeArray(I.Name, I.Primary, I.Unique, I.Required, _I.IgnoreNulls, I.Clustered, F.Name, F.Attributes)Next F1End SubPrivate Function IsField(td As TableDef, ByVal FieldName As String) _As Boolean’ Returns TRUE if a field exists in the table with the same name as’specified in FieldName.’ Returns FALSE otherwise.Dim F As FieldErr.ClearOn Error Resume NextSet F = td(FieldName)IsField = Err.Number = 0Err.ClearEnd Function Private Function MakeArray(ParamArray X() As Variant) As Variant’ Does the same thing as the Array() function in VB6MakeArray = XEnd Function If necessary, change the CFT_Failed constant to use an error number that conforms to your company’s standards.Paste the following code into the General Declarations section of Form1’s Code Window:

Private Sub Command1_Click()Dim strDB As StringstrDB = “c:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb”Dim db As DAO.DatabaseSet db = DBEngine(0).OpenDatabase(strDB)ChangeFieldType db, “Customers”, “CustomerID”, dbText, 8db.CloseEnd Sub If necessary, modify strDB to use your Nwind database.Run the sample project.
Click the command button.
End the project.Examine the table in Microsoft Access or the Visual Basic Visual Database Manager add-in.
Note that the field has been resized.