Foreign Key?

[austenr]

Can you set a foreign key in an access table? It was my understanding you could not. If anyone can shed some light on this for me that would be a help. Thanks

[Ron Coderre]

A foreign key is one or more fields on one table that are the primary key on another table. The foreign key is typically the join field for the two tables. How were you thinking of "setting" the foreign key?

[austenr]

Well, I am wanting to set a relationship in/on the table. The client wants a drop down to pick from as a foreign key. Guess I just don't understand that concept. They want to be able to select from a drop down and have some sort of query ran. ????

[Ron Coderre]

Hmmmm.....I'm feeling a bit like a mushroom, here.

Can you explain what you're starting with and what you want to do?

I suspect you're thinking of having a form with a dropdown list. That dropdown list will contain the descriptive values from a dimension table. When the user selects one of those values.....that will trigger a query joining the fact table to the dimension table.

Data Example:
Dropdown contains a list of Employee Names from the EmployeeName table (which contains EmpID and EmpName.

The query joins the EmployeeName table to the EmployeeData table (which contains EmpID, DeptID, JobID, etc) using the EmpID field.

Am I on the right track here?

[austenr]

Thats exactly it!!

[Ron Coderre]

Here's what I'd do....

Create a Main Form with a Sub Form

The Main Form contains the dropdown list.
The Sub Form contains the query results with a query that uses the dropdown selection as the parameter.

Does that get you pointed in the right direction?

[austenr]

Still lpst. A example would help. Thanks.

[stanl]

You can simulate 'setting' a foreign key, if you are building tables with either DAO/ADO and SQL, See - http://home.gci.net/~mike-noel/CompareEM-PRO/samplevba-sql.htm#Relations

If you have inherited a DB and relationships have not been defined (under Tools/Relationships) then one trick like is to run a short ADO sub that executes OpenSchema(adSchemaForeignKeys), and if that returns nothing
execute OpenSchema(4) [4=Table Column enumeration]. That creates a recordset that you can pop into an Excel worksheet and sort/filter to find which tables have common-fields.

Stan

[Ron Coderre]

Still lpst. A example would help. Thanks.

OK....Let's see if an example will help

Assumptions:
tblEmployeeName (EmpID, Name)
tblEmployeeData (EmpID, DeptID, JobID)

A form named: frmChooseName
That form has 1 combobox:
-Named: cboEmpName
-Based on tblEmployeeName
-AfterUpdate event code:


Private Sub cboEmpName_AfterUpdate()
DoCmd.OpenQuery QueryName:="qryDisplaySelectedNameData"
End Sub

That means whenever you choose a name from the dropdown list, the qryDisplaySelectedNameData query (described below) will run and display values for the selected name.

The qryDisplaySelectedNameData query
-Joins the tblEmployeeName table to the tblEmployeeData on the EmpID field
-the tblEmployeeData.EmpID criteria is: [Forms]![frmChooseName]![cboEmpName]
? So when frmChooseName is open....That value is available to the query.

Notes:
If you try to run that query when the form is NOT open, it will prompt you for the [Forms]![frmChooseName]![cboEmpName] value

If no combobox value is selected, the query returns no records.

Does that help?
(Post back if you have more questions.)

[austenr]

Attached is a layout of the tables in the data base. As you can see there are a lot of foreign keys. Also Something called a PK what ever that is. I guess I just dont understand the concept as my access skills suck. Any help would be appreciated.

[austenr]

Im totally lost here?? :confused: :confused4 :nervous:

[Ron Coderre]

PK should mean Primary Key.....and I would expect ALL of the tables to have one (or more) fields disignated as Primary Keys. That doesn't seem to be the case though.

For instance, the FACULTY table has only LastName, FirstName....which is fine until somebody changes their name. Then any other table joined to that table by the "old name" will lose the link. If it had an EmployeeID field, it could be the Primary Key for that table.

I'm still not really sure what you need to do, though.

[stanl]

I'm still not really sure what you need to do, though.

Ditto... perhaps all this talk about foreign keys is putting the cart before the horse. Most important is can the client [in non-computereze] describe what they are after. .02 Stan

[asingh]

Yes PK "does" mean primary key. [Should be set/mandate on all tables]

1. It can not be NULL
2. It can not be a duplicate in the same table.
3. It can also be a combination of one or more fields. If it is more than one field..then it can be called a Composite Key. Access I think allows a 11-way composite key..maximum.

If a Primary key is referenced in a query, and two tables are joined..say using a left outer join, and you SELECT fields from the left table..the Primary Key in the right table becomes the Foreign Key. It is called this..cause it resides in another table, so in literal terms is foreign.

Access gives the user the liberty [dont know why Microsoft did this]..to not set Primary Keys on tables. It lets you create table structures and NOT set a PK. Nightmare.........!

The reason you have so many foreign keys listed..cause they will be "referenced" as join fields in your queries.

regards,

asingh

[stanl]

Access gives the user the liberty [dont know why Microsoft did this]..to not set Primary Keys on tables.

Probably because of importing text,Excel and other file formats - IMHO: Access is not really a DB but more a frontend. However, Microsoft did set up a Autonumber to substitute as a primary key, and that would rank as the worst mistake they ever made... another .02 Stan

[austenr]

So if I go to the table in access how do you define the Foreign Key? We are talking about 2007.

[austenr]

I agree but the client wants it that way. So they can change the data w/o much trouble. This is asking for trouble IMHO. What if they change a PK or FK relationship. Then its just junk that wont work. This probably belongs in SQL, SQL Server or something that is a real DB which Access is not. However few realize that. Because of marketing Microsoft says it is but in "geek" terms it really isn't.

[austenr]

AAMOF what is happening right now is that a web page is using Excel as the feeder for the data. But we wont go there. These are a bunch of designers (Flash, ASP, JSP) and not VBA programmers or even VB programmers. They dont understand the ramifications and the problems you can run into.

[stanl]

So if I go to the table in access how do you define the Foreign Key? We are talking about 2007.

Had you looked at the link I posted, you use ALTER TABLE. Now if they want to be in charge of things, you use OpenSchema, create lookup tables based on comparable fields, let them chhose the relationships [probably with two listboxes] and make necessary modifications with SQL from there.

{then of course, your quietly explain this is what you are about to do, that it probably won't work and that it will cost twice as much, as say you just presenting a 'normalized' data structure}

... sorry but I have to move from programming to plumbing.... Ahhh! old toilets :banghead: Stan

[austenr]

Not that I dodnt look at it. Just dont know how to incorporate it into what they want to do.

[stanl]

Just dont know how to incorporate it into what they want to do.

That get's us back to what they want. My take on foreign keys is that they exist to ensure no orphan rows will exist in a child table as the foreign key is used to reference the primary key in a parent table.

The concept of changing foreign keys 'ad-hoc' doesn't make much sense in that context - so maybe it is just a pop quiz?

Clarify... are you at the design stage, or have tables already been created and there is a need for referential integrity? Either way, I think ADO is the best route. Normally foreign keys are indexed, but as of Access 2003 ADO includes a NO INDEX clause for foreign key constraints. Might appear counter-intuitive, but if the child table has a large amount of duplicate foreign keys, an index can actually slow down processing that involves serious deletes and updates.

Also IMHO, ADO's OpenSchema() method for a connection object is great tool for documentation. It is difficult to be more specific at this point without details about actual tables and fields.:dunno Stan

[austenr]

Yes, the tables have been created. However on PK of FK's have been selected mainly because I don't think they know what they want to do with this. From what I can gather, they want to be able to do "Queries on the fly". Say you have a table with Last name and First name. They want to be able to click a last name and execute a query to see all records that pertain to that person across the database in some sort of report. I guess it would be some sort of dynamic filter or something.

[asingh]

Hi,

Austenr, you cannot set a foreign key. You set primary key(s) on table. Which at time of executing a query, become a foreign key..or stay as a primary key..depending on the join type...!

In you requirement..who would be changing the PK...this would cause the foreign keys to also change..cause the change would replicate on all dependant object. It can be done in two ways:

1. Using the Standard Access GUI..go into the table design view and set the primary key..by selecting the field.
2. Use DDL [Data defination language] [ALTER TABLE....]...to modify the PK..either in the SQL Text area..or via the VBA environment.

Just remember if end users are doing this..it might get messy..specially if relationships have been set up..!

regards,

asingh

[asingh]

Hi,

If they just want to create queries on the fly..and they are not transactional..just plain simple select...then..the best method would be for you to set all the constraints/relationships..and let them play around with the queries. End users changing table constraints...."try to make them understand"....its not feasible...!

regards,

asingh

[stanl]

Austenr, you cannot set a foreign key.

Not completely TRUE... you can set Constraints which happen to be foreign keys, useful for cascading updates and deletes... but that becomes philosophical and a moot point. But your also said


the best method would be for you to set all the constraints/relationships

and that is both sensible and logical and why I suggested using OpenSchema()... this allows you to create Meta-Tables {tables about tables}. So assuming the main database was Faculty with a PK of FacultyID. You could relate FacultyID to all other tables that contain that column {whether an FK is defined or not}, then any other column in the main table could be used to search for data in the related tables. Again assuming FacultyID appears in

Faculty - main table
Classes -
Publications -
GradStudents -
OfficeHours -
etc...

You could set up a form with a drop-down based on all columns in Faculty, except FacultyID.

If they select LastName and enter Plotznik and that is a unique last name in the faculty, they can return results from all other tab les related to the Plotznik FacultyID, but they could also search Smith and there may be 7 Smiths on the faculty but the query could still sort the related data.

So, austenr, if this is what they are after, whter or not Foreign Keys are establsihed or not, you can, as Asingh said, perform the queries. However, if the queries are Transactional, viz. Plotznik leaves, dies, or is fired.. and you want to archive then delete all related info, then FK's are probably preferred.

I just finished a script for a friend that uses ADOX to create an Access 2007 DB, create table and key structures and migrate data from Office 2003 - using only the ACE Provider. As I did this for nothing, I can post the ADOX code should you want to see the FK portions. Stan

[austenr]

Stan,

The code would be helpful to me.