Alter Table Alter Column help (1 Viewer)

smig · Oct 23, 2015

Is there any way to set the Column's description using the Alter Table Alter Column ?

gemma-the-husky · Oct 23, 2015

I am not sure

Description may be a created property of the tabledef/fields object. Something like that.

jdraw · Oct 23, 2015

I agree with Dave. I think you make a property and populate it.

I use this code to take whatever is put in table description and put it into my own table (sort of mini dictionary)

Code:

 !table_description = tdf.Properties("description")

and similar thing for fields

Code:

!field_description = fld.Properties("description")

Here is a sample (allen Browne ado)

Code:

              'Set the field properties.
              'AutoNumber
140           With !ContractorID
150               Set .ParentCatalog = cat
                  
160               .Properties("Autoincrement") = True     'AutoNumber.
170               .Properties("Description") = "Automatically " & _
                      "generated unique identifier for this record."
180           End With

smig · Oct 23, 2015

Thanks
But how do I do it for table not in the current db ?

jdraw · Oct 23, 2015

From current db open a second db. Iterate the tables - when you find the one you want to add description --set the tdf.property("Description") accordingly.

Here's a routine I use to list tables in a second database. It may be helpful.
It says 2003 but I'm using 2010 at the moment.

Code:

'---------------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

      Dim dbsCurrent As DAO.Database
      Dim dbsSecond As DAO.Database
      Dim tbl As DAO.TableDef
10       On Error GoTo testdb_Error

20    Set dbsCurrent = CurrentDb
30    Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("c:\users\mellon\downloads\EquipInventoryOrLocation.mdb")
40    Debug.Print dbsCurrent.name
50    Debug.Print dbsSecond.name

60    For Each tbl In dbsSecond.TableDefs
70    Debug.Print "  " & tbl.name
80    Next
90    Debug.Print Now()

100      On Error GoTo 0
110      Exit Sub

testdb_Error:

120       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub

smig · Oct 23, 2015

Thank you

jdraw · Oct 23, 2015

You're welcome. Happy to help.

smig · Oct 24, 2015

Still stuck

Can't find Descriotion as a field property.

I used this code:

Code:

Dim DataDB As DAO.Database
Dim CurrDB As DAO.Database
Dim tbl As DAO.TableDef
Dim xx As DAO.Field
Dim rs As DAO.Recordset
Dim i As Long

Dim strSQL As String
Dim strAlterTableQRY As String

Set DataDB = OpenDatabase(strDataMDBFile, False, False, "MS Access; PWD=" & pbDataMDBFilePass & " ")
Set CurrDB = CurrentDb()

For Each tbl In DataDB.TableDefs
    Debug.Print tbl.Name
    For Each xx In tbl.Fields
        For i = 1 To 30
        Debug.Print "PropertyID " & i & " - " & xx.Properties(i).Name
        Next i
    Next
Next

Here are the properties I got (Code stoped at 28):

Code:

PropertyID 1 - Attributes
PropertyID 2 - CollatingOrder
PropertyID 3 - Type
PropertyID 4 - Name
PropertyID 5 - OrdinalPosition
PropertyID 6 - Size
PropertyID 7 - SourceField
PropertyID 8 - SourceTable
PropertyID 9 - ValidateOnSet
PropertyID 10 - DataUpdatable
PropertyID 11 - ForeignName
PropertyID 12 - DefaultValue
PropertyID 13 - ValidationRule
PropertyID 14 - ValidationText
PropertyID 15 - Required
PropertyID 16 - AllowZeroLength
PropertyID 17 - AppendOnly
PropertyID 18 - Expression
PropertyID 19 - FieldSize
PropertyID 20 - OriginalValue
PropertyID 21 - VisibleValue
PropertyID 22 - ColumnWidth
PropertyID 23 - ColumnOrder
PropertyID 24 - ColumnHidden
PropertyID 25 - DecimalPlaces
PropertyID 26 - DisplayControl
PropertyID 27 - GUID

jdraw · Oct 24, 2015

Try this to show properties.

Code:

Sub smig()
Dim datadb As DAO.Database
Set datadb = CurrentDb
On Error Resume Next  '<<<need this in case property is NULL
For Each tbl In datadb.TableDefs
    Debug.Print "Table  :" & tbl.name
    For Each xx In tbl.Fields
        For i = 1 To 30
        Debug.Print vbTab & "Field   PropertyID " & i & " - " & xx.Properties(i).name
        Next i
    Next
Next
End Sub

jdraw · Oct 24, 2015

I don't think the Description property exists until you put a value in the description area of the table/field design screen.

Here is another version to get table descriptions, if they exist.

