• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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*
0
mattirons
Asked:
mattirons
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now