create only Primary key with code??

[alaric]

I've copied a table from the BE to the FE. During the copy it losses the primary key (the help confirms this)

does somebody knows how to create just a new primary key instead of creating a whole new table with vba?

is this possible?

thx
Al

 

[Len Boorman]

Check Access Help on ALTER TABLE statement

and also CONSTRAINT which explains setting the primary key

Suspect that you are able to put Constraint in an Alter Table statement so that all you add is the primary key

Len B

 

[alaric]

"Operation is not supported for this type of object"

thx for your reply

with this hint I wrote following code as a test for functionality:

Dim conDatabase As ADODB.Connection
Dim SQL As String

On Error GoTo Error_Handler

Set conDatabase = Application.CurrentDb.Connection

SQL = "CREATE TABLE tblCustomers (" & _
"CustomerID INTEGER CONSTRAINT PK_tblCustomers PRIMARY KEY, " & _
"[Last Name] TEXT(50) NOT NULL, " & _
"[First Name] TEXT(50) NOT NULL, " & _
"Phone TEXT(10), " & _
"Email TEXT(50), " & _
"Address TEXT(40) DEFAULT Unknown)"

conDatabase.Execute SQL

MsgBox "The Customers table has been created.", vbInformation

conDatabase.Close
Set conDatabase = Nothing

Whatever I try I always get the message:
"Operation is not supported for this type of object"

 

[alaric]

Got It!!

code to use to add a primary key on a copied table (wich looses its key):

strSQL = "ALTER TABLE Temp ALTER COLUMN columnNameID INTEGER PRIMARY KEY"

thx to you all for all suggestions

Al