Code:

Sub smig()
    Dim datadb As DAO.Database
    Set datadb = CurrentDb
    On Error Resume Next
    For Each tbl In datadb.TableDefs
        If Not (tbl.name Like "~*" Or tbl.name Like "MSys*") Then
            Debug.Print "Table  :" & tbl.name & vbCrLf & IIf(GetTableDescr(tbl.name) & vbNullString = "", "", " --  " & GetTableDescr(tbl.name))
            For Each xx In tbl.Fields
                Debug.Print vbTab & vbTab & "Field " & xx.name & " - " & xx.Properties("Description").Value
            Next xx

        End If
    Next
End Sub

Code:

Public Function GetTableDescr(stTableName As String) As String
On Error Resume Next
GetTableDescr = CurrentDb.TableDefs(stTableName).Properties("Description").Value
End Function

gemma-the-husky · Oct 24, 2015

smig said:

Still stuck

Can't find Descriotion as a field property.

I used this code:

Code:

Dim DataDB As DAO.Database
Dim CurrDB As DAO.Database
Dim tbl As DAO.TableDef
Dim xx As DAO.Field
Dim rs As DAO.Recordset
Dim i As Long

Dim strSQL As String
Dim strAlterTableQRY As String

Set DataDB = OpenDatabase(strDataMDBFile, False, False, "MS Access; PWD=" & pbDataMDBFilePass & " ")
Set CurrDB = CurrentDb()

For Each tbl In DataDB.TableDefs
    Debug.Print tbl.Name
    For Each xx In tbl.Fields
        For i = 1 To 30
        Debug.Print "PropertyID " & i & " - " & xx.Properties(i).Name
        Next i
    Next
Next

Here are the properties I got (Code stoped at 28):

Code:

PropertyID 1 - Attributes
PropertyID 2 - CollatingOrder
PropertyID 3 - Type
PropertyID 4 - Name
PropertyID 5 - OrdinalPosition
PropertyID 6 - Size
PropertyID 7 - SourceField
PropertyID 8 - SourceTable
PropertyID 9 - ValidateOnSet
PropertyID 10 - DataUpdatable
PropertyID 11 - ForeignName
PropertyID 12 - DefaultValue
PropertyID 13 - ValidationRule
PropertyID 14 - ValidationText
PropertyID 15 - Required
PropertyID 16 - AllowZeroLength
PropertyID 17 - AppendOnly
PropertyID 18 - Expression
PropertyID 19 - FieldSize
PropertyID 20 - OriginalValue
PropertyID 21 - VisibleValue
PropertyID 22 - ColumnWidth
PropertyID 23 - ColumnOrder
PropertyID 24 - ColumnHidden
PropertyID 25 - DecimalPlaces
PropertyID 26 - DisplayControl
PropertyID 27 - GUID

that's the point. It's not an intrinsic property. You have to create it.

jdraw · Oct 24, 2015

smig,

Here is a routine to add a description to a table, and a test routine.

Code:

'---------------------------------------------------------------------------------------
' Procedure : assignTableDescription
' Author    : mellon
' Date      : 24/10/2015
' Purpose   : Assign a description to a table
'
' If a description already exists, overwrite it
'---------------------------------------------------------------------------------------
'
Function assignTableDescription(tableName As String, TableDesc As String)
    Dim db As DAO.Database
    Dim MyDesc As String

10  On Error GoTo assignDescription_Error

20  Set db = CurrentDb

30  db.TableDefs(tableName).Properties("description") = TableDesc

assignDescription_Error:
40  'MsgBox "error number is " & Err.number  'for debugging
50  If Err.number = 3270 Then
        ' Create property, set its value, and append it to the
        ' Properties collection.
60      Set prpNew = db.TableDefs(tableName).CreateProperty("Description", dbText, TableDesc)
70      db.TableDefs(tableName).Properties.Append prpNew

80  ElseIf Err.number = 0 Then
        Exit Function
    Else
90      MsgBox "Error " & Err.number & "  in line " & Erl & "   (" & Err.Description & ") in procedure assignDescription of Module DataDictionary"
100 End If

End Function

Test routine follows:

Code:

'---------------------------------------------------------------------------------------
' Procedure : smig2
' Author    : mellon
' Date      : 24/10/2015
' Purpose   : sample code to call assignTableDescription to add a description to a table
'
'  Calls: assignTableDescription
'---------------------------------------------------------------------------------------
'
Sub smig2()

'note can not add a description if one exists!!!
    Dim desc As String, resp
    Dim tblName As String
10  On Error GoTo smig2_Error

20  tblName = "animalcapture"    'put a table name here

