Hi All

I had a problem that I had posted about several times but no one could offer any help. Eventually I put the query to Microsoft who actually solved it

I thought I'd save everyone else the hastle of going through the same route by posting the solution here. Enjoy


Problem: When an SQL table is linked in Access the tables should act just like normal linked Access tables. With this problem, Access is unable to recognise different records and therefore duplicates the first record for any subsiquent record added. This means that you can type anything into the second record of a table and, when saved, the record will take the values of the first record. The values in SQL however are correct as you typed them. This can be seen by connecting to the SQL data through excel or an SQL client.

Reason: Access needs a unique key to be able to identify a record. If there is no primary key on an Access table then it will use a hidden value as the key. When the tables are linked from SQL then Access cannot assign a hidden key if there is no unique value. This key must be in the form of an autonumber or Acces will still not recognise unique records.

Solution: Simply always ensure that there is a primary key in the SQL tables. When Access links the tables then you should not be prompted to specify a primary key as Access will recognise it and use it as the unique identifier.


I hope that this saves people the time and effort I spent solving it