Primary Key field in Excel

[Guest]

Hi
How can I add a Primary Key field to a Excel Worksheet? Or let me ask it in this way: How can I set a column in a Excel Worksheet as Primary Key
I am trying to query a Excel Workbook from inside a .NET application with Sql statements, which works fine, and as I'm trying to dynamicaly generate Insert, Update and Delete commands (which .NET generates automaticaly, great) I receive the error that it can not generate commands on tables with no "Primary Key" field. Even though I have a field called ID with unique numbers, I don't know how to set that column as "Primary Key" so that my error will not occure any more
Is it then possible to set a column as Primary Key in Excel? Or shall I set it somehow in my Sql Select statement
Thanks
Navid

 

[Randall Roberts]

Seems to me that you want to be using Access not Excel

 

[Guest]

No I am using Excel and I want to use Excel because everything what I need can be done with it (I have only limited amount of Text data and database security is not a big matter in my case) and the file sizes are so much smaller than Acess databases (26K in compare to 2.3M). ADO.NET handels ALL kind of data with Sql statements, even Excel, what is also not bad at all. The only problem I have is with the Primary Key field which Excel seems not to be able to assign to any field.
I was able to solve my problem with static Insert, Update and Delete commands and not dynamic ones, but I am still very interested to know the answer to my original question.

 

[Harlan Grove]

How can I add a Primary Key field to a Excel Worksheet? Or let me
ask it in this way: How can I set a column in a Excel Worksheet
as Primary Key?

....

You can't. Excel doesn't provide this functionality.

I receive the error that it can not generate commands on tables with
no "Primary Key" field. Even though I have a field called ID with
unique numbers, I don't know how to set that column as "Primary Key"
so that my error will not occure any more.
Is it then possible to set a column as Primary Key in Excel? Or shall
I set it somehow in my Sql Select statement?

Excel ranges may *ONLY* be treated as *UNKEYED* tables. There is *NO*
mechanism in Excel that would allow you to use any column(s) within any
Excel range as a primary key. Excel is a spreadsheet, not a DBMS.
Spreadsheets generally and Excel in particular are *designed* to provide
near complete freedom of referencing, meaning they impose *no* restrictions
with regard to referential integrity. That being the case, they provide no
support for it. Meaning no primary (or foreign) key fields.

If you need to use primary key fields, you need to use a file format other
than .XLS.

 

[Randall Roberts]

While Excel can do everything you want it to do
Unfortunatly in this situation Excel is not the program you need to be
using

You need to be using Access
Access uses primary keys Excel does not

 

[Guest]

Yes, I see. Unfortunately. I was hoping maybe there would be a way...
But thanks a lot.
Navid