Recordset not updateable

[justlearning2003]

Hey,

I have a query created using two tables. When I run the query it prevents me from updating any records. Any ideas why this is happening or a way around it?

Chris

 

[jebry]

Hi!

If you could post the SQL you are using and the basic structure of the tables, we could get a better idea of the problem. The only reason I know of why you would get an unupdatable recordset is because Access is having trouble determining what records to update in each table. Why it is having that problem can only be determined with more information.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[justlearning2003]

Hey Jeff,

Here is the sql.


SELECT [test\].aname, [test\].[Employee #], [test\].tl, [test\].om, [test\].LOGIN, tbltech.feedback, tbltech.comments, tbltech.dategiv
FROM [test\] INNER JOIN tbltech ON [test\].LOGIN = tbltech.InvalidID
WHERE ((([test\].tl)=[Enter TL]));

 

[jebry]

Hi!

I don't see anything wrong with the SQL assuming InvalidID and LOGIN are the PK's for the tables or a PK-FK pair. To make a guess I would say that the tablename test\ and the field name Employee # are causing the problem. Access does some wierd things when unusal characters are used in the name of an object (especially when it is the last character of the name). So rename the table (possibly test1) and the field (EmployeeNumber) and see it that helps.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[justlearning2003]

Hey,

The fields InvalidID and LOGIN and NOT PK's.
Could this be the problem.
Not really sure what you meant by
"tables or a PK-FK pair."

??
Chris

 

[jebry]

Hi Chris!

PK-FK is Primary Key-Foriegn Key which is how two tables are related. Sometimes Access requires that the primary key from each table be part of the recordset for it to be updateable (even if the PK is not used for anything). That usually isn't a problem unless the query is more complex than the one you posted. Make sure that the join you posted, [test\].LOGIN = tbltech.InvalidID, along with the criteria, [test\].tl)=[Enter TL], will always resolve to just one record from each table. If it doesn't then the PK from the table on the many side must be included in the recordset for Access to be able to determine what record to update in that table.

Hope that makes sense.
:


Jeff Bridgham
bridgham@purdue.edu

 

[justlearning2003]

Jeff,

I changed the InvalidID to the PK and now everything works.

Thanks
Chris

 

[jebry]

Good, glad it is working!

P.S. I would still change the table name and field name. Somewhere down the road they will cause problems.


Jeff Bridgham
bridgham@purdue.edu