Visual Basic Q&A

Symptoms
When you try to insert an empty string into Memo, Text, nText or Blob columns by using the OLE DB .NET data provider, you receive the following exception:

An unhandled exception of type ‘System.InvalidOperationException’ occurred in system.data.dll If you handle this exception within a try-catch block, you receive the following information:

System.InvalidOperationException
System.Data.OleDb.OleDbException: Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Resolution
To work around this problem: For strings, insert a space that has a length greater than zero (such as ” “), instead of using an empty string.If the database has an Allow Nulls setting, select Allow Nulls for the columns in the database, and treat nulls as empty strings.

Symptoms
This article demonstrates how to programmatically use Data Links feature of the Microsoft Data Access Components in order to generate a connection string at run-time.
Resolution
In version 2.0 of the Microsoft Data Access Components, Data Links were introduced. Data Link files are similar to ODBC DSN files, but allow you to select an OLE DB provider to connect to your database. With the OLE DB Provider for ODBC drivers, you can also connect to an ODBC data source.
Double-clicking on a Data Link file displays a set of property pages that allow you to build a connection string to connect to your database.
You can use this same functionality in your Visual Basic applications by following the steps listed below: Launch Visual Basic and open a new Standard Exe project. Form1 is created by default.Select References from the Project menu, and then select Microsoft OLE DB Service Component 1.0 Type Library from the list of available references.Add a CommandButton to your form.Add the following code to the Click event of your CommandButton:

Private Sub Command1_Click()Dim objDataLink As New DataLinksDim strConn As StringstrConn = objDataLink.PromptNewMsgBox “The connection string you created is:” & _vbCrLf & strConnEnd Sub Run the project. When you click the CommandButton, you will see the Data Links property pages. Once you have specified how you want to connect to your database and click the OK button, you’ll see the connection string in a dialog box.

Symptoms
You may use ADOMD with the MSOLAP provider to return an Out of Process Cellset. This is useful with DCOM/MTS business objects. This code sample requires the MSOLAP OLEDB provider on the client computer and the Food Mart OLAP database on SQL Server OLAP Services computer. The MSOLAP OLEDB provider is installed when you install OLAP client components from SQL Server 7.0 CD.
Resolution
ServerSteps to AccomplishCreate a new Visual Basic ActiveX EXE Project. Class 1 is created by default.Set a Project Reference to the Microsoft ActiveX Data Objects (Multi-Dimensional) 1.0 Object Library.Change the name of the Project to ADOBusObj.Paste the following code into Class1:

Private strSQL As StringPrivate strConnect As StringDim adoCat As New ADOMD.CatalogPublic Function GetRs() As ADOMD.CellSetDim adoCst As New ADOMD.CellSetWith adoCstSet adoCst.ActiveConnection = adoCat.ActiveConnection.Source = strSQL.OpenEnd WithSet GetRs = adoCstEnd FunctionPrivate Property Get ConnectStr() As StringConnectStr = strConnectEnd PropertyPrivate Property Let ConnectStr(strCn As String)strConnect = strCnEnd PropertyPublic Property Get SQL() As StringSQL = strSQLEnd PropertyPublic Property Let SQL(nSQL As String)strSQL = nSQLEnd PropertyPublic Sub ADOMDConnect(strConnect As String, Optional CmdTimeOut As Integer = 20)adoCat.ActiveConnection = strConnectConnectStr = adoCnEnd Sub
ClientCreate a new Visual Basic Standard EXE Project. Form1 is created by default.Set a Project Reference to the Microsoft ActiveX Data Objects (version 2.0 or later) Library.Set a Project Reference to the ActiveX EXE ADOBusObj created earlier. Change the connection string and the SQL string to reflect your OLAP server’s configuration.Paste the following code into the General Declarations section of Form1:
NOTE: A cube query (MDX query) has the following layout that defines the number of Axes in the query. The count of the fields referenced between SELECT and FROM in the MDX statement are the number of Axes in the query.

SELECT <axis_specification> [, <axis_specification>...] FROM <cube_specification>WHERE <slicer_specification>

