Visual Basic Q&A

Symptoms
When you use FileMon or another utility to monitor file activity, if an application uses DAO 3.6x with Jet 4.0, you receive the following error message:

FILE NOT FOUNDfor the following dynamic-link libraries (DLLs):
MSJET49.DLL
MSJET48.DLL
MSJET47.DLL
MSJET46.DLL
MSJET45.DLL
MSJET44.DLL
MSJET43.DLL
MSJET42.DLL
MSJET41.DLLHowever, you also notice that MSJET40.DLL is loaded successfully.
Resolution
This behavior is by design to allow future Jet 4 functionality to be included into DLLs that are named incrementally. For example, a new version of Jet 4 can be implemented in a DLL named MSJET41.DLL. DAO 3.60 then automatically uses the new version of Jet 4, MSJET41.DLL.
Microsoft Development has found that it does not significantly impact DAO 3.60 or Jet 4.0 performance to load these DLLs.

Symptoms
The following error occurs when using RDO or ADO to update datain an Access (Jet) database:

Query is too complexDue to a limitation of the Jet database engine, this error can occur if the recordset to be updated contains morethan 40 fields.
Using the Jet 3.51 engine, the error occurs if the recordset to be updated contains more than 50 fields. With the Jet 4.0 engine and later, the error occurs if the recordset to be updated contains more than 99 fields.
Using the Jet 4.0 engine and later, you may also see the errors:

Expression too complex -or-

Too many defined fieldsThis article describes the problem scenario, the Jet limitation, and several workarounds.
Resolution
When executing the Update() or BatchUpdate() methods of RDO’s rdoResultsetobject, or the Update() or UpdateBatch() methods of ADO’s Recordset object,the ODBC error S1000 “Query is too complex” (Visual Basic run-time error40002) occurs. An error with the same description occurs using the Microsoft Jet OLE DB providers versions 3.51 and later.
This behavior occurs because the default behavior of ADO and RDO is to useeach field in the recordset to determine the record to be updated on theserver. That is, when the update is attempted, a SQL Update query is sentto the server. Part of this query is a WHERE clause that is used toidentify the record to be updated. An AND clause appears within the WHEREclause for each field to be used in that identification. Updating arecordset with more than 40 fields involves a WHERE clause with more than40 ANDs.
The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVINGclause. Jet 3.51 increased this limit to 50 AND clauses, and Jet 4.0 increased the limit to 99 AND clauses. The Access engine returns a “Query is too complex” error whenit encounters such a SQL statement. See the REFERENCES section of thisarticle for more information.
There are several ways to avoid this behavior:For new applications, use DAO when working with Access databases. DAOwas designed for this purpose, and will not issue a SQL query to performthe update. Note that RDO and especially ADO were designed to operateefficiently with many other types of databases and are recommended forapplications that will interact with various servers or that will beupsized in the future to use Microsoft SQL Server, for example.When opening the recordset to be updated (such as the RDO OpenResultsetor ADO OpenRecordset methods), select specific, and 40 or fewer fields.Use a server-side cursor:
To do this in ADO, set the CursorLocation location property of theRecordset object to adUseServer before opening the Recordset:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseServerrs.Open …… In RDO, set the CursorDriver property of the rdoEnvironment objectbefore calling the OpenConnection() method:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseServerSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)… Ensure that a unique key is used to identify the record to be updated,rather than the default behavior of using every field. A unique key(though not necessarily a primary key) must be defined in the underlyingrecordset for this technique to work. To do this in ADO (this will not work in versions prior to ADO2.0), set the Recordset object’s “Update Criteria” property toadCriteriaKey. This will work both for the Update() and UpdateBatch()methods:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseClientrs.Properties(“Update Criteria”).Value = adCriteriaKeyrs.Open …… For RDO, use the ClientBatch cursor library and ensure that theResultset object’s UpdateCriteria property is not set tordCriteriaAllCols. By default, this property is set to rdCriteriaKey,which will cause the use of the primary key to determine the row(s) tobe updated on the server:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseClientBatchSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)rs.Edit…rs.UpdateCriteria = rdCriteriaKeyrs.Update…

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.