Alter Table Alter Column help

[smig]

OK, I see where the problem is:
"Two or more Field objects in the same collection can have the same OrdinalPosition property value, in which case they will be ordered alphabetically"

This mean I'll have to reorder the table :banghead:

 

[jdraw]

smig,

I'm sure you have resolved the issue. But for anyone looking at the thread I did create a routine base on the M$oft link.

For what it's worth, I took one of my existing tables "tblBio" with fields

ID             auto
FName      text
LName      text
GradDate   date

and modified the ordinal positions

Starting design:
'EntryID table_name table_desc field_name field_desc ordinal_pos data_type length
'1388 tblBio ID 0 Long 4
'1389 tblBio Fname 1 Text 255
'1390 tblBio LName 2 Text 255
'1391 tblBio GradDate 3 Date 8
'---------------------------------------------------------------------------------------
''
'After running the procedure and then rerunning my dictionary routine the result was:
'EntryID table_name table_des field_name field_desc ordinal_pos data_type length
'1388 tblBio GradDate 61 Date 8
'1389 tblBio LName 68 Text 255
'1390 tblBio Fname 75 Text 255
'1391 tblBio ID 82 Long 4

I have attached the source vba.

 

[smig]

Yes, I solved this issue

First I created a table to hold the existing structure of my tables (The linked ones from the BE)
Then I filled this table

Public Sub fillTablesStructureTable()

Dim CurrDB As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim strDataMDBFile As String
Dim i As Long
Dim x As Long

Set CurrDB = CurrentDb()
Set rs = CurrDB.OpenRecordset("TablesStructure_Table")

With rs
    For Each tdf In CurrDB.TableDefs
        x = 0
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            For i = 0 To tdf.Fields.Count - 1
                Debug.Print tdf.Name & " - " & tdf.Fields(i).OrdinalPosition & " - " & tdf.Fields(i).Name
                .AddNew
                    .Fields("TableName") = tdf.Name
                    .Fields("NewOrdinalPosition") = x
                    .Fields("OldOrdinalPosition") = tdf.Fields(i).OrdinalPosition
                    .Fields("ColumnName") = tdf.Fields(i).Name
                .Update
                x = x + 1
            Next i
        End If
    Next tdf
    .Close
End With

End Sub

Now I can manually set a new order for fields.
After I manually set the new order I resort the tables I added columns to. Though it's very quick run I saw no reason to reorder all tables.

Public Sub ReOrderColumns(strDataMDBFile As String)

Dim DataDB As DAO.Database
Dim CurrDB As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strTableName As String
Dim strColumnName As String

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

strSQL = "SELECT [TablesStructure_Table].* " & _
    "FROM [TablesStructure_Table] INNER JOIN [NewVersion_Tables_Columns] ON [TablesStructure_Table].[TableName] = [NewVersion_Tables_Columns].[TableName] " & _
    "WHERE [NewVersion_Tables_Columns].[dbVersion] > " & LatestDataMDBVersion(strDataMDBFile) & " " & _
    "ORDER BY [TablesStructure_Table].[TableName], [TablesStructure_Table].[ColumnName] "

Set rs = CurrDB.OpenRecordset(strSQL)
With rs
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            strTableName = .Fields("TableName")
            strColumnName = .Fields("ColumnName")
            DataDB.TableDefs(strTableName).Fields(strColumnName).OrdinalPosition = .Fields("NewOrdinalPosition")
            .MoveNext
        Loop
        .Close
    End If
End With

Set rs = Nothing
CurrDB.Close
Set CurrDB = Nothing
DataDB.Close
Set DataDB = Nothing

End Sub

I'll probably save into my TablesStructure_Table all the field's constrains.
What I couldn't find is the property that will tell me if a field is a PrimaryKey.

 

[jdraw]

smig,
see attached vba code. There are pieces you could use re Indexes
'---------------------------------------------------------------------------------------
' Procedure : fjListIndexesToTable
' Author : mellon
' Date : 2/13/2009
' Purpose : To put the indexes of all tables in this database into a table.
' The table is Table_indexes. It is created dynamically when this procedure
' is executed. The table structure is as follows:
' table_name varchar(250),
' Index_name varchar(255),
' field_name varchar(250),
' primary_key varchar(1),
' foreign_key varchar(1),
' unique_values varchar(1),
' ignoreNulls varchar(1));
'
' Modified : 11/17/2011 Adjusted to get all index names for table
' including Foreign key maintained by Access.
' Ignore deleted tables, temp indexes and Msys tables.
'---------------------------------------------------------------------------------------
'

 

