Hej!
Jag ska via SQL-satsen nedan lägga till en Yes/No kolumn i en access 2000 databas som min applikation är länkad till;

ALTER TABLE myTray ADD COLUMN MyCol BIT;

Kolumnen läggs dit som den ska MEN den får ett tomt värde i egenskapen "Format".
Jag vill att "Format" ska vara satt till Yes/No.

Hur lyckas jag med detta??


Mvh,

Sofia

Jag känner inte till hur du kan ange format med SQL kod.
Men du kan göra det genom att använda DAO.

Hej Andreas!
Jag har försökt med det med men lyckas inte.
Har du någon kodsnutt jag kan använda mig av?
Det gäller alltså en länkad databas, tabellen ligger inte i samma db som koden körs i.


Stort tack!


Mvh,

Sofia

Om det är en länkad tabell så måste du göra förändringen i källdatabasen.

Jag har skrivit en generell funktion för att uppdater egenskaper:

Public Sub SetProperty(FileName As String, TableName As String, FieldName As String, PropertyName As String, Value As Variant)
Dim Database As DAO.Database
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Property As DAO.Property
On Error GoTo FixFormat_Err
    Set Database = OpenDatabase(FileName)
    Set Table = Database.TableDefs(TableName)
    Set Field = Table.Fields(FieldName)
    Set Property = Field.Properties(PropertyName)
    Property.Value = Value

FixFormat_Exit:
    Exit Sub
    
FixFormat_Err:
    Select Case Err.Number
    Case 3270 'Property not found.
        Set Property = Field.CreateProperty(PropertyName, dbText, Value)
        Field.Properties.Append Property
        Resume FixFormat_Exit
    Case Else
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End Select
    
End Sub

Exempel:

Public Sub test()
    SetProperty "D:\My documents\db3.mdb", _
                "myTray", _
                "MyCol", _
                "Format", _
                "Yes/No"
End Sub

Hoppas det är till hjälp för dig.

Tack snälla Andreas!
Funkar kalasbra!


TACK!


Mvh,

Sofia

Hej igen!
Med Andreas fina funktion hade man ju då kunnat tro att man även kunnat modifiera Display Control och sätta den till "Check Box"....

Men icke.

Jag anropar funktionen med följande kod:
SetProperty("C:\minDatabas.mdb", "myTable", "myField", "Display Control", "Check Box")

Och funktionen ser ju ut som ovan, vill säga:

Public Function SetProperty(FileName As String, TableName As String, FieldName As String, PropertyName As String, Value As Variant) As Boolean
Dim Database As DAO.Database
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Property As DAO.Property
On Error GoTo FixFormat_Err
If FileName = "Me" Then
Set Database = CurrentDb
Else
Set Database = OpenDatabase(FileName)
End If
Set Table = Database.TableDefs(TableName)
Set Field = Table.Fields(FieldName)
Set Property = Field.Properties(PropertyName)
Property.Value = Value

FixFormat_Exit:
SetProperty = True
Exit Function

FixFormat_Err:
Select Case err.Number
Case 3270 'Property not found.
Set Property = Field.CreateProperty(PropertyName, dbText, Value)
Field.Properties.Append Property
Resume FixFormat_Exit
Case Else
SetProperty = False
End Select

End Function

Inga fel generas men heller inget händer.

Vad gör jag för fel?


Mvh,

Sofia

Jag skapade ett fält i tabellen och använde följande kod för att lista egenskaper, typ och värden för den kolumenen:

Private Function DatTypeName(DataType As DAO.DataTypeEnum) As String
    Select Case DataType
    Case DAO.DataTypeEnum.dbBigInt
        DatTypeName = "dbBigInt"
    Case DAO.DataTypeEnum.dbBinary
        DatTypeName = "dbBinary"
    Case DAO.DataTypeEnum.dbBoolean
        DatTypeName = "dbBoolean"
    Case DAO.DataTypeEnum.dbByte
        DatTypeName = "dbByte"
    Case DAO.DataTypeEnum.dbChar
        DatTypeName = "dbChar"
    Case DAO.DataTypeEnum.dbCurrency
        DatTypeName = "dbCurrency"
    Case DAO.DataTypeEnum.dbDate
        DatTypeName = "dbDate"
    Case DAO.DataTypeEnum.dbDecimal
        DatTypeName = "dbDecimal"
    Case DAO.DataTypeEnum.dbDouble
        DatTypeName = "dbDouble"
    Case DAO.DataTypeEnum.dbFloat
        DatTypeName = "dbFloat"
    Case DAO.DataTypeEnum.dbGUID
        DatTypeName = "dbGUID"
    Case DAO.DataTypeEnum.dbInteger
        DatTypeName = "dbInteger"
    Case DAO.DataTypeEnum.dbLong
        DatTypeName = "dbLong"
    Case DAO.DataTypeEnum.dbLongBinary
        DatTypeName = "dbLongBinary"
    Case DAO.DataTypeEnum.dbMemo
        DatTypeName = "dbMemo"
    Case DAO.DataTypeEnum.dbNumeric
        DatTypeName = "dbNumeric"
    Case DAO.DataTypeEnum.dbSingle
        DatTypeName = "dbSingle"
    Case DAO.DataTypeEnum.dbText
        DatTypeName = "dbText"
    Case DAO.DataTypeEnum.dbTime
        DatTypeName = "dbTime"
    Case DAO.DataTypeEnum.dbTimeStamp
        DatTypeName = "dbTimeStamp"
    Case DAO.DataTypeEnum.dbVarBinary
        DatTypeName = "dbVarBinary"
    Case Else
        Err.Raise 5, "DatTypeName", "Invalid procedure call"
    End Select