Option ExplicitConst strConnect = “Data Source=<DataSource>;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart”Private Sub Form_Click()On Error GoTo ErrorHandlerDim adoCst As ADOMD.CellsetDim objAdoData As ADOBusObj.Class1Dim strOutput As StringDim intStrLen As IntegerDim intDC0 As IntegerDim intDC1 As IntegerDim intPC0 As IntegerDim intPC1 As IntegerDim i As IntegerDim j As IntegerDim k As IntegerSet objAdoData = CreateObject(“ADOBusObj.Class1″)With objAdoData.SQL = “Select {[Measures].members} On Columns,” & _”Non Empty [Store].[Store City].members ” & _”Properties [Store].[Store Type], [Store].[Store Manager] ” & _”On Rows From Sales”.ADOMDConnect strConnect, 20 ‘Establish connection.End With’Return the Cellset from MD Data Object.Set adoCst = objAdoData.GetRs’it is known up front there are two axes for this query so,’just check each axis for number of dimensions.intDC0 = adoCst.Axes(0).DimensionCount – 1intDC1 = adoCst.Axes(1).DimensionCount – 1intPC0 = adoCst.Axes(0).Positions.Count – 1intPC1 = adoCst.Axes(1).Positions.Count – 1For i = 0 To intDC0For j = 0 To intPC0intStrLen = Len(adoCst.Axes(0).Positions(j).Members(i).Caption)If intStrLen > 15 Then intStrLen = 0strOutput = strOutput & “[" & adoCst.Axes(0).Positions(j).Members(i).Caption & "]” & _String(3, vbTab) & Space(15 – intStrLen)Next jNext iDebug.Print strOutput & vbCrLfFor i = 0 To intPC1strOutput = “”For j = 0 To intDC1Debug.Print “– ” & adoCst.Axes(1).Positions(i).Members(j).Caption & ” –”Next jFor k = 0 To intPC0intStrLen = Len(adoCst(k, i).FormattedValue)If intStrLen > 15 Then intStrLen = 0strOutput = strOutput & adoCst(k, i).FormattedValue & _Space(15 – intStrLen) & String(4, vbTab)Next kDebug.Print strOutputNext iMsgBox “Success”, vbOKOnly, “MD Data Object”Exit SubErrorHandler:MsgBox “Change Failed:” & vbCrLf & _Err.Number & _vbCrLf & Err.Description, _vbOKOnly, “Data Object”Exit SubEnd Sub

Symptoms
When concatenating large strings on the order of 50kb or larger (for example, building an HTML table from a database), the length of time to complete can become quite long as the string gets larger. This article demonstrates an alternative to normal concatenation that can improve performance for large strings by 20 times or more.
Resolution
When performing repeated concatenations of the type:

For I = 1 To NDest = Dest & SourceNext N the length of time increases proportionally to N-squared. Therefore, 1000 iterations will take about 100 times longer than 100 iterations. This is because Visual Basic does not just add the Source characters to the end of the Dest string; it also performs the following operations:
Allocates temporary memory large enough to hold the result. Copies Dest to the start of the temporary area. Copies Source to the end of the temporary area. De-allocates the old copy of Dest. Allocates memory for Dest large enough to hold the result. Copies the temporary data to Dest. Steps 2 and 6 are very expensive and basically result in the entire concatenated result being copied twice with additional overhead to allocate and de-allocate memory.
This article details a method using the Mid$ statement and pre-allocating memory in larger chunks to eliminate all but step 3 above for most of the concatenation phase.
WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
Step-by-Step ExampleType the following code into a module:

