Creating two Primary Key Fields both indexed 1

[EddyLLC]

I'm using the code below to create an Access table. It works fine but the Primary keys, Field1 and Field2 are not indexed . If I only list one field as the primary key it is created as indexed with no duplicates. Can I create two primary fields and have them both indexed with not duplicates?

Set cat = New ADOX.Catalog
strDb = CurrentProject.Path & "\Testing.mdb"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & strDb

cat.Create strConnect
'Set connection to currently catalog
Set conn = cat.ActiveConnection

strTable = "TblNew"
conn.Execute "CREATE TABLE " & strTable _
& "(Field1 CHAR (9),Field2 CHAR (10),Field3 CHAR (5)," _
& "CONSTRAINT idxPrimary Primary Key (Field1, Field2));"

Application.RefreshDatabaseWindow

 

[Remou]

Perhaps

 cn.Execute "CREATE TABLE " & strTable _
 & "(Field1 CHAR (9) CONSTRAINT UniqueF1 UNIQUE, " _
 & "Field2 CHAR (10)  CONSTRAINT UniqueF2 UNIQUE,Field3 CHAR (5)," _
 & "CONSTRAINT idxPrimary Primary Key (Field1, Field2)) "

http://lessthandot.com

 

[EddyLLC]

Works perfectly! Thanks for the help.

 

[EddyLLC]

One last question simply for my edification. How would the above code change if the database already existed and I was just adding the table and the fields?

I'm trying to development a library as I learn the coding.

Thanks

 

[RoyVidar]

I find this to be a very strange request.

When you create a composite Primary Key, consisting of two or more fields, then the "Primary Key rules" is enforced on it.

- none of the fields can be Null
- the combination of them must be unique

And for terminology, this isn't two Primary Keys, it is one Primary Key. A table can have only one Primary Key, but it may consist of more than one field -> A Composite Primary Key.

What you have asked for, and received, is that each of the fields the composite Primary Key consists of, individually, is unique and Non-Null. Meaning, each of them could by themselves be the Primary Key of the table. This, to me, seems illogical and unnecessary - and if I'm not mistaken, it could be considered a 2NF violation (roughly - each Non-Key attribute is dependent on the whole PK).

So why on earth do you create the composite Primary Key, when each of the individual fields are Candidate Keys (Unique and Non Null)? Why not just assign one of them as Primary Key?

That said - to connect to a database, you can use the CurrentProject.Connection property from within the same database, or if you connect to a different database, use a connection string similar to what you find here

http://www.connectionstrings.com/access,

http://www.connectionstrings.com/access-2007

(sample of how to connect, should be easily found around here, but check out for instance thread705-1545101

For more samples on how to work with data structure through SQL, you might have a look at for instance

http://msdn.microsoft.com/en-us/library/Aa140015(office.10).aspx

and referred articles.

Roy-Vidar