Add new records in a query?

  • Thread starter Slez via AccessMonster.com
  • 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
 
K

Klatuu

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.
 
J

JK

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
 
L

Lynn Trapp

Do a search on Updatable Query in Access Help... it describes the
circumstances under which you can update.
 
J

John Vinson

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]
 
L

Lynn Trapp

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...
 
J

JK

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...
 
L

Lynn Trapp

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?
 
J

JK

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
 
Top