Changing field order in a table with VBA

[pascal]

Hi,

I added three new fields in an existing table with VBA. These new fields where all added at the end of the table (after the other fields). Is it possible to add them (or move them) at the beginning or inbetween two other fields with VBA?
If so, how can this be done.

Thanks already.

 

[KenHigg]

Is this a one time deal were you have added some flds and would like them re-arranged, or do you plan on adding them on a regular basis?

kh

(Got any 'Belgium' pictures you can post? Maybe your home town? Never been there )

 

[pascal]

Thanks Ken for your quick reply. To answer your question: it's a one time deal.

 

[WayneRyan]

pascal,

You can get your table in Design View and "Drag" a field to a new location.

BUT, you really don't want to refer to your fields positionally, they have
names. Any code that you have should really reference them by name.

Wayne

 

[KenHigg]

Then you should be able to just click and drag 'em where you want.

(You may want to experiment on a test table first )

kh

 

[KenHigg]

BUT, you really don't want to refer to your fields positionally, they have
names. Any code that you have should really reference them by name.

True enough, but there can be some advantages to having flds in a certain order in a table (In my humble opinion )

kh

 

[MarionD]

Hi there,

I do this on a regular basis as a way of updating my backend version.
This is the code I use:

Set td = db.TableDefs("tbl_test")

With td
.Fields.Append .CreateField("Testfield", dbText, 50)
.Fields![Testfield].OrdinalPosition = 4
.Fields![Testfield].Required = False
.Fields![Testfield].AllowZeroLength = True
.Fields.Refresh
End With

Hope this helps a bit
Marion

 

[pascal]

Thanks Marion for your reply. This is exactly what I was looking for. But still a small problem. When I tried your code (td.Fields![MyField].OrdinalPosition = 1) in order to make my new added field the first field in my table I found out that my new added field was still not the first but it did move to another place (third column) in my table.
Can you help me further with this Marion?

Thanks already.

Pascal

 

[KenHigg]

You may have to set it to zero.

???
kh

 

[pascal]

Already tried Ken but still not the first field.

 

[KenHigg]

Sorry...

I'm missing something here, if this is a one time deal. why not just click and drag it?

kh

 

[pascal]

Ken,

I think I misunderstood your first question yesterday and answered it with only one time but I plan on adding them on a regular basis.
Sorry for this stupid mistake I made.

Pascal

 

[MarionD]

Hi Pascal ,

Sorry took a while I was out yesterday.

When you say position 1, do you mean you are trying to make this field your primary key? If so, you have to add the index as well

Set idx = .CreateIndex("IDIX")
With idx
.Fields.Append .CreateField("testfield")
.Primary = True
End With
.Indexes.Append idx
.Indexes.Refresh

this normally doesn't have anything to do with the ordinal position as your primary kay must not neccessarily be in position 1, but I cannot think of any other reason why ordinal position should not work. Perhaps you'd like to post your code here so we can have a look.

Grüße aus Deutschland
Marion

 

[KenHigg]

I really don't mean to be a pain, but something tells me your db design may be in need of re-look. Would you mind sharing why you would need to add flds to a table on a regular basis?

kh

 

[MarionD]

Hi Ken,

I have a Front end and Back End DB. I constantly Develope my Front End DB, taking into account customer wishes, new features, improvements.... sometimes I need to change/ add fields to the BAckend DB to "accomadate" the changes in the FrontEnd. I have a Version check in a local table in the front end, and a version check in the Back-End DB. This way, I only dispatch a new FE - when they logon I check if the Data Version matches the FE Version, if not I run a module that automatically adds/changes the Backend Tables and updates the Version Number accordingly. Thus if I have a version say 5.01 - Any changes made to the front-end only add 5.02, 5.03 - as soon as a change is made to Data structure the version changes to 6.01

This is only my way of doing things.

 

[KenHigg]

MarionD - Cool. Never tried this. I may give it whirl sometime - I may have to look you up for advice if I do...

Ken

 

[MarionD]

Please do !

 

[pascal]

Hello again Marion,

To answer your question: my table doesn't have a primary key and I don't want to make one of the new fields the have the primary key.

Here's my code:

Function VeldenToevoegen()

Dim db As Database
Dim td As TableDef
Dim Jaartal As Field
Dim Maandtal As Field
Dim Bedrijf As Field
Dim Toekenning As Field
Dim Veld As Field

Set db = CurrentDb
Set td = db.TableDefs("Gegevens")

For Each Veld In td.Fields
If Veld.Name = "Year" Or Veld.Name = "Month" Or Veld.Name = "Branch" Or Veld.Name = "Assignment" Then
Set Veld = Nothing
Exit Function
End If
Next

Set Jaartal = td.CreateField("Year", dbText)
Set Maandtal = td.CreateField("Month", dbText)
Set Bedrijf = td.CreateField("Branch", dbText)
Set Toekenning = td.CreateField("Assignment", dbText)

td.Fields.Append Jaartal
td.Fields.Append Maandtal
td.Fields.Append Bedrijf
td.Fields.Append Toekenning

td.Fields![Year].OrdinalPosition = 1
td.Fields![Month].OrdinalPosition = 2
td.Fields![Branch].OrdinalPosition = 3

Set td = Nothing
Set db = Nothing

End Function

Thanks already.

Greetings,

Pascal

 

[MarionD]

Hi Pascal,

I changes the code a bit - I tested it and works fine
Notes: It's not a good idea to name a field Month or Year as these are reserved word used for function in Access (e.g. Year(datefield))

You have to set the Attributes of the field before appending it.
I just made them all text fields. If they have to be Number, Currency, date fields just change the dbtext to dbLong or DbCurrency etc.


Function VeldenToevoegen()

Dim db As DAO.Database
Dim td As TableDef
Dim Veld As Field

Set db = CurrentDb
Set td = db.TableDefs("Gegevens")

For Each Veld In td.Fields ' not quite sure why this?
If Veld.Name = "Year" Or Veld.Name = "Month" Or Veld.Name = "Branch" Or Veld.Name = "Assignment" Then
Set Veld = Nothing
Exit Function
End If
Next
'***********
Set td = db.TableDefs("Gegevens")

Set Veld = td.CreateField("Jaartal", dbText, 50) 'it's not a good idea to name a field Year or Month - these are reserved and names of functions in access!!
Veld.OrdinalPosition = 1
Veld.AllowZeroLength = True ' you don't have to set these properties - they take the standard if you don't
Veld.Required = False
td.Fields.Append Veld
Set Veld = td.CreateField("Maandtal", dbText, 50) 'it's not a good idea to name a field Year or Month - these are reserved and names of functions in access!!
Veld.OrdinalPosition = 2
Veld.AllowZeroLength = True
Veld.Required = False
td.Fields.Append Veld
Set Veld = td.CreateField("Bedrijf", dbText, 50)
Veld.OrdinalPosition = 3
Veld.AllowZeroLength = True
Veld.Required = False
td.Fields.Append Veld
Set Veld = td.CreateField("Toekenning", dbText, 50)
Veld.OrdinalPosition = 4
Veld.AllowZeroLength = True
Veld.Required = False
td.Fields.Append Veld

td.Fields.Refresh
'**************************


Set td = Nothing
Set db = Nothing

End Function

 

[pascal]

Hello again Marion,

Thanks once again but still my added fields aren't in the right place. I've changed my code following yours, here it is:

Function VeldenToevoegen()

Dim db As Database
Dim td As TableDef
Dim Veld As Field

Set db = CurrentDb
Set td = db.TableDefs("Gegevens")

Set Veld = td.CreateField("vYear", dbText)
Veld.OrdinalPosition = 0
td.Fields.Append Veld

Set Veld = td.CreateField("vMonth", dbText)
Veld.OrdinalPosition = 1
td.Fields.Append Veld

Set Veld = td.CreateField("Branch", dbText)
Veld.OrdinalPosition = 2
td.Fields.Append Veld

Set Veld = td.CreateField("Assignment", dbText)
Veld.OrdinalPosition = 8
td.Fields.Append Veld

Set Veld = Nothing
Set td = Nothing
Set db = Nothing

End Function

************************

The following part was only to find out if the fields weren't already there. If yes the function must not be done. That's why.

For Each Veld In td.Fields ' not quite sure why this?
If Veld.Name = "Year" Or Veld.Name = "Month" Or Veld.Name = "Branch" Or Veld.Name = "Assignment" Then
Set Veld = Nothing
Exit Function
End If
Next

*************************

Why adding td.fields.refresh at the end of your code?
Is this needed?

Greetings,

Pascal