[smig]

smig,
see attached vba code. There are pieces you could use re Indexes
'---------------------------------------------------------------------------------------
' Procedure : fjListIndexesToTable
' Author : mellon
' Date : 2/13/2009
' Purpose : To put the indexes of all tables in this database into a table.
' The table is Table_indexes. It is created dynamically when this procedure
' is executed. The table structure is as follows:
' table_name varchar(250),
' Index_name varchar(255),
' field_name varchar(250),
' primary_key varchar(1),
' foreign_key varchar(1),
' unique_values varchar(1),
' ignoreNulls varchar(1));
'
' Modified : 11/17/2011 Adjusted to get all index names for table
' including Foreign key maintained by Access.
' Ignore deleted tables, temp indexes and Msys tables.
'---------------------------------------------------------------------------------------
'

Sorry, I lost it
Can't understand how to get the PrimaryKey.

 

[jdraw]

The vba code is in the attachment.
Copy the code, put it in a module and run the routine.
It should build a table, and populate it with index info or ll tables in your db.

 

[smig]

The vba code is in the attachment.
Copy the code, put it in a module and run the routine.
It should build a table, and populate it with index info or ll tables in your db.

I saw the attachment but couldn't understand it. Can't understand where the PK is hidden. Is it one of the Field's properties or what?

I won't use a code if I can't understand it.

 

[jdraw]

But you do read and understand vba, right?

I've taken an excerpt (heart of the process and added comments in UPPERCASE

    'Iterate over all tables that have Indexes
120 For Each tdf In db.TableDefs
130     With tdf  'FOR EACH TABLE
140         If Left(tdf.name, 4) = "MSys" Or _    'NOT SYSTEM OR DELETED
               Left(tdf.name, 1) = "~" Then GoTo tdf_Bypass
150         For Each idxLoop In .Indexes   'LOOK AT INDEXES FOR TABLE
160             Rind.AddNew         'create new record
170             Rind!table_name = tdf.name  'add table name
180             Debug.Print "table: " & .name
190             With idxLoop
200                 Debug.Print "  " & "Index: " & .name
210                 Rind!Index_name = idxLoop.name   "GET THE INDEX NAME

                    ' Enumerate Properties collection of each
                    ' Index object.
220                 Debug.Print "    Properties"
230                 For Each prpLoop In .Properties
240                     Debug.Print "    " & prpLoop.name & _
                                    " = " & IIf(prpLoop = "", "[empty]", _
                                                prpLoop)
                        ' Is this a Primary Key
250                     If prpLoop.name = "Primary" Then  'IS THIS A PRIMARY KEY
260                         If prpLoop = True Then
270                             hldPrimary = "Y"
280                         Else
290                             hldPrimary = "N"
300                         End If
310                     Else
320                     End If
                        ' Is this a unique index        'IS IT PART OF UNIQUE INDEX
330                     If prpLoop.name = "Unique" Then
340                         If prpLoop = True Then
350                             hldUnique = "Y"
360                         Else
370                             hldUnique = "N"
380                         End If
390                     Else
400                     End If

                        ' Does this index Ignore NULLS?  'DOES INDEX IGNORE NULLS
410                     If prpLoop.name = "IgnoreNulls" Then
420                         If prpLoop = True Then
430                             hldIgnoreNulls = "Y"
440                         Else
450                             hldIgnoreNulls = "N"
460                         End If
470                     Else
480                     End If


                        ' Does this index represent Foreign Key?
490                     If prpLoop.name = "Foreign" Then  'DOES INDEX REPRESENT FK
500                         If prpLoop = True Then
510                             hldForeign = "Y"
520                         Else
530                             hldForeign = "N"
540                         End If
550                     Else
560                     End If

570                 Next prpLoop

                    ' Enumerate Fields collection of each Index
                    ' object.

580                 i = 0    'ARE THERE MULTIPLE FIELDS IN THIS INDEX
590                 Debug.Print "    *Fields* making up this index-> " & idxLoop.name
600                 For Each fldLoop In .Fields
610                     i = i + 1
620                     Debug.Print "    " & "Field: " & fldLoop.name
630                     Rind!Field_Name = fldLoop.name
640                     Rind!Primary_key = hldPrimary
650                     Rind!Unique_Values = hldUnique
660                     Rind!IgnoreNulls = hldIgnoreNulls
670                     Rind!Foreign_key = hldForeign
                        ' if the index is composed of multiple fields
                        'then repeat the table, index information for each field
               
                        'THEN WRITE TO TABLE_INDEXES

 

