Unique index message
-
Hi all, I am connecting to SQL Server 2000 via MS Access as the front end. No problem in getting connected to the Server, database and tables. However, I am always get prompted for unique index field. My table has no primary key and do not need one. I just click on cancel on the message and continue. Everything work as expected. Question here is can I turn off the Unique key message from the Server? Thanks in advance.
-
As far as I know access need at least a unique constraint to work. There are many problems related to that and you apparently discovered another one. BTW why wouldn't this table need a promary key?
-
Tables on the same database bears no relationship to each other. Theses tables are read-only.
-
A table is still a table, and it has to have some sort of unique key that uniquely identifies the data. Do you have an exemple of such a table?
-
Every table needs a unique primary key. This is the most fundamental rule of database design.
If you don't have this, forget about database programming, and forget about using Access - it won't be possible.
Even if your table has only one column and one record, it still should have a unique primary key!!!
-
Actually even if you have no row, you should have a PK... unless you don't want to have an actual table
. -
You only need a PK on an SQL table if you want to update or delete from Access. If you only read the tables then when you first link the table it will prompt you for a key if you don't select a key then you will not be able to amend/delete
-
If you dont want the primary key prompt in Access then add a promary key to the table - even if it is superfluous to your needs it takes care of the prompt.
-
Try creating a primary key by adding a field called ID as an integer and then make it an IDENTITY field with a seed of (1, 1). Set it as a primary key and you'll never get prompted again and never have to use it if you have no need. It will functionally work as a row counter.
-
You will need to update the link to the Sql Table in Access after adding the PK to the table.
-
Why not consider converting the .mdb to an .adp? Adp will use the Sql engine for what it needs. We have it deployed in a very large scale deployment with over 200 concurrent users across of T100 line. Very very fast. Can scan an item and find its value from over 14,000,000 records, apply 800 lines of business rules validation and display on screen in under 1 sec. Very robust. Uses T-Sql, does not connection commands, allows you to build stored procedures / triggers / views. Outperformed .net and vb in identical tests. Corporate auditor thought it was a smoke and mirrors trick (the speed) so he spent additional time validating. Sent me a personal e-mail the next day and asked if I would disclose the methodology. I didn't know what it was so I stayed away for a long time, but I will never go back unless I have to. Give some thought. An adp is a front-end GUI to SQL with the Access vba, wizards and report tools. You will not believe the speed and it only makes 1 connection (at start to the SQL server db).
Just a thought,
Mike
-
My Rant:::
Don't namel the column "ID" that is a reserved word in some languages, name it rowId which will not create a conflict in any language now or in the future.
Pamela Reinskou -
Thank you for everyone's input. Just added a PK to tables (no update or add etc... tables are read only), work just fine.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply