Import table Fields definitions from another table

[Forest14]

Hello! Happy Christmas/holidays to you all

I have this huge table named "Positions" with more than 160 fields of which the fields are named with non obvious abbreviations.
I have another table named "definitions" which has the column name and definition fields.

In order to help me all along my forthcoming developments, I would like to use these definitions from table "definitions" into "Positions". Of course i can manually copy each definition, one by one but there is a risk of error and also, the table "Positions" might be updated by the party which delivers it to us... so automatic might be better over the long term in this case.

What are the automatic options?
- Import once?
- Is it possible to link the description field of "Positions" with the "Definitions" table?

Thank you in advance for your input guys.
Best regards

[PEB]

So i n the table definitions u have other fields out of those 160 in Positions?
and u want to add those fileds from definitions to Positions...

If they a large size those fields u can have a pb with Access! There can't be more than 255 fields into an Access table! And can tell u that if u have more than 220 fields u will have problems...

So i don't think as a good idea to import more fields in Positions.

So my suggestion...

Create tirth table and in this table create 3 fields:

ID_Position, ID_definition and Value

In this structure u can introduce info for each position and definition that u have!

Is this helpful?

[Forest14]

Table "positions" is made of 160 fields but there are no field definitions
I am looking to add automatically the definitions of these fields by importing them from table "defintions"
Table definitions includes the following fields:
"Field" which actually matches the "positions" table fields names.
and "definition"

THank you

[PEB]

Table "positions" is made of 160 fields but there are no field definitions
Table definitions includes the following fields:
"Field" which actually matches the "positions" table fields names.
and "definition"

THank you

What do u mean under field definition? Validation Rules, Keys, Indexes, Validation text, Size? All of those properties of each field can be considered as Field definition! So tell me what property of the field u want to update automatically?
Principally programatically can be done while the field is created! When the field is created i'll check TableDef properties in Access help for more info!

[Forest14]

I mean Field description which is displayed in the status bar when this field is selected. Sorry for misunderstanding.

I was thinking about another mean of getting to the point:
for example: a text popup showing when hovering over the field on table or form view.

thanks!!!

[Killer42]

I mean Field description which is displayed in the status bar when this field is selected. Sorry for misunderstanding.

I was thinking about another mean of getting to the point:
for example: a text popup showing when hovering over the field on table or form view.

thanks!!!

So would you say that what you want to do is automate the process of loading a bunch of text entries from one table ("definitions") into the descriptions for the corresponding fields in the table "Positions"?

This doesn't seem as though it would be excessively difficult. I'm sure that with all the Access experts here we can work something out.

[Forest14]

Yes, that's exactly what I'm looking for!

[NeoPa]

If it's just the Description of the field you're looking for then it can be found by referencing :

CurrentDb.TableDefs("TableName").Fields("FieldName").Properties("Description")

You would have to cycle through the fields (Fields can also be referenced via their number) and update each one with the next item from the Definitions table.

[Killer42]

If it's just the Description of the field you're looking for then it can be found by referencing :

CurrentDb.TableDefs("TableName").Fields("FieldName").Properties("Description")

You would have to cycle through the fields (Fields can also be referenced via their number) and update each one with the next item from the Definitions table.

So NeoPa, how would I go about referring to the Description property in a field definition object? I seem to recall that I've had problems with this in the past.

As I see it, the actual process required will be quite simple. Something along these lines (just rough pseudo-code, the syntax will be all wrong)...

 Dim fld As Field
For Each fld In CurrentDb.TableDefs("Positions").Fields
  Go look up fld.Name in the "Definitions" table
  fld.Description = text found in "Definitions"
  ' Do I need to update fld, or is that automatic?
 Next
 

[NeoPa]

So NeoPa, how would I go about referring to the Description property in a field definition object? I seem to recall that I've had problems with this in the past.

As I see it, the actual process required will be quite simple. Something along these lines (just rough pseudo-code, the syntax will be all wrong)...

 Dim fld As Field
For Each fld In CurrentDb.TableDefs("Positions").Fields
  Go look up fld.Name in the "Definitions" table
  fld.Description = text found in "Definitions"
  ' Do I need to update fld, or is that automatic?
 Next
 

That's about the size of it.
It is a property of an object (if that helps) rather than data within a record so no .Edit / .Update required.
You may need a For IntegerVar type construct instead though, as the records in the other table would be likely to be positionally dependant.

HTH -Ade.

[Killer42]

That's about the size of it.
It is a property of an object (if that helps) rather than data within a record so no .Edit / .Update required.
You may need a For IntegerVar type construct instead though, as the records in the other table would be likely to be positionally dependant.

I'll have to await confirmation (or refutation) from the OP, but I think they are linked by the field name, which I expect would be a foreign key on Definitions table.