[smig]

Yes, I read and write vba.

What I miss are these:
What is the structure? Db, index, table, field, property ? Db, table, property, index, field ? ...
Where the PK in all the structure is saved ?

 

[jdraw]

I have created a table, called Tables_indexes.
I process all tables in the current database.
If the table has 1 or more indexes, I record the name and the properties of that index.
An index name may or may not be the same as the related field name.
Access identifies the index that is the PK with index name "Primary Key" and sets the property to Y. I'm attaching a jpg of the first part of my output.

 

[smig]

Thanks
This make it clear.
I guess I'll be able to make it work now.


*** Edit ***
Are the prpLoop.Value can have only True or False ?
I made all the hld vars as boolean and prefer using
hldPrimary = prpLoop.Value

 

[smig]

Thank you

I have a working code now

Public Sub fillTablesStructureTable()

Dim CurrDB As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
    Dim idxLoop As Index        ' --- Index
    Dim fldLoop As DAO.Field        ' --- Fields in the Index
    Dim prpLoop     ' --- Index Properties
    Dim hldPrimary As Boolean       ' --- The property is PrimaryKey
    Dim hldUnique As Boolean       ' --- The property is Unique
    Dim hldForeign As Boolean       ' --- The property is ForeignKey
    Dim hldIgnoreNulls As Boolean       ' --- The property is IgnoreNulls
Dim strSQL As String
Dim i As Long
Dim x As Long

Set CurrDB = CurrentDb()

Set rs = CurrDB.OpenRecordset("TablesStructure_Table")

With rs
    For Each tdf In CurrDB.TableDefs
        x = 0
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            For i = 0 To tdf.Fields.Count - 1
                Debug.Print tdf.Name & " - " & tdf.Fields(i).OrdinalPosition & " - " & tdf.Fields(i).Name
                .AddNew
                    .Fields("TableName") = tdf.Name
                    .Fields("NewOrdinalPosition") = x
                    .Fields("OldOrdinalPosition") = tdf.Fields(i).OrdinalPosition
                    .Fields("ColumnName") = tdf.Fields(i).Name
                .Update
                x = x + 1
            Next i
        End If
    Next tdf
    .Close
End With


' --- Find PrimaryKey
For Each tdf In CurrDB.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
        
        For Each idxLoop In tdf.Indexes
            With idxLoop
                For Each prpLoop In idxLoop.Properties
                    Select Case prpLoop.Name
                        Case "Primary"      ' --- PrimaryKey
                            If prpLoop.Value = True Then
                                hldPrimary = True
                            Else
                                hldPrimary = False
                            End If
                        Case "Unique"        ' --- Unique
                            If prpLoop.Value = True Then
                                hldUnique = True
                            Else
                                hldUnique = False
                            End If
                        Case "IgnoreNulls"        ' --- IgnoreNulls
                            If prpLoop.Value = True Then
                                hldIgnoreNulls = True
                            Else
                                hldIgnoreNulls = False
                            End If
                        Case "Foreign"        ' --- Foreign Key
                            If prpLoop.Value = True Then
                                hldForeign = True
                            Else
                                hldForeign = False
                            End If
                        Case Else
                    End Select
                Next prpLoop
                ' --- Now catch up the Fields that making up this Index
                For Each fldLoop In idxLoop.Fields
                    strSQL = "SELECT * FROM [TablesStructure_Table] " & _
                        "WHERE [TableName] = '" & tdf.Name & "' AND [ColumnName] = '" & fldLoop.Name & "' "
                    Set rs = CurrDB.OpenRecordset(strSQL)
                    With rs
                        rs.MoveFirst
                        rs.Edit
                            If idxLoop.Name = "PrimaryKey" And hldPrimary = True Then
                                rs.Fields("ColumnPrimary") = True
                            End If
                        rs.Update
                    End With
                    rs.Close
                Next fldLoop
            End With
        Next idxLoop
    End If
Next tdf

Set rs = Nothing

End Sub

Do I realy need all these hld vars ?
Is it possible an Index name to be PrimaryKey but it's Primary property to be False ?

 

[gemma-the-husky]

smig

