I am trying to create a talbe by coding in VB.Net and I know how to create a
table with one column as a primary key, but I need mulitple columns as the
key how can I do that? Right now I use:
SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
TEXT(10), [Game] INTEGER)".
To make one column the PK, but if I add a second or thrid constraint as as
PK in the same way it does not work. Can someone help me?

On Tue, 15 Feb 2005 10:49:11 -0800, "ryan"
<(E-Mail Removed)> wrote:

>I am trying to create a talbe by coding in VB.Net and I know how to create a
>table with one column as a primary key, but I need mulitple columns as the
>key how can I do that? Right now I use:
>SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
>K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
>[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
>TEXT(10), [Game] INTEGER)".
>To make one column the PK, but if I add a second or thrid constraint as as
>PK in the same way it does not work. Can someone help me?

out of the help file: Topic Microsoft Jet SQL reference

This example creates a new table called MyTable with two text fields,
a Date/Time field, and a unique index made up of all three fields.

Sub CreateTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.

dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName CHAR, LastName CHAR, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));"
dbs.Close

End Sub

---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

Hey Andi, this solution does not work I am getting a Constraint error. I am
using System.Data.OleDb. I dont know if that makes a difference. Also, will
the unique contraint do the same thing as the primary key. I dont think so.
All I need the key for is editing the table b/c if I dont have one and I try
to update the table I will get an error. Thanks

"Andi Mayer" wrote:

> On Tue, 15 Feb 2005 10:49:11 -0800, "ryan"
> <(E-Mail Removed)> wrote:
>
> >I am trying to create a talbe by coding in VB.Net and I know how to create a
> >table with one column as a primary key, but I need mulitple columns as the
> >key how can I do that? Right now I use:
> >SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
> >K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
> >[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
> >TEXT(10), [Game] INTEGER)".
> >To make one column the PK, but if I add a second or thrid constraint as as
> >PK in the same way it does not work. Can someone help me?
>
> out of the help file: Topic Microsoft Jet SQL reference
>
> This example creates a new table called MyTable with two text fields,
> a Date/Time field, and a unique index made up of all three fields.
>
> Sub CreateTableX2()
> Dim dbs As Database
> ' Modify this line to include the path to Northwind
> ' on your computer.
> Set dbs = OpenDatabase("Northwind.mdb")
> ' Create a table with three fields and a unique
> ' index made up of all three fields.
>
> dbs.Execute "CREATE TABLE MyTable " _
> & "(FirstName CHAR, LastName CHAR, " _
> & "DateOfBirth DATETIME, " _
> & "CONSTRAINT MyTableConstraint UNIQUE " _
> & "(FirstName, LastName, DateOfBirth));"
> dbs.Close
>
> End Sub
>
> ---
> If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
> MW
>

I figured it out. You can use
CREATE TABLE NBA_games_today ([Away] TEXT(20), [Home] TEXT(20), [Away_Score]
INTEGER, [Home_Score] INTEGER, [Spread] SINGLE, [Home_Fav] BIT, [OverUnder]
SINGLE, [Date] TEXT(20), [Day] TEXT(10), [Game] INTEGER, PRIMARY KEY (Away,
Home, Date) ), and it will work

"ryan" wrote:

> Hey Andi, this solution does not work I am getting a Constraint error. I am
> using System.Data.OleDb. I dont know if that makes a difference. Also, will
> the unique contraint do the same thing as the primary key. I dont think so.
> All I need the key for is editing the table b/c if I dont have one and I try
> to update the table I will get an error. Thanks
>
> "Andi Mayer" wrote:
>
> > On Tue, 15 Feb 2005 10:49:11 -0800, "ryan"
> > <(E-Mail Removed)> wrote:
> >
> > >I am trying to create a talbe by coding in VB.Net and I know how to create a
> > >table with one column as a primary key, but I need mulitple columns as the
> > >key how can I do that? Right now I use:
> > >SQL = "CREATE TABLE " & type & "_games_today ([Away] TEXT(20) CONSTRAINT
> > >K_Away KEY, [Home] TEXT(20), [Away_Score] INTEGER, [Home_Score] INTEGER,
> > >[Spread] SINGLE, [Home_Fav] BIT, [OverUnder] SINGLE, [Date] TEXT(20) , [Day]
> > >TEXT(10), [Game] INTEGER)".
> > >To make one column the PK, but if I add a second or thrid constraint as as
> > >PK in the same way it does not work. Can someone help me?
> >
> > out of the help file: Topic Microsoft Jet SQL reference
> >
> > This example creates a new table called MyTable with two text fields,
> > a Date/Time field, and a unique index made up of all three fields.
> >
> > Sub CreateTableX2()
> > Dim dbs As Database
> > ' Modify this line to include the path to Northwind
> > ' on your computer.
> > Set dbs = OpenDatabase("Northwind.mdb")
> > ' Create a table with three fields and a unique
> > ' index made up of all three fields.
> >
> > dbs.Execute "CREATE TABLE MyTable " _
> > & "(FirstName CHAR, LastName CHAR, " _
> > & "DateOfBirth DATETIME, " _
> > & "CONSTRAINT MyTableConstraint UNIQUE " _
> > & "(FirstName, LastName, DateOfBirth));"
> > dbs.Close
> >
> > End Sub
> >
> > ---
> > If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
> > MW
> >

On Tue, 15 Feb 2005 13:41:15 -0800, "ryan"
<(E-Mail Removed)> wrote:

>Hey Andi, this solution does not work I am getting a Constraint error. I am
>using System.Data.OleDb. I dont know if that makes a difference. Also, will
>the unique contraint do the same thing as the primary key. I dont think so.
>All I need the key for is editing the table b/c if I dont have one and I try
>to update the table I will get an error. Thanks
>

oops have send you the wrong example

Sub test1()

Dim aType As String, SQL As String
aType = "Test1"
SQL = "CREATE TABLE " & aType & "_games_today (Away TEXT(20), " _
& " K_Away INTEGER, Home TEXT(20), Away_Score INTEGER," _
& " Home_Score INTEGER, Spread SINGLE, Home_Fav BIT," _
& " OverUnder SINGLE, aDate TEXT(20) , aDay " _
& " TEXT(10), Game INTEGER," _
& " CONSTRAINT MyPK PRIMARY KEY (Away,K_Away, Away_Score ))"

CurrentDb.Execute SQL

End Sub


I don't know vb.net, but this example is working in Access with DAO

Note: dont use date, day in Access, this are reserved words. With your
brackets the SQL will work but you can get afterwards a lot of
troubles

Key is unknow to Access, therefore I used INTEGER, because I asumed
you want a Long
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW