Solved

Using SQL 2005 tables in an Access 2003 database

Posted on 2007-03-27
5
209 Views
Last Modified: 2013-11-28
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
Comment
Question by:mattirons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18800134
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
 

Author Comment

by:mattirons
ID: 18800262
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18800473
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
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18800500
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
 

Author Comment

by:mattirons
ID: 18800740
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question