indexes are named whatever you want. If your PK is called ID, you will also probably get an index called ID, (as access automatically indexes fields ending in ID) as well the PK index.

you may find some "system indexes" with an attribute of "foreign" set to true. (I think it's foreign, offhand). When you add a RI link, access automatically adds an index named fieldname1fieldname2

these are hidden and are there to manage the RI link.

-----
I take it you are snapshotting the back end table structure?

 

[smig]

Thanks

Yes, I snapshooting the BE only. I might change it and only put a mark for BE/FE.

As jdraw pointed the PK are saved under the indexes named PrimaryKey. Is it possible that their 'Primary' property will be False? If not, looking at the name should be enough.

 

[gemma-the-husky]

There is a Boolean property of an index called "primary", which determines whether an index is primary or not.

index properties

clustered
distinctcount
fields - collection of fields that makes up the index
foreign
ignorenulls
name
primary
properties - collection
required
unique

per DAO Object Model (O'Reilly) - fantastic reference if you need it.


fwiw, I wrote a utility to snapshot databases in the way you are doing. I could snapshot my development backend, (tables indexes and relationships) and then automatically propagate the changes to a client's back end. I hoped to sell some, but never managed to sell any, though.

 

[jdraw]

smig,
Glad you have it working. I created a table when I first ran the routine. Then, deleted and recreated a new table with all the latest info each time.

You may find this useful. It shows Relations. It just lists all relations to the immediate window. You could put the output into a table if really required.

You can supply a table name if you are interested in 1 table's relation(s) only.

'---------------------------------------------------------------------------------------
' Procedure : ShowRelations
' Author    : mellon
' Date      : 11/06/2015
' Purpose   : To show the relations in the current database.
'             WHERE:
' RelationName is the name assigned to the relation
' TableName  is the From Table Name
' Foreign Table/To Table is the name of the To Table
' FieldName is the name of the field (PK in From Table)
' ForeignFieldName is the name of the field (FK in the To Table/Foreign Table)
'---------------------------------------------------------------------------------------
'
Function ShowRelations(Optional sTableName As String = "")
    Dim db As DAO.Database
    Dim rel As DAO.relation
    Dim fld As DAO.Field
    Dim errNoRelation As String
10        errNoRelation = " No relation exists for table (" & sTableName & ")"
20    On Error GoTo ShowRelations_Error

30    Set db = CurrentDb()
40
50    If sTableName & "" <> "" Then
60      For Each rel In db.Relations
70          If rel.table = sTableName Or rel.ForeignTable = sTableName Then
80              errNoRelation = ""
90              Debug.Print vbCrLf & "RELATION NAME :" & rel.name & vbCrLf & vbTab & vbTab & "FROM TABLENAME: " & rel.table & "  TO TABLENAME: " & rel.ForeignTable
100             For Each fld In rel.Fields
110                 Debug.Print vbTab & vbTab & vbTab & " FieldName: " & fld.name & "  ForeignFieldName: " & fld.ForeignName
120             Next fld
   
   
130         End If
140     Next rel
150   Debug.Print errNoRelation
160   Else
        '"No table name provided so list all relations including MSys System"
170     For Each rel In db.Relations
180         Debug.Print vbCrLf & "RELATION NAME :" & rel.name & vbCrLf & vbTab & vbTab & "FROM TABLENAME: " & rel.table & "  TO TABLENAME: " & rel.ForeignTable    ', rel.Attributes"
190         For Each fld In rel.Fields
200             Debug.Print vbTab & vbTab & vbTab & " FieldName: " & fld.name & "  ForeignFieldName: " & fld.ForeignName
210         Next fld

220     Next rel
230   End If
240   On Error GoTo 0
250   Exit Function

ShowRelations_Error:

260   MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ShowRelations "
End Function

Sample routine to show all relations all tables. No tablename supplied.

Sub showRelationsforTable()
    ShowRelations
End Sub

Calling the routine with a supplied table name.

Sub showRelationsforTable()
    ShowRelations ("Book")
End Sub

Typical output.

RELATION NAME :BOOKBOOKAuthors
        FROM TABLENAME: BOOK  TO TABLENAME: BOOKAuthors
             FieldName: BookId  ForeignFieldName: BookID

 

[smig]

I have it working great now

Only problem I have is how to add a ShortDate type of field, using the Alter Table Add column :banghead:

I can create a Date type field but can't find how to make it into a ShortDate format.