Jim Lawrence (AccessD)
accessd at shaw.ca
Mon Mar 31 12:46:37 CST 2003
Hi Roz: I have worked in a similar environment, a combination of Access and SQL backends. I had to use unbound forms, ADO connections strings. The recordsets that supplied the information to the forms were bound or unbound depending on their requirement. For straight viewing CursorType: adOpenStatic and LockType: adLockReadOnly, generally displayed in List format. For adding and updating CursorType: adOpenDynamic and LockType: adLockPessimistic, generally displayed in Form format because there is a single or small subset of records. You can easily update both or either data sources by changing or swapping the connection strings. This process requires a few more steps but you get the added flexibility and performance. HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Roz Clarke Sent: Monday, March 31, 2003 8:01 AM 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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com