Should a table must have a primary key ? (1 Viewer)

[edwinastono]

I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :

I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.

Many thanks in advance

 

[boblarson]

When connected to SQL Server, you MUST have a primary key in the tables. And, normally you must tell Access, when you initially set up the link, which field is the primary key. Otherwise it becomes read-only. Your Bill of Lading Detail table should have it's own Key field and BillofLadingID is a FOREIGN key in the detail table.

 

[edwinastono]

Dear Bob,

Thanks for your quick answer. Then it's mean I have to "spend" one field to create primary key if I use SQL Server ? But why that problem not occured when I use MDB format ? Is it the "price" that I have to pay if I want to use ADP format ?

 

[boblarson]

Actually, I don't know if you've noticed, but if your SQL Server table does not have a primary key, an MDB will read it, but you cannot write to it.

 

[edwinastono]

Dear Bob,

I think you missunderstand about it. When I use mdb format I create all the tables and relationship in Ms. Access, I'm not using SQL Server at all, pure Access and it was worked, I don't have to set a primary key in the detail table.

Edwin Astono

 

[boblarson]

Yes, that is true it will work, in an mdb internally. It's not good and not effective to not use primary keys in an access database either because keys and indexes will speed up processing as well as provide ways to truly link the data together. That doesn't mean that it can't be done otherwise, but just because something can be done doesn't mean that it's the right way to do it.

With SQL Server you MUST have a primary key in your table and have told Access which field is the key in order for it to be able to write to it. That's just the way it works. SQL Server is more strict about the data being written to itself when dealing as linked data.

 

[edwinastono]

Thanks for your explanation, you help me a lot.

 

[Dennisk]

when access reads one of its own tables even if there is no primary key it will create a bookmark for each row. this is a 'one off' primary key so the process can relate the current row being processed back to the original table. when using SQL tables Access (the Jet Engine) cannot do this through the ODBC drivers so a Primary Key is required.