Access 2007, Can't edit data in any query, none...at all,

[mhshane]

None of my queries will allow me to edit data.

As a test I created a brand new DB on my C drive (Trusted location)
Added Tbl 1 and Tbl 2

Tbl 1
ID
Number
Status

Tbl 2
ID
User
Location
Number

Qry1
Join includes all of Tbl 1 and matches in Tbl 2
I have actually tried all three joins with no luck.

I have two entries in each table.

Qry shows me both entries and the data from both tables but I cannot edit.

SQL:
SELECT Table1.Number, Table1.Status, Table2.User, Table2.Location
FROM Table1 LEFT JOIN Table2 ON Table1.Number = Table2.Number;

I have tried:
Trusted Center Settings
Enabled all macros
Have no ODBC connections
No Group By, Transform clause, etc
None of the reasons stated by MS apply to this simple DB?

Any ideas?

Thanks.

 

[dhookom]

Are either of the Number fields the primary key? If not, why not? What is the ID field for if not to join to a field in another table?

Duane
Hook'D on Access
MS Access MVP

 

[mhshane]

Hmmm, very interesting. The AutoID was the primary key in both tables because it is the default when you create a table. I did not bother changing it to something else since I was building the tables on the fly and thought the PK would not matter for my test.

So I went back and changed the key on Tbl 1 to "Number" and now I can edit from the query.

I expanded on this and changed the PK to "Status" on TBL 1. Kept the join on "Number". Could not edit from the qry.

Added a new field on each table: EMPID

Made EMPID the PK on Table 2 and set the join to EMPID in the qry.
Qry became editable again.

So, the join has to include a PK from one of the tables in order to edit from the qry? That is not listed in MS Office webpage as a requirement. Did I miss a big point in my Access 2003 classes? Or is this something new to Access 2007?
Or is there a config option I need to change in Access Options?

Thanks for the help. At least I have something to work with now.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhshane]

Cool. Learn something new everyday. My definition of One-To-Many was a query with more than two tables. I see my mistake after reading the white paper. Thank you for the info.