Access Table Limitations

Question

Is there a limit to the number of fields that Access will take before it creates a problem?  I know a table should not be too large and that the max number of fields is 255.  

If a table has 100 fields will that create a problem like a write conflict error?  I have a table where I canwrite and update on SQL but can not update a record when it is linked to Access?  I have used the Access record locking feature this does not help.

Thanks

Answer 1

Your problem is not with the number of fields. In order to update a linked SQL table it has to have a primary key or at least a natural key. When you link to a SQL table and it has no primary key Access will prompt you to select the field(s) that will make the record unique. If you don't the table is read-only.

Your best bet is to make sure every table has a primary key. I usually use an int IDENTITY(1,1). That's the same thing as an AutoNumber.

You might find my article on linking to SQL tables of use to you. It is at http://thatlldoit.com/howtosarticles.aspx#sqlbackend