80 ' desc = "AAAAAA  WWWW --S A M P L E - D E S C R I P T I O N--         PPPP  WWW"  'description here
   desc = "Sample table to mock up different Capture dates for animals to assist a poster"
90  Call assignTableDescription(tblName, desc)
100 Debug.Print tblName & vbCrLf & vbTab & CurrentDb.TableDefs(tblName).Properties("Description")
110 On Error GoTo 0
120 Exit Sub

smig2_Error:

130 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure smig2 of Module DataDictionary"
End Sub

smig · Oct 24, 2015

Thanks for the help
It seems it will only show existing properties and will index them according to this.
This mean property(index) means nothing as it will change

In my code what should I declare the XX as ? Tried as Field but this didnt work.
I'll try it all tomorrow.

jdraw · Oct 24, 2015

I don't think Description is a standard Property.
In my test, previous post, I could try to add a Description to table. If there was no existing description, I was forced to create the Property. I received error 3270 that the Property didn't exist, so I created the Description property and added a value.

If a Description already exists, you can replace it.

If you want to add a description to a Database, you use

db.Properties("Description")

for tables
db.tabledefs("tableName").Properties("Description")

for Fields
db.Tabledefs("tablename").Fields("FieldName").Properties("Description")

See this for info

Good luck.

smig · Oct 25, 2015

Now I'm good with this Description thing
Thank you

Is there any way to set where the Field be located in the table ?
Using Alter Table Add Column will put it at the end.

jdraw · Oct 26, 2015

Not that I am aware of. I think it gets appended.
You could probably create a new table with the field, then load data from the original.

smig · Nov 8, 2015

DB.TableDefs(strTableName).Fields(strColumnName).OrdinalPosition = lngPos
will put the column in the lngPos (First = 0)

jdraw · Nov 8, 2015

There are details here

smig · Nov 11, 2015

I can't put OrdinalPosition into order :banghead:

I try to order some fields after I put them in table.
here is the code I use

Code:

' --- Order the Ordered ones (PositionAfter)
strSQL = "SELECT * FROM [NewVersion_Tables_Columns] " & _
    "WHERE [dbVersion] > " & LatestDataMDBVersion(strDataMDBFile) & " " & _
    "AND Trim([ColumnName] & '') <> '' " & _
    "AND Trim([ColumnPositionAfter] & '') <> '' " & _
    "ORDER BY [TableName], [ColumnAddOrder]"

Set rs = CurrDB.OpenRecordset(strSQL)
With rs
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            Set DataDB = OpenDatabase(strDataMDBFile, False, False, "MS Access; PWD=" & pbDataMDBFilePass & " ")        ' db need to be set after each column so they can be ordered correctly
            strTableName = .Fields("TableName")
            strColumnName = .Fields("ColumnName")
            ' --- Set Position
strPrevPositionAfter & " - " & .Fields("ColumnPositionAfter")
            If strPrevTableName = strTableName And strPrevPositionAfter = .Fields("ColumnPositionAfter") Then
                x = x + 1
            Else
                x = 1
            End If
            For i = 0 To DataDB.TableDefs(strTableName).Fields.Count - 1
                If DataDB.TableDefs(strTableName).Fields(i).Name = .Fields("ColumnPositionAfter") Then
                    DataDB.TableDefs(strTableName).Fields(strColumnName).OrdinalPosition = i + x
Debug.Print DataDB.TableDefs(strTableName).Fields(strColumnName).Name & ", POS:" & DataDB.TableDefs(strTableName).Fields(strColumnName).OrdinalPosition & ", X=" & x
                End If
            Next i
            strPrevTableName = strTableName
            strPrevPositionAfter = .Fields("ColumnPositionAfter")
            DataDB.Close
            Set DataDB = Nothing
            .MoveNext
        Loop
        .Close
        Set rs = Nothing
    End If
End With

This is what I get in the Immediate window, this is good and what I expact.

Code:

OvedPasswordHintID, POS:[B]4[/B], X=1
OvedPasswordHintAnswer, POS:[B]5[/B], X=2
Test1, POS:[B]6[/B], X=3
Test2, POS:[B]7[/B], X=4

In the table they are positioned in places 4, 7, 9, 11

Nothing I try willput them in the correct place. Only the first one will go into place correctly.
Even tried to close the db after every change I make.

Tal

jdraw · Nov 11, 2015

smig,
I'm on my way out for a few hours, but will look later. I haven't done this specifically, but there is an example in the link I gave in post # 18.

Alter Table Alter Column help (1 Viewer)

Registered User.

Super Moderator

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Super Moderator

Super Moderator

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread