Solved

Using SQL 2005 tables in an Access 2003 database

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 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