Alter Table Query Question

[BobV]

Group:

I am adding a column to a table with the flollowing VBA statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN [Trade Number]
Number;"

The new column field is a number, but I also need to set the following field
properties:
Field Size: Double
Format: Standard
Decimal Places: 2
Default Value: 0
Required: No
Indexed: No

How should the above code be modified in order to set the above properties
for the new column that I am adding to the table?

Thank you,
BobV

 

[Allen Browne]

You should not have a problem adding a column of type DOUBLE. There's a list
of these type names and how they match the Access interface names in this
link:
http://members.iinet.net.au/~allenbrowne/ser-49.html

Can't remember for sure, but you may find that you are only able to use:
DEFAULT 0
if you execute the query under ADO. That's true for some of these attributes
such as WITH COMP.

However, the Format and Decimal Places properties can only be set through
DAO code. Unless they have been set already, the proerties don't exist, so
you have to create them. The code below creates/sets the property as needed.

Example usage:
Call SetPropertyDAO(CurrentDb().TableDefs("MyTable").Fields("MyField"),
"Format", dbText, "Standard")


--------------code starts------------------------
Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--------------code ends------------------------

 

[BobV]

Allen:

Thank you very much for your help and code.

BobV

You should not have a problem adding a column of type DOUBLE. There's a
list of these type names and how they match the Access interface names in
this link:
http://members.iinet.net.au/~allenbrowne/ser-49.html

Can't remember for sure, but you may find that you are only able to use:
DEFAULT 0
if you execute the query under ADO. That's true for some of these
attributes such as WITH COMP.

However, the Format and Decimal Places properties can only be set through
DAO code. Unless they have been set already, the proerties don't exist, so
you have to create them. The code below creates/sets the property as
needed.

Example usage:
Call SetPropertyDAO(CurrentDb().TableDefs("MyTable").Fields("MyField"),
"Format", dbText, "Standard")


--------------code starts------------------------
Function SetPropertyDAO(obj As Object, strPropertyName As String, intType
As Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--------------code ends------------------------

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Group:

I am adding a column to a table with the flollowing VBA statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN [Trade Number]
Number;"

The new column field is a number, but I also need to set the following
field properties:
Field Size: Double
Format: Standard
Decimal Places: 2
Default Value: 0
Required: No
Indexed: No

How should the above code be modified in order to set the above
properties for the new column that I am adding to the table?

Thank you,
BobV