By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Updating Linked Records Across DB's

P: n/a
Here's a problem that I can't find anyone else has run into. I'm using
Access and SQL Server, but the theory would be the same for any db.

I have a large number of tables that contain linked records
(intersection tables mostly). In the interest of space, I'll
illustrate an example:
tblStudents (ID, Name)
tblTeachers (ID, Name)
tblClasses (ID, Name)
tblEnroll (StudentID,ClassID,TeacherID)
I have about 10 people who each use a separate
copy of this database (in access). I want them (at the end of each
day) to be able
to update all the records that they entered that day into a database
that I have setup on a server (SQL Server 2005). Both databases have
the exact same structure.
Caveat 1: All of the classes, students, and teachers are not the same
on each database, but the server database should contain all of them.
Caveat 2: There is no way for the clients to automatically insert into
the server, they are offsite and out of range.
Herein lies the problem: when a record is inserted into the server from

a client, all of the links are lost since the ID will be different on
the server that it was on the client.
I don't think a simple update / insert query will work, and most db's
and languages don't play nice with recordset appends.

What are your thoughts??

Mar 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(jo*****@ci.green-bay.wi.us) writes:
Here's a problem that I can't find anyone else has run into. I'm using
Access and SQL Server, but the theory would be the same for any db.

I have a large number of tables that contain linked records
(intersection tables mostly). In the interest of space, I'll
illustrate an example:
tblStudents (ID, Name)
tblTeachers (ID, Name)
tblClasses (ID, Name)
tblEnroll (StudentID,ClassID,TeacherID)
I have about 10 people who each use a separate
copy of this database (in access). I want them (at the end of each
day) to be able
to update all the records that they entered that day into a database
that I have setup on a server (SQL Server 2005). Both databases have
the exact same structure.


Sounds like you investigate replication. This is not my area, so I
can't give any details, even less any that includes Access. You might
want to ask in microsoft.public.sqlserver.replication for more details.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.