Solved

Using SQL 2005 tables in an Access 2003 database

Posted on 2007-03-27
5
210 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

717 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