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

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
"Lars Brownies" <(E-Mail Removed)> wrote in message
news:grlvt5$1dme$(E-Mail Removed)...
>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
>

Thanks!

Lars

"Arvin Meyer MVP" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> 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
> "Lars Brownies" <(E-Mail Removed)> wrote in message
> news:grlvt5$1dme$(E-Mail Removed)...
>>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
>>
>
>

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/f...ts.asp?TID=410

HTH

Peter Hibbs.


On Fri, 10 Apr 2009 01:21:09 +0200, "Lars Brownies"
<(E-Mail Removed)> wrote:

>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
>

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" <(E-Mail Removed)_SPAM> schreef in bericht
news:(E-Mail Removed)...
> 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/f...ts.asp?TID=410
>
> HTH
>
> Peter Hibbs.
>
>
> On Fri, 10 Apr 2009 01:21:09 +0200, "Lars Brownies"
> <(E-Mail Removed)> wrote:
>
>>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
>>

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.

On Fri, 10 Apr 2009 11:50:28 +0200, "Lars Brownies"
<(E-Mail Removed)> wrote:

>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" <(E-Mail Removed)_SPAM> schreef in bericht
>news:(E-Mail Removed)...
>> 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/f...ts.asp?TID=410
>>
>> HTH
>>
>> Peter Hibbs.
>>
>>
>> On Fri, 10 Apr 2009 01:21:09 +0200, "Lars Brownies"
>> <(E-Mail Removed)> wrote:
>>
>>>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
>>>
>

Thanks.

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

Lars

"Peter Hibbs" <(E-Mail Removed)_SPAM> schreef in bericht
news:(E-Mail Removed)...
> 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.
>
> On Fri, 10 Apr 2009 11:50:28 +0200, "Lars Brownies"
> <(E-Mail Removed)> wrote:
>
>>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" <(E-Mail Removed)_SPAM> schreef in bericht
>>news:(E-Mail Removed)...
>>> 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/f...ts.asp?TID=410
>>>
>>> HTH
>>>
>>> Peter Hibbs.
>>>
>>>
>>> On Fri, 10 Apr 2009 01:21:09 +0200, "Lars Brownies"
>>> <(E-Mail Removed)> wrote:
>>>
>>>>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
>>>>
>>

"Lars Brownies" <(E-Mail Removed)> wrote:

>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/Compa.../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/

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]" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> "Lars Brownies" <(E-Mail Removed)> wrote:
>
>>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/Compa.../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" <(E-Mail Removed)> wrote:

>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/