Option Explicit’ For 16-bit products, uncomment the next three lines by removing the’ single quotes and add a single quote to comment out the following’ three lines.’Const ConcatStr = “ABC”‘Const ccIncrement = 15000′Declare Function GetTickCount Lib “USER” () As LongConst ConcatStr = “ABCDEFGHIJKLMNOPQRSTUVWXYZ”Const ccIncrement = 50000Private Declare Function GetTickCount Lib “KERNEL32″ () As LongDim ccOffset As LongSub StdConcat(ByVal LoopCount As Long)Dim BigStr As String, I As Long, StartTick As LongStartTick = GetTickCount()For I = 1 To LoopCountBigStr = BigStr & ConcatStrNext IDebug.Print LoopCount; “concatenations took”;Debug.Print GetTickCount() – StartTick; “ticks”End SubSub Test_Concat()Debug.Print “Using standard concatenation”StdConcat 1000StdConcat 2000StdConcat 3000StdConcat 4000StdConcat 5000Debug.PrintDebug.Print “Using pre-allocated storage and pseudo-concatenation”MidConcat 1000MidConcat 2000MidConcat 3000MidConcat 4000MidConcat 5000End SubSub Concat(Dest As String, Source As String)Dim L As LongL = Len(Source)If (ccOffset + L) >= Len(Dest) ThenIf L > ccIncrement ThenDest = Dest & Space$(L)ElseDest = Dest & Space$(ccIncrement)End IfEnd IfMid$(Dest, ccOffset + 1, L) = SourceccOffset = ccOffset + LEnd SubSub MidConcat(ByVal LoopCount As Long)Dim BigStr As String, I As Long, StartTick As LongStartTick = GetTickCount()ccOffset = 0For I = 1 To LoopCountConcat BigStr, ConcatStrNext IBigStr = Left$(BigStr, ccOffset)Debug.Print LoopCount; “pseudo-concatenations took”;Debug.Print GetTickCount() – StartTick; “ticks”End Sub In the Debug/Immediate Window, type Test_Concat, and hit the Enter key.
The results will look similar to:

Using standard concatenation1000 concatenations took 2348 ticks2000 concatenations took 8954 ticks3000 concatenations took 20271 ticks4000 concatenations took 35103 ticks5000 concatenations took 54453 ticksUsing pre-allocated storage and pseudo-concatenation1000 pseudo-concatenations took 82 ticks2000 pseudo-concatenations took 124 ticks3000 pseudo-concatenations took 165 ticks4000 pseudo-concatenations took 247 ticks5000 pseudo-concatenations took 289 ticks
Additional InformationThe code may take a couple of minutes to run. GetTickCount returns the number of milliseconds since Windows was started. Therefore, the output is in milliseconds. Performance improvement ranges from almost 30 times for the 1000-iteration case to almost 200 times for the 5000-iteration case. These times may vary depending on:
The product used. Your system configuration..The size of ccIncrement (larger size favors MidConcat).The number of iterations used (more iterations favors MidConcat).The size of the resultant string (larger size favors MidConcat).

Symptoms
This article describes how to authenticate the Inbox in Microsoft Exchange Server 2003 with forms-based authentication enabled by using Microsoft Visual Basic .NET.
Resolution
To access the Inbox in Exchange Server 2003, you must set up WebDAV access to Exchange Server 2003 in your Web application. You must also authenticate the Inbox in Exchange Server 2003 to the Exchange Server 2003 server that is enabled with forms-based authentication. The following Visual Basic .NET code sample and Visual Basic .NET function accomplish both of these tasks.WebDAV accessTo set up WebDAV access to Exchange Server 2003 in your Web application, use the following Visual Basic .NET code sample as a reference.

Dim strServerName as String = “Server Name” ‘ TODO: Change to your environmentDim strDomain as String = “Domain Name” ‘ TODO: Change to your environmentDim strUserID as String = “Username” ‘ TODO: Change to your environmentDim strPassword as String = “Password” ‘ TODO: Change to your environment’ Create our destination URL.Dim strURL As String = “https://” & strServerName & “/exchange/” & strUserName & “/inbox/test.eml”Dim strReusableCookies As String’ Create our Web request object.GETRequest = CType(WebRequest.Create(New System.Uri(strURL & strApptItem)), HttpWebRequest)strReusableCookies = AuthenticateSecureOWA(strServerName, strDomain, strUserID, strPassword)’ Add the cookie set that is obtained after OWA authentication to our request header.PROPPATCHRequest.Headers.Add(“Cookie”, strReusableCookies)PROPPATCHRequest.ContentType = “text/xml”PROPPATCHRequest.KeepAlive = TruePROPPATCHRequest.AllowAutoRedirect = False’ Specify the PROPPATCH method.PROPPATCHRequest.Method = “GET”‘ Enter your WebDAV-related code here.NoteIn the Visual Basic .NET code sample that was just mentioned, the strReusableCookies string variable is the authentication cookie that is returned from the AuthenticateSecureOWA function call. If the authentication cookie times out, call the AuthenticateSecureOWA function again to receive a new authentication cookie.
Another way to work around this problem is to put the WebDAV request in a try/catch block. The try/catch block will catch the authentication cookie time-out error. When the authentication cookie time-out error occurs, you can re-authenticate the Inbox in Exchange Server 2003 to the Exchange Server 2003 server that is enabled with forms-based authentication.Authentication functionTo authenticate to Exchange Server 2003 with forms-based authentication enabled from your Web application, use the following Visual Basic .NET function:

