Add new records in a query?

  • Thread starter Thread starter Slez via AccessMonster.com
  • Start date Start date
S

Slez via AccessMonster.com

How does Access determine whether or not a query will allow you to add new
records? I have a query based on 3 tables...perhaps my tables are not
properly laid out, but I wanted to ask this quick question first.
Thanks
 
This can be caused by serveral different circumstances when you have multiple
tables used in a query. One is that the query causes there to be multiple
rows for the table you want to modify. Another is the fields used to join
the tables are not indexed.
 
I believe you can only add a new record to a query based on one table.
You cannot add (or modify) records in a query based on two or more
related tables.

Thanks,
JK
 
Do a search on Updatable Query in Access Help... it describes the
circumstances under which you can update.
 
I believe you can only add a new record to a query based on one table.
You cannot add (or modify) records in a query based on two or more
related tables.
Click to expand...

That's often *but not always* the case. Two or even multi-table
queries can be updateable, but there are restrictions. See
"Updateable" in the Help for details.

John W. Vinson[MVP]
 
JK,
Actually, queries based on 2 tables are quite often updatable. Queries on 3
or more tables are updatable under certain circumstances.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


JK said:
I believe you can only add a new record to a query based on one table.
You cannot add (or modify) records in a query based on two or more
related tables.

Thanks,
JK
Click to expand...
 
Now that I've read the Help Menu on it, I see how that would work. I
rarely have databases with a one-to-one table structure though, and am
usually opposed to cascading updates/deletes.

Thanks for the tip!



Lynn said:
JK,
Actually, queries based on 2 tables are quite often updatable. Queries on 3
or more tables are updatable under certain circumstances.
Click to expand...
 
Now that I've read the Help Menu on it, I see how that would work. I
rarely have databases with a one-to-one table structure though, and am
usually opposed to cascading updates/deletes.
Click to expand...

Why are you opposed to cascading updates and deletes?
 
How does Access determine whether or not a query will allow you to add new
records?
Click to expand...

In the Immediate Window:

? CurrentProject.Connection.OpenSchema(adSchemaViews, Array(Empty,
Empty, "<<Query name here>>")).Fields("IS_UPDATABLE").Value

Jamie.

--
 
Why are you opposed to cascading updates and deletes?
Click to expand...

I'm not the OP but I've often pondered the same.

Immutability in a key important but not essential. Even so, many people
are 'phobic' about a key value *ever* changing and so seek to use a
'surrogate'. This is not so terribly bad if they also constrain the
natural key and candidate keys (simply adding an autonumber and making
it the PRIMARY KEY means no data integrity, of course). Such
practitioners don't want their 'surrogate' values to change, except
under 'managed' conditions, so making the referential action ON UPDATE
NO ACTION is the preference.

Avoiding ON DELETE CASCADE is harder to account for. I get the
impression some people are a bit 'phobic' when it comes to the concept
of 'an automatic loss of data controlled by the system'. In Access/Jet
SQL, where you can only execute one SQL statement per procedure, ON
DELETE CASCADE is a particularly important feature.

Jamie.

--
 
Jamie's response is ultimately the same as mine. At the end of the day
I think it comes down to simply wanting to clearly code what is
happening, and not rely on Jet to do something so consequential as
deleting records.

The other thing I'd add is that when you do not cascade deletes, it
forces you to write code to do this. Although it takes time, it
clearly shows what records are being deleted and from what tables.
This, in turn, forces people to think about the consequences of
deleting data with related records, instead of realizing later-on that
they lost information in another table unintentionally.

Thanks,
JK
 
Back
Top