Listing and redefining columns properties programmatically.

[hesaenz]

Hello,
I have inherited an Access DB that has a considerable amount of tables and queries that has become too large for Access and I need to migrate to Sql Server. Unfortunately there are multiple issues that need to be cleaned up first. The table and column name are unconventional because they contain spaces and unusual characters (!@#%&/?) which makes them difficult to handle. Most of the columns also have the default 255 character length which makes indexing inefficient. The DB is not normalized so there is considerable redundancy. Rebuilding from scratch would take years.

I would like to create a table FieldChange with following columns "TableName", "OldColumnName", "OldColumnType", "OldColumnLength", "NewColumnName", "NewColumnType", "NewColumnLength". I would like to populate the Old column properties and then work with the DB subject matter expert to define more appropriate names and properties and update the table and associated dependent query definitions.

I use the following script that renames the column names but it is only a partial solution based on a manually defined table.
I need to:
1. Retrieve and Write the existing TableName, OLdColumnName, OldColumnType, OldColumnLength to table FieldChange.
- I am not sure how to do this
2. Maybe add a column "ProposedLength" and populate with Select Max(Length(OldColumnName))
3. Script the updating of column properties.
- I do not now how to do this.

I have some threads that appear to do this using vba but for older versions of access. Thanks, Hector



------------------------- work so-far --------
Sub RenameField()

Dim x As String
Dim TableName As String
Dim NewFieldName As String


Dim OldFieldName As String

'Examples
TableName = "TestTbl"
NewFieldName = "Chg"
OldFieldName = "#Chg"

On Error GoTo errhandler

Dim Db As DAO.Database
Dim rst1 As DAO.Recordset
Set Db = CurrentDb()
Set rst1 = Db.OpenRecordset("FieldChange")
rst1.MoveLast
rst1.MoveFirst
Do While Not rst1.EOF
OldFieldName = rst1.Fields(1).Value
NewFieldName = rst1.Fields(2).Value

For Each tdf In Db.TableDefs
' ignore system and temporary tables
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
Debug.Print tdf.Name

'Rename Column
For Each n In tdf.Fields
If n.Name = OldFieldName Then n.Name = NewFieldName
Next n

End If
Next

Set tdf = Nothing
rst1.MoveNext
Loop

Set Db = Nothing

ExitHere:
Set tdf = Nothing
Set Db = Nothing

MsgBox "Field Renamed"
Exit Sub

errhandler:
With Err
MsgBox "Error " & .Number & vbCrLf & .Description, _
vbOKOnly Or vbCritical, "RenameField "
End With

Resume ExitHere
End Sub

[June7]

1. get table field properties http://www.java2s.com/Code/VBA-Excel...properties.htm

2. that's an incomplete SELECT statement - might use DMax() instead.

3. more code that uses TableDefs - an example:

    'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
    Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    While Not rs.EOF
        If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
            'create field in new table
            cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
                        rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
                        IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
        End If
        If rs!DataType = "Text" Then
            'change the AllowZeroLength default Yes to No
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
        End If
        rs.MoveNext
    Wend