Linked excel file and use it in a relation

H

hucktx

I have a database that I would like to link a excel file to, but when I link
the file and try to use it in a relation to an existing table I can only get
it to do a indeterminate relations not a one to many. I tried to set a
primary key but the excel file is not editable. Is this normal or is their
something wrong.
 
J

John Vinson

I have a database that I would like to link a excel file to, but when I link
the file and try to use it in a relation to an existing table I can only get
it to do a indeterminate relations not a one to many. I tried to set a
primary key but the excel file is not editable. Is this normal or is their
something wrong.
Click to expand...

This is normal. Excel spreadsheets do not have Primary Keys or
enforced indexes; there is no way that Access can determine the
cardinality of the relationship, or enforce relationships. To do so
you must import the spreadsheet data, not link it.

John W. Vinson[MVP]
 
H

hucktx

thanks
can this be done
can i fill a non linked table with a linked table?
i would like the excel table to fill a table so when the excel file changed
the table in acces would reflect the changes.

im using a feild in the excel file (job NO) in every table in my database
 
J

John Vinson

thanks
can this be done
can i fill a non linked table with a linked table?
i would like the excel table to fill a table so when the excel file changed
the table in acces would reflect the changes.
Click to expand...

A linked spreadsheet will act pretty much like a Table; you can create
queries joining the spreadsheet to Access tables, base Forms and
Reports on it, and so on. What you cannot do is enforce Referential
Integrity - that is, there is no way for Access to require that the
ID's in the spreadsheet must match those in a table.

You cannot - and should not - store the same data in Excel and in
Access; and you certainly cannot have such redundant data
automagically updated when the spreadsheet is changed.

John W. Vinson[MVP]
 
H

hucktx

thank for the help (first try at access)
I will continue handling it this way. I’m using a combox to bring the job
No. I need in to a table called Job No. and then my other tables relate it
 
Top