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

Can't update a table via a 'Union Query'!!!

P: 8
Hi good people of the Access world,

I have a two tables, i have a form, the forms recordsource is a 'union query', however this does not seem to allow me to update the table via the form, does anyone have any ideas....??? is this something that happens when you use union queries....??

Cheers....
Aug 9 '07 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
Sorry, Union queries are always read-only!

Welcome to TheScripts!

Linq ;0)>
Aug 9 '07 #2

P: 8
Thanks for the reply... is there anyway around this, at all...??
Aug 9 '07 #3

Scott Price
Expert 100+
P: 1,384
Thanks for the reply... is there anyway around this, at all...??
You can use your union query as part of a separate select query that chooses the records that need to be updateable. Base your form on this second (non-union) query.

Regards,
Scott
Aug 9 '07 #4

FishVal
Expert 2.5K+
P: 2,653
You can use your union query as part of a separate select query that chooses the records that need to be updateable. Base your form on this second (non-union) query.

Regards,
Scott
Sorry, Scott, but any query based on not updatable query will be not updatable itself.
Aug 9 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Thanks for the reply... is there anyway around this, at all...??
The only way around, I think, is to merge tables physically.

There is now reason to store data in separate tables to distinguish London from Bristol. Just add to both'em field (let us say txtCity (Type=Text)), update it in tbl_london to "London" and in tbl_bristol to "Bristol", then merge both'em.

Now to normalize your db you'll need to create additional table (let us say tblCities) with single field (txtCity (Type=Text)) which will be the Primary Key, add two records "London" and "Bristol" and then make PK/FK relationship between tblCities and the merged table (tblCities.txtCity - PK, tbl_mergedtable.txtCity - FK).
Aug 9 '07 #6

Scott Price
Expert 100+
P: 1,384
Sorry, Scott, but any query based on not updatable query will be not updatable itself.
Good catch, FishVal... I guess I've never tried to use a union query for anything but data display.

Regards,
Scott
Aug 10 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.