Add fields to backend's table

[Lars Brownies]

I need to add 8 fields to a backend's table. I need to do this for 8
identically structured backends (in use by 8 different departments). Can I
write a routine that takes care of all these changes in one action. I'm
thinking of creating one mdb with vba code that updates all the backends.

Thanks,

Lars

 

[Arvin Meyer MVP]

From Access 97 Help:

This example uses the CreateField method to create three Fields for a new
TableDef. It then displays the properties of those Field objects that are
automatically set by the CreateField method. (Properties whose values are
empty at the time of Field creation are not shown.)

Sub CreateFieldX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim fldLoop As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")

' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("TextField", dbText)

..Fields.Append .CreateField("IntegerField", dbInteger)
.Fields.Append .CreateField("DateField", dbDate)
End With

dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new Fields in " & tdfNew.Name

' Enumerate Fields collection to show the properties of
' the new Field objects.
For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name

For Each prpLoop In fldLoop.Properties
' Properties that are invalid in the context of

' TableDefs will trigger an error if an attempt
' is made to read their values.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
On Error GoTo 0
Next prpLoop

Next fldLoop

' Delete new TableDef because this is a demonstration.
dbsNorthwind.TableDefs.Delete tdfNew.Name
dbsNorthwind.Close

End Sub

 

[Lars Brownies]

Thanks!

Lars

From Access 97 Help:

This example uses the CreateField method to create three Fields for a new
TableDef. It then displays the properties of those Field objects that are
automatically set by the CreateField method. (Properties whose values are
empty at the time of Field creation are not shown.)

Sub CreateFieldX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim fldLoop As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")

' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("TextField", dbText)

.Fields.Append .CreateField("IntegerField", dbInteger)
.Fields.Append .CreateField("DateField", dbDate)
End With

dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new Fields in " & tdfNew.Name

' Enumerate Fields collection to show the properties of
' the new Field objects.
For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name

For Each prpLoop In fldLoop.Properties
' Properties that are invalid in the context of

' TableDefs will trigger an error if an attempt
' is made to read their values.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
On Error GoTo 0
Next prpLoop

Next fldLoop

' Delete new TableDef because this is a demonstration.
dbsNorthwind.TableDefs.Delete tdfNew.Name
dbsNorthwind.Close

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I need to add 8 fields to a backend's table. I need to do this for 8
identically structured backends (in use by 8 different departments). Can I
write a routine that takes care of all these changes in one action. I'm
thinking of creating one mdb with vba code that updates all the backends.

Thanks,

Lars

 

[Peter Hibbs]

Lars,

If this is something you are likely to do on a regular basis then have
look at the Back End Update Utility which will make it easier for you.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

HTH

Peter Hibbs.

 

[Lars Brownies]

Thanks Peter. I'll check it out. Indeed I'll expect having to do this more
often.

A first look at your code indicates that when you add a field to a table you
can't set the ordinal position of the field, so it will always be added at
the end of the table. Am I correct?

Lars

 

[Peter Hibbs]

Lars,

No, that is not correct, you can change the Ordinal Position of a
field (it's in the Field Properties Selection section) but it should
not really matter where it is - should it?

Peter Hibbs.

 

[Lars Brownies]

Thanks.

For the user it doesn't matter, but for the administrator I think it's nice
when a logical order is maintained.

Lars

 

[Tony Toews [MVP]]

I need to add 8 fields to a backend's table. I need to do this for 8
identically structured backends (in use by 8 different departments). Can I
write a routine that takes care of all these changes in one action. I'm
thinking of creating one mdb with vba code that updates all the backends.

A useful utility with quirks is Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lars Brownies]

Thanks. Work pretty well! Only downside is that the ordinal order is not
maintained like in the new mdb. Am I the only one who wants to keep that
order?

Lars

 

[Tony Toews [MVP]]

Thanks. Work pretty well! Only downside is that the ordinal order is not
maintained like in the new mdb. Am I the only one who wants to keep that
order?

I've seen it generate the OrdinalPosition property. (I just double
checked.) Maybe that's only in the pro version.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lars Brownies]

The ordinal property is also set in the light version but imo it's not
working properly, at least not in A2003. I think the reason is that the
ordinal position is only changed for that particular field. So if you want
to insert a field in a 3 field table between field0 and field1 by setting
its ordinal property to 1, it will result in the following ordinal
positions: 0,1,1,2. The order of the two 1's will be alphabetical. I have
found code that takes care of that, although I had to add the
Fd.OrdinalPosition = FldPos myself:

http://www.micronetservices.com/manage_remote_backend_access_database.htm#addfield

If Not IsMissing(FldPos) Then
Dim Num As Integer
For Num = 0 To FldPos - 1
Td.Fields(Num).OrdinalPosition = Num
Next
For Num = FldPos To .Fields.Count - 1
Td.Fields(Num).OrdinalPosition = Num + 1
Next
Fd.OrdinalPosition = FldPos
End If

Lars

 

[Tony Toews [MVP]]

The ordinal property is also set in the light version but imo it's not
working properly, at least not in A2003. I think the reason is that the
ordinal position is only changed for that particular field. So if you want
to insert a field in a 3 field table between field0 and field1 by setting
its ordinal property to 1, it will result in the following ordinal
positions: 0,1,1,2. The order of the two 1's will be alphabetical. I have
found code that takes care of that, although I had to add the
Fd.OrdinalPosition = FldPos myself:

http://www.micronetservices.com/manage_remote_backend_access_database.htm#addfield

If Not IsMissing(FldPos) Then
Dim Num As Integer
For Num = 0 To FldPos - 1
Td.Fields(Num).OrdinalPosition = Num
Next
For Num = FldPos To .Fields.Count - 1
Td.Fields(Num).OrdinalPosition = Num + 1
Next
Fd.OrdinalPosition = FldPos
End If

Ah, I see your point. Makes a lot of sense. And I hadn't thought of
that.

However I never add fields to the middle of a table once I've given
the first release of the app to the users. The problem is that
Access will cache certain information about the tables which leads to
the very puzzling -1517 error
http://www.granite.ab.ca/access/reservederror1517.htm

When I do a search on this error message in the Microsoft newsgroups I
see very few hits from 2005 and newer. So very likely a Microsoft
service pack fixed this problem. Either that or more people were
doing searches at Google and finding my page on this problem. <smile>

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lars Brownies]

Who knows? Thanks for the pointer. Appending rather than inserting makes
the whole process a lot easier. That being said: I've not come across
the -1517 error, though I have inserted many fields in between other fields
the last year (A2003). My backend is compacted every morning on first start
up.

Lars