Very Computer

by **Effemme Sp** » Wed, 22 May 2002 18:02:47

Hello Everybody, I received support with the ADOX API reference
address, in the same time I was coding a routine to modify the name of a
column using ADOX (i send it ), I have some problems:

Restoring indexes of the table (adox.indexes) if i try to rename a primary
key field the code work but if i open the table with the update made
Access 2000 ask me a parameter like:

nametable.the_name_that_i_try_to_rename

1)I try to check all the property in the adox.table before and after my test
but i am not able to find any property with the name that access ask me.

2) Could i send the order of the new column with adox, for exemple if i try
to append a new colum is possible to put it in the first position of the
access table structure ?

I hope you understand my English...

With best regards

thank you,

Piva Giancarlo.

'###########################################################################
####################

Public colI As Collection
Public Temp As New ADOX.Index
Public Catalog As New ADOX.Catalog

Public Sub Modify_Column(ByRef MyCnn As ADODB.Connection, _
MyTblName As String, _
MyColName As String, _
MyNewColName As String, _
Optional MyLen As Long)

Dim Col As ADOX.Column
Dim Cols As ADOX.Columns
Dim Grp As ADOX.Group
Dim Grps As ADOX.Groups
Dim Ndx As New ADOX.Index
Dim Ndxs As ADOX.Indexes
Dim Key As ADOX.Key
Dim Keys As ADOX.Keys
Dim Proc As ADOX.Procedure
Dim Procs As ADOX.Procedures
Dim Prop As ADOX.Property
Dim Props As ADOX.Properties
Dim Table As ADOX.Table
Dim Tables As ADOX.Tables
Dim User As ADOX.User
Dim Users As ADOX.Users
Dim View As ADOX.View
Dim Views As ADOX.Views
Dim MyXcol As New ADOX.Column
Dim ThisIndex As New ADOX.Index
Dim MyRecordset As ADODB.Recordset
Dim MyArray() As Variant
Dim IsCounter As Boolean
Dim I As Integer
Dim n As Integer
Dim a As Integer
Dim iFile As Integer
Dim sFile As String
Dim strTempName As String

strTempName = MyColName & "_tmpFM"

Set Catalog.ActiveConnection = MyCnn
Set Tables = Catalog.Tables
Set Table = Tables(MyTblName)
Set colI = New Collection

'(GP) Verifico la presenza di indici li salvo _
in nella collezione ColIndexes e li rimuovo...

'###########################################################################
##############
For I = 0 To Table.Indexes.Count - 1
If Left$(Table.Indexes(I).Name, 2) <> "s_" Then
Set ThisIndex = Table.Indexes(I)
RecordIndexInfo Table.Indexes(I), colI
Set ThisIndex = Nothing
End If
Next
'Elimino...
Do While Table.Indexes.Count > 0 ' Inner loop.
Table.Indexes.Delete Table.Indexes(0).Name
Loop

'###########################################################################
##############

With Table
.Name = MyTblName
Set Cols = Tables(MyTblName).Columns

For Each Col In Cols

'(GP)Verifico se il nome campo passato corrisponde _
ad un campo realmente esistente...

If Col.Name = MyColName Then

MyXcol.Name = strTempName
MyXcol.Type = Col.Type
MyXcol.NumericScale = Col.NumericScale
MyXcol.DefinedSize = Col.DefinedSize + MyLen
MyXcol.ParentCatalog = Catalog

If Col.Properties(0).Value = False Then
IsCounter = False
ElseIf Col.Properties(0).Value = True Then
IsCounter = True
End If

'(GP)Store data into array...

'######################################################################
If Not IsCounter Then

Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MyTblName, MyCnn, adOpenKeyset,
, adCmdTable
ReDim MyArray(MyRecordset.RecordCount)
If MyRecordset.RecordCount > 0 Then

MyRecordset.MoveFirst

I = 0
Do Until MyRecordset.EOF
MyArray(I) =
MyRecordset.Fields(MyColName).Value
MyRecordset.MoveNext
I = I + 1
Loop
I = 0

End If
MyRecordset.Close

End If

'######################################################################

'MyXcol.Attributes =
.Columns.Item(MyColName).Attributes '(GP)NOT USED...
For I = 0 To Col.Properties.Count - 1
Select Case Col.Properties(I).Name

Case "Autoincrement"

If Col.Properties(I).Value = False
Then
MyXcol.Properties(I).Value =
False
ElseIf Col.Properties(I).Value =
True Then
MyXcol.Properties(I).Value =
True
End If

Case "Default"

If Not
IsEmpty(Col.Properties(I).Value) Then
'inserire controllo sul type col...
etc.etc...
Else
MyXcol.Properties(I).Value =
Empty
End If

Case "Description"

If Col.Properties(I).Value <> ""
Then
MyXcol.Properties(I).Value =
CStr(Col.Properties(I).Value)
Else
MyXcol.Properties(I).Value = ""
End If

Case "Nullable"

If Col.Properties(I).Value = False
Then
MyXcol.Properties(I).Value =
False
ElseIf Col.Properties(I).Value =
True Then
MyXcol.Properties(I).Value =
True
End If

Case "Fixed Length"

If Col.Properties(I).Value = False
Then
MyXcol.Properties(I).Value =
False
ElseIf Col.Properties(I).Value =
True Then
MyXcol.Properties(I).Value =
True
End If

'*****************************************************
' SI POSSONO SETTARE SOLO DOPO L' APPEND
...

'Case "Seed"

' If IsCounter = True Then
' MyXcol.Properties(I).Value =
CInt(Col.Properties(I).Value)
' Else
' MyXcol.Properties(I).Value = 1
' End If

'Case "Increment"

' If IsCounter = True Then
' MyXcol.Properties(I).Value =
CInt(Col.Properties(I).Value)
' Else
' MyXcol.Properties(I).Value = 1
' End If

'*****************************************************
Case "Jet OLEDB:Column Validation Text"

If Col.Properties(I).Value <> ""
Then
MyXcol.Properties(I).Value =
CStr(Col.Properties(I).Value)
Else
MyXcol.Properties(I).Value = ""
End If

Case "Jet OLEDB:Column Validation Rule"

If Col.Properties(I).Value <> ""
Then
MyXcol.Properties(I).Value =
CStr(Col.Properties(I).Value)
Else
MyXcol.Properties(I).Value = ""
End If

Case "Jet OLEDB:IISAM Not Last Column"

If Col.Properties(I).Value = False
Then
MyXcol.Properties(I).Value =
False
ElseIf Col.Properties(I).Value =
True Then
MyXcol.Properties(I).Value =
True
End If

Case "Jet OLEDB:AutoGenerate"

If Col.Properties(I).Value = False
Then
MyXcol.Properties(I).Value =
False
ElseIf Col.Properties(I).Value =
True Then
MyXcol.Properties(I).Value =
True
End If
...

read more »

by **Ayax Varg** » Fri, 24 May 2002 04:46:11

Mr Giancarlo,

I believe that if you only rename the column only the Colums collection,
you don't need to rename it in the indexes collection.
You cannot move a column to a different order using ADOX. You could create
a new table and copy the columns in the order in which you want them.
If you still have problems, it would be convenient if you could place a
more simplified piece of code that reproduces the problem and some details
about the problematic tables.

Hope this helps,

Ayax Vargas, MCSD, MCDBA
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights.

read more »

Very Computer

HELP ADOX rename a column

HELP ADOX rename a column

HELP ADOX rename a column