[AccessD] Updating queries with linked tables

Roz,

I admit that I have never tried updating both Access and SQL Server
tables at the same time so I have not run into your problem.  But one
solution that will work is to move the local tables to the BE, use SQL
Server Security to protect the data, and convert the MDBs to ADPs.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: Roz Clarke [mailto:roz.clarke at donnslaw.co.uk] 
Sent: Monday 2003 Mar 31 10:01
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Updating queries with linked tables

Charles

Sorry for the vague. I am still a little wiped out from the weekend.

Access XP, SQL 7. It's a 1:1 relationship - how do I tell it that?! It's
not an update query as such, just a normal select statement behind a
form.

Thanks

Roz

-----Original Message-----
From: Wortz, Charles [mailto:CWortz at tea.state.tx.us] 
Sent: 31 March 2003 16:43
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Updating queries with linked tables


Roz,

Are you saying you have an Access db (version?) with a local table and a
link to a table in SQL Server (version?) and you are trying to update
both in one update query?  If so, remember that the rules of updating
generally do not allow the updating of the one side of a one-to-many
relationship.  If you do have a one-to-many relationship you need to use
two update queries, one for each side.


Charles Wortz 
Software Development Division 
Texas Education Agency 
1701 N. Congress Ave 
Austin, TX 78701-1494 
512-463-9493 
CWortz at tea.state.tx.us 
-----Original Message-----
From: Roz Clarke [mailto:roz.clarke at donnslaw.co.uk] 
Sent: Monday 2003 Mar 31 09:33
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Updating queries with linked tables

Dear all 
I have a SQL database which stores core information about members of
staff here - name, department, phone extn. etc. 
I need to publish this information out to several different departments,
all of whom need to be able to update the data. At the same time, they
each have local data requirements which have to be updated alongside the
core data. I was going to put these in Access databases and give each
department it's own. What I'm finding (and I can't believe I never hit
this before) is that although the SQL tables can be updated through the
Access FE, and the local tables can be updated, combine them and wallop,
not updateable. Even if all the key fields are included in the query.
The amount of info is about 50/50 and it would be highly user-unfriendly
to split the data onto separate forms. Is unbound the only way
forward??? TIA 
Roz