Using SQL 2005 tables in an Access 2003 database

I have just started to develop and internal app and have across a problem when using linked SQL 2005 tables.
The app enables uses to enter company and contact details (so far), the companies get entered onto the main form and the contacts through a sub form as there are multiple contacts at most sites. When I use local access tables it works perfectly i.e. when you add a new contact it populates the companyid into the companyid field in the contacts table. However I would like to store the data in SQL 2005 on the main server, but when I try to run the app using the linked SQL tables the relationships don;t seem to work! i.e. it doesn't populate the companyid field in the contacts table! When I look at the relationships there don't seem to be as many options in the join type properties, so I'm not sure if this is where the fault lies or if I've missed any basic rules or something. I'm new both Access 2003 and SQL 2005, so that doesn;t help and I just can;t crack this one! Any help would be gratefully accepted.

Regards
Matt*
mattironsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
It sounds like you have a primary key - foreign key relationship between the company and contact table. If this is the case you can turn on cascading updates for the relationship in SQL Server. With this turned on as you enter the companyid column in the company it will be also entered into the companyid in the contacts table. This assumes that there is a pk-fk relationship between the 2 tables.
0
mattironsAuthor Commented:
Ok, so lets see if I've got this (I'm new to SQL too). I have to create the relationship in the SQL database, with a primary - foreign key set between tblcompanies and tblcontacts using the companyid field.

I then have to turn on cascading updates? Just out of interest is this a global setting or do I have to edit/amend each table?
0
rboyd56Commented:
It is set at the table level.

Check out this topic in Books on Line:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm
0
rboyd56Commented:
Actually, I may have given you some invalid information. It appears that only deletes and updates cascade. Inserts do not. Because of this you will need to create a trigger on the tblcompanies table to handle the insert into the tblcontacts.

Check out this topic on how to create a trigger:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/edeced03-decd-44c3-8c74-2c02f801d3e7.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mattironsAuthor Commented:
Hi thanks for these comments, it looks as though your solution is correct.

I'll have to hit the SQL books I think before I do this one!

Thanks all the same and I'll award the points for this one.

Cheers

Matt*
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.