By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Import table Fields definitions from another table

P: 10
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
Dec 25 '06 #1
Share this Question
Share on Google+
10 Replies


PEB
Expert 100+
P: 1,418
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?
Dec 26 '06 #2

P: 10
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
Dec 26 '06 #3

PEB
Expert 100+
P: 1,418
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!
Dec 26 '06 #4

P: 10
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!!!
Dec 26 '06 #5

Expert 5K+
P: 8,434
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.
Dec 26 '06 #6

P: 10
Yes, that's exactly what I'm looking for!
Dec 27 '06 #7

NeoPa
Expert Mod 15k+
P: 31,661
If it's just the Description of the field you're looking for then it can be found by referencing :
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 28 '06 #8

Expert 5K+
P: 8,434
If it's just the Description of the field you're looking for then it can be found by referencing :
Expand|Select|Wrap|Line Numbers
  1. 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)...
Expand|Select|Wrap|Line Numbers
  1. Dim fld As Field
  2. For Each fld In CurrentDb.TableDefs("Positions").Fields
  3.   Go look up fld.Name in the "Definitions" table
  4.   fld.Description = text found in "Definitions"
  5.   ' Do I need to update fld, or is that automatic?
  6. Next
  7.  
Dec 28 '06 #9

NeoPa
Expert Mod 15k+
P: 31,661
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)...
Expand|Select|Wrap|Line Numbers
  1. Dim fld As Field
  2. For Each fld In CurrentDb.TableDefs("Positions").Fields
  3.   Go look up fld.Name in the "Definitions" table
  4.   fld.Description = text found in "Definitions"
  5.   ' Do I need to update fld, or is that automatic?
  6. Next
  7.  
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.
Dec 28 '06 #10

Expert 5K+
P: 8,434
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.
Dec 28 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.