[AccessD] Updating queries with linked tables

Roz Clarke roz.clarke at donnslaw.co.uk
Tue Apr 1 09:06:29 CST 2003


Actually it is pretty different, though 100 tables is a bit of an
exaggeration. There's a lot of fiddly stuff to do with definitions of work
teams, and stages in legal training - let's not go there, eh? 

I have implemented the tab control in the most urgently needed app, and
tabled the major re-vamp for future reference.

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


Roz,

I doubt that all 100 tables have unique table definitions.  Since they are
all storing local data about personnel, I would hazard a guess that 95% or
more of the data each is collecting is similar and can go in one table.
With SS Security, as I have already suggested, it is no problem to have many
departments store their data in one table and keep it secure.

But you are correct in that Lambert's tab idea will be quicker for you to
implement.  Good luck in getting it all done by yesterday. <grin>

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: Tuesday 2003 Apr 01 03:11
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Updating queries with linked tables

Charles

You're right, it would work. I'm reluctant to clutter the server with lots
of little bitty tables though, (like, 100) and to transfer them all would be
a major commitment when, of course, they need this fixing yesterday...

Lambert's tab form thingy might work kinda, and I guess I could also unbind
some of the data without having to unbind the whole lot.

Thanks for your help

Roz

-----Original Message-----
From: Wortz, Charles [mailto:CWortz at tea.state.tx.us] 
Sent: 31-Mar-2003 17:26
To: accessd at databaseadvisors.com
Subject: RE: [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

-----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 
 
-----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 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list