1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| Private Function CompareFieldDataType(ByRef r_ImportTable As TableDef, ByRef r_TargetField As Field, ByRef r_ImportField As Field) As Boolean
'//*****Check target field datatype vs import field datatype*****
Select Case r_TargetField.Type
'//******Same datatype*****
Case r_ImportField.Type
'//Check data size
If CompareFieldDataSize(r_ImportTable, r_ImportField, r_TargetField) Then
CompareFieldDataType = True
Else
CompareFieldDataType = False
End If
'//*****Import datatype ID inferior to target field*****
Case Is > r_ImportField.Type
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'//%%%%Check target field%%%%
Select Case r_TargetField.Type
'//%%%%Target field type is a number%%%%
Case 2 Or 4 Or 6 Or 7
'''''''''''''''''''''''''''''''''''''''''''''
'//******Check import field datatype*****
Select Case r_ImportField.Type
'//Import field is a number
Case 2 Or 4 Or 6 Or 7
CompareFieldDataType = True
Case Else
CompareFieldDataType = False
End Select
'''''''''''''''''''''''''''''''''''''''''''
'//%%%%Target field type is text%%%%
Case 10
'//Check data size
If CompareFieldDataSize(r_ImportTable, r_ImportField, r_TargetField) Then
CompareFieldDataType = True
Else
CompareFieldDataType = False
End If
'//%%%%Target field type is memo%%%%
Case 12
CompareFieldDataType = True
End Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'//*****Import datatype ID superior to target field******
Case Is < r_ImportField.Type
CompareFieldDataType = False
End Select
End Function
Private Function CompareFieldDataSize(ByRef r_ImportTable As TableDef, r_ImportField As Field, r_TargetField As Field) As Boolean
Dim l_daoRecordset As recordset
Dim l_daoDB As Database
Dim l_lngMaxLength As Long
Dim l_strSQL As String
Set l_daoDB = CurrentDb()
'//Import field is not text
If r_ImportField.Type <> 10 Then
'//Check import field data size
Select Case r_ImportField.Size
'// Import field size superior to target field
Case Is > r_TargetField.Size
CompareFieldDataSize = False
'// Import field size inferior to target field
Case Is <= r_TargetField.Size
CompareFieldDataSize = True
End Select
'//Import field is text
ElseIf r_ImportField.Type = 10 Then
'//SQL statement: max length of text field in the import table
l_strSQL = "SELECT TypeName("
l_strSQL = l_strSQL & r_ImportField.Name & ") AS TypeName, Max(Len (" & r_ImportField.Name & ")) AS Length "
l_strSQL = l_strSQL & "FROM " & r_ImportTable.Name
l_strSQL = l_strSQL & " GROUP BY TypeName(" & r_ImportField.Name & ")"
l_strSQL = l_strSQL & "HAVING (((TypeName(" & r_ImportField.Name & "))<>""null""));"
Set l_daoRecordset = l_daoDB.OpenRecordset(l_strSQL)
l_lngMaxLength = l_daoRecordset.Fields(1).Value
If l_lngMaxLength <= r_TargetField.Size Then
CompareFieldDataSize = True
Else
CompareFieldDataSize = False
End If
End If
End Function |
Partager