Вот некоторый код, который я написал, чтобы изменить размер поля данных CSV, которые мне пришлось импортировать как все текстовые поля. Код получает информацию о полях из таблицы полей, размеров и типов, которые я создал, и их легко изменить с помощью изменений в этой таблице.
Sub AlterFieldStructure()
' this procedure changes the field size and field types (both text and number fields)
' in the "Rebuild Copy of Grade YYYY" tables
Dim db As Database
Dim cn As New ADODB.Connection
Dim rs_fields As New ADODB.Recordset
Dim rs_yrtable As New ADODB.Recordset
Dim SQL As String
Dim fieldlist As String
Dim yr As Variant
Dim yrArr As Variant
Dim fld As Variant
Dim fldArr()
Dim i As Long
Dim j As Long
Dim ErrMsg As String
On Error GoTo Err
Set db = CurrentDb
Set cn = CurrentProject.Connection
' get list of fields to query the "Copy from Grade YYYY" tables
SQL = "SELECT FieldName, DataType, FieldSize FROM GradeTableFields;"
rs_fields.Open SQL, cn
rs_fields.MoveFirst
i = 1
' Array for field name, datatype, and size
Do Until rs_fields.EOF = True
If rs_fields!FieldName <> "ID" Then
ReDim Preserve fldArr(1 To 3, 1 To i)
fldArr(1, i) = rs_fields!FieldName
fldArr(2, i) = rs_fields!DataType
fldArr(3, i) = rs_fields!FieldSize
fieldlist = fieldlist & rs_fields!FieldName & ", "
i = i + 1
End If
rs_fields.MoveNext
Loop
j = i - 1
rs_fields.Close
Set rs_fields = Nothing
fieldlist = Left(fieldlist, Len(fieldlist) - 2)
yrArr = Array(2008, 2009, 2010, 2011, 2012)
For Each yr In yrArr
' for each field in field array
For i = 1 To j
SQL = "ALTER TABLE [Rebuild Copy of Grade " & yr & "] ALTER COLUMN " & _
"[" & fldArr(1, i) & "] "
If fldArr(2, i) = "Text" Then
SQL = SQL & "Text(" & fldArr(3, i) & ");"
ElseIf fldArr(2, i) = "Number" Then
SQL = SQL & fldArr(3, i) & ";"
Else
MsgBox "DataType for " & fldArr(1, i) & " is not Text or Number", vbExclamation + vbOKOnly
Exit Sub
End If
Debug.Print SQL
db.Execute SQL, dbFailOnError
Next i
Next yr
cn.Close
Set cn = Nothing
Set db = Nothing
Exit Sub
Err:
ErrMsg = "Error: " & Err.Number & " " & " " & Err.Description
Debug.Print ErrMsg
cn.Close
Set cn = Nothing
Set db = Nothing
End Sub