Darhl Thomason said:
Hi Gary,
Thanks for the advice. Your first reply is kind of what I was leaning
towards. I'm doing this in VB6, so I'm hoping the DAO method will work
here as well. Right now, my thought process is to read the old column
details, use that to create the new column, copy the data from the old
column to the new column, then delete the old column.
I don't think there are any constraints on the column, but I honestly
don't know. How would I check? I do know the column is only a "data"
column, the relationships are all built on the "ID" column.
Hi Darhl,
Sounds like you should be okay.
Cannot you just verify on a backup?
This may be "overkill" but here be an old Access code module
that can give all details in Debug window using DAO
(perhaps you can adapt to your purposes):
Option Compare Database
Option Explicit
Function fGetIndexes(pstrTableName As String) As Boolean
'adapted from NEAT CD
' Prints indexes in Immediate Window
On Error GoTo Err_fGetIndexes
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim indx As DAO.Index
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pstrTableName)
Debug.Print "=================================="
Debug.Print "INDEX Info for: " & pstrTableName
Debug.Print "=================================="
Debug.Print "Index Count: " & tdf.Indexes.Count
For i = 0 To tdf.Indexes.Count - 1
Set indx = tdf.Indexes(i)
Debug.Print "----------------"
Debug.Print vbCrLf & "Index Name: " & indx.Name
Debug.Print " Primary: " & indx.Primary
Debug.Print " Foreign: " & indx.Foreign
Debug.Print " Clustered: " & indx.Clustered
Debug.Print " Fields: " & indx.Fields
Debug.Print " Required: " & indx.Required
Debug.Print " Unique: " & indx.Unique
Debug.Print " Ignore Nulls: " & indx.IgnoreNulls
Next i
Debug.Print "========================================"
Debug.Print "End of INDEX Info for: " & pstrTableName
Debug.Print "========================================"
db.Close
fGetIndexes = True
Exit_fGetIndexes:
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_fGetIndexes:
fGetIndexes = False
If Err = 3265 Then
MsgBox "The table " & pstrTableName & " does not exist."
Resume Exit_fGetIndexes
Else
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume Exit_fGetIndexes
End If
End Function
Public Function FieldType(intType As Integer) As String
'from post by Dan Artuso
Select Case intType
Case dbBoolean
FieldType = "dbBoolean"
Case dbByte
FieldType = "dbByte"
Case dbInteger
FieldType = "dbInteger"
Case dbLong
FieldType = "dbLong"
Case dbCurrency
FieldType = "dbCurrency"
Case dbSingle
FieldType = "dbSingle"
Case dbDouble
FieldType = "dbDouble"
Case dbDate
FieldType = "dbDate"
Case dbText
FieldType = "dbText"
Case dbLongBinary
FieldType = "dbLongBinary"
Case dbMemo
FieldType = "dbMemo"
Case dbGUID
FieldType = "dbGUID"
End Select
End Function
Public Function FieldOutput(fldTemp As DAO.Field) As Variant
' adapted from Access Help
Dim prpLoop As DAO.Property
Dim strProp As String
' Enumerate Properties collection of passed Field
' object.
For Each prpLoop In fldTemp.Properties
' Some properties are invalid in certain
' contexts (the Value property in the Fields
' collection of a TableDef for example). Any
' attempt to use an invalid property will
' trigger an error.
On Error Resume Next
strProp = strProp & prpLoop.Name & " = " & _
prpLoop.Value & vbCrLf
On Error GoTo 0
Next prpLoop
FieldOutput = strProp
End Function
Public Function fGetRelations() As Boolean
On Error GoTo Err_fGetRelations
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
'open current db
Set db = CurrentDb
Debug.Print "============================"
Debug.Print "RELATIONS"
Debug.Print "============================"
For Each rel In db.Relations
With rel
Debug.Print "RelName = ", .Name
Debug.Print "Table = ", .Table
Debug.Print "ForeignTable = ", .ForeignTable
Debug.Print "RelationAttributes = ", .Attributes
For Each fld In .Fields
Debug.Print "FieldName = ", fld.Name
Debug.Print "ForeignTableFieldName = ", fld.ForeignName
Next
End With
Debug.Print "--------------------------"
Next
fGetRelations = True
db.Close
Exit_fGetRelations:
Set fld = Nothing
Set db = Nothing
Exit Function
Err_fGetRelations:
MsgBox Err.DescripTableion
Resume Exit_fGetRelations
End Function
Public Function fListTableInfo(pTable As String) As Boolean
On Error GoTo Err_fListTableInfo
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim strName As String
Dim strType As String
Dim varProp As Variant
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(pTable)
Debug.Print "Table: " & pTable
'fGetIndexes pTable
For i = 0 To tdf.Fields.Count - 1
Debug.Print "----------------------------"
Set fld = tdf.Fields(i)
varProp = FieldOutput(fld)
strName = fld.Name
strType = FieldType(tdf.Fields(i).Type)
If strType = "dbText" Then
strType = strType _
& " (" & tdf.Fields(i).Size & ")"
End If
'is it a primary key?
For Each idx In tdf.Indexes
If idx.Primary Then
' Found a Primary Key
For Each fld In idx.Fields
If fld.Name = strName Then
strType = strType & " (pk)"
Exit For
End If
Next fld
Exit For
End If
Next idx
Debug.Print "Field: " & strName & vbCrLf _
& "Field Type: " & strType _
& vbCrLf & varProp
Next i
Debug.Print "----------------------------"
db.Close
fListTableInfo = True
Exit_fListTableInfo:
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_fListTableInfo:
MsgBox Err.Description
Resume Exit_fListTableInfo
End Function
good luck,
gary