Imports SystemImports System.NetImports System.IOImports System.XmlImports System.Text.RegularExpressions’ Code to call the Authentication:Private CookieJar As CookieContainerPrivate strCookies As String’ Authenticate to OWA. Assign the returned cookies to a string.Dim strReusableCookies As StringstrReusableCookies = AuthenticateSecureOWA(strServerName, strDomain, strUserID, strPassword)’Implementation of the Authentication to the Exchange Server 2003 server that is enabled with forms-based authenticationPrivate Function AuthenticateSecureOWA(ByVal strServerName As String, ByVal strDomain As String, ByVal strUserName As String, ByVal strPassword As String) As StringDim AuthURL As System.UriTry’ Construct our destination URI.AuthURL = New System.Uri(“https://” + strServerName + “/exchweb/bin/auth/owaauth.dll”)Catch ex As Exception.MsgBox(“Error occurred while you are creating the URI for OWA authentication!” + vbCrLf + vbCrLf + ex.Message)Return “Error”End TryDim WebReq As HttpWebRequestCookieJar = New CookieContainer’ Create our request object for the constructed URI.WebReq = CType(WebRequest.Create(AuthURL), HttpWebRequest)WebReq.CookieContainer = CookieJar’ Create our post data string that is required by OWA (owaauth.dll).Dim strPostFields As String = “destination=https%3A%2F%2F” & strServerName & “%2Fexchange%2F” + strUserName + “%2F&username=” + strDomain + “%5C” + strUserName + “&password=” + strPassword + “&SubmitCreds=Log+On&forcedownlevel=0&trusted=0″WebReq.KeepAlive = TrueWebReq.AllowAutoRedirect = FalseWebReq.Method = “POST”‘ Store the post data into a byte array.Dim PostData() As Byte = System.Text.Encoding.ASCII.GetBytes(strPostFields)’ Set the content length.WebReq.ContentLength = PostData.LengthDim tmpStream As StreamTry’ Create a request stream. Write the post data to the stream.tmpStream = WebReq.GetRequestStream()tmpStream.Write(PostData, 0, PostData.Length)tmpStream.Close()Catch ex As Exception.MsgBox(“Error occurred while trying OWA authentication!” + vbCrLf + vbCrLf + ex.Message)Return “Error”End Try’ Get the response from the request.Dim WebResp As HttpWebResponse = WebReq.GetResponse()’ Create a stream to capture the response dataDim tmpStreamRead As New StreamReader(WebResp.GetResponseStream())’ Write returned data to a string.Dim strResponseData As String = tmpStreamRead.ReadToEnd()tmpStreamRead.Close()’ Close the response object.WebResp.Close()’ Get our returned cookie set.strCookies = CookieJar.GetCookieHeader(AuthURL).ToString()’ Filter for our cadata and session ID cookies.Dim strCADataCookie As String = Regex.Replace(strCookies, “(.*)cadata=”"(.*)”"(.*)”, “$2″)Dim strSessionIDCookie As String = Regex.Replace(strCookies, “(.*)sessionid=(.*)(,|;)(.*)”, “$2″)’ Create and return the cookie set for performing subsequent Web requests.strCookies = “sessionid=” + strSessionIDCookie + “; ” + “cadata=” + strCADataCookieReturn strCookiesEnd Function

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.