End Function

Public Sub FieldProperties(Database As DAO.Database, TableName As String, FieldName As String)
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Property As DAO.Property
On Error Resume Next
    Set Table = Database.TableDefs(TableName)
    Set Field = Table.Fields(FieldName)
        For Each Property In Field.Properties
        Debug.Print Property.Name
        Debug.Print , "Type:", DatTypeName(Property.Type)
        Debug.Print , "Value:", ;
        Debug.Print Property.Value;
        Debug.Print
    Next
End Sub

Public Sub TEST()
Dim Database As DAO.Database
    Set Database = CurrentDb
    FieldProperties Database, "myTable", "myField"
End Sub

Ur dett får jag fram följande intressant information:
<info>
DisplayControl
Type: dbInteger
Value: 111
RowSourceType
Type: dbText
Value: Table/Query
RowSource
Type: dbMemo
Value: myTable
BoundColumn
Type: dbInteger
Value: 1
ColumnCount
Type: dbInteger
Value: 2
ColumnHeads
Type: dbBoolean
Value: False
ColumnWidths
Type: dbText
Value: 0
ListRows
Type: dbInteger
Value: 10
ListWidth
Type: dbText
Value: 0twip
LimitToList
Type: dbBoolean
Value: True
</info>


För att skapa detta i en anna databas kan du använda följande kod:

Public Function SetProperty(Database As DAO.Database, TableName As String, FieldName As String, PropertyName As String, Value As Variant, Optional DataType As DAO.DataTypeEnum = dbText) As Boolean
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Property As DAO.Property
On Error GoTo FixFormat_Err
    Set Table = Database.TableDefs(TableName)
    Set Field = Table.Fields(FieldName)
    Set Property = Field.Properties(PropertyName)
    Property.Value = Value

FixFormat_Exit:
    SetProperty = True
    Exit Function
    
FixFormat_Err:
    Select Case Err.Number
    Case 3270 'Property not found.
        Set Property = Field.CreateProperty(PropertyName, DataType, Value)
        Field.Properties.Append Property
        Resume FixFormat_Exit
    Case Else
        SetProperty = False
    End Select
    
End Function

Public Sub TEST()
Dim Database As DAO.Database
    Set Database = CurrentDb
    SetProperty Database, "myTable", "myField", "DisplayControl", 111, dbInteger
    SetProperty Database, "myTable", "myField", "RowSourceType", "Table/Query", dbText
    SetProperty Database, "myTable", "myField", "RowSource", "SELECT id, Text FROM Table1", dbMemo
    SetProperty Database, "myTable", "myField", "BoundColumn", 1, dbInteger
    SetProperty Database, "myTable", "myField", "ColumnCount", 2, dbInteger
    SetProperty Database, "myTable", "myField", "ColumnHeads", False, dbBoolean
    SetProperty Database, "myTable", "myField", "ColumnWidths", 0, dbText
    SetProperty Database, "myTable", "myField", "ListRows", 10, dbInteger
    SetProperty Database, "myTable", "myField", "ListWidth", "0twip", dbText
    SetProperty Database, "myTable", "myField", "LimitToList", True, dbBoolean

End Sub

I detta fall så är det inte så "effektivt" att använda SetProperty eftersom eden är tvungen att slå upp tabell och fält.

UTan istället skriva:
<code>
Public Function SetProperty(Field As DAO.Field, PropertyName As String, Value As Variant, Optional DataType As DAO.DataTypeEnum = dbText) As Boolean
Dim Property As DAO.Property
On Error GoTo FixFormat_Err
Set Property = Field.Properties(PropertyName)
Property.Value = Value

FixFormat_Exit:
SetProperty = True
Exit Function

FixFormat_Err:
Select Case Err.Number
Case 3270 'Property not found.
Set Property = Field.CreateProperty(PropertyName, DataType, Value)
Field.Properties.Append Property
Resume FixFormat_Exit
Case Else
SetProperty = False
End Select

End Function

Public Sub TEST()
Dim Database As DAO.Database
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Set Database = CurrentDb
Set Table = Database.TableDefs("myTable")
Set Field = Table.Fields("myField")
SetProperty Field, "DisplayControl", 111, dbInteger
SetProperty Field, "RowSourceType", "Table/Query", dbText
SetProperty Field, "RowSource", "SELECT id, Text FROM Table1", dbMemo
SetProperty Field, "BoundColumn", 1, dbInteger
SetProperty Field, "ColumnCount", 2, dbInteger
SetProperty Field, "ColumnHeads", False, dbBoolean
SetProperty Field, "ColumnWidths", 0, dbText
SetProperty Field, "ListRows", 10, dbInteger
SetProperty Field, "ListWidth", "0twip", dbText
SetProperty Field, "LimitToList", True, dbBoolean

End Sub
</code>