Setting primary key with code?

[UniqueTII]

Is there any way to set the primary key of a table using code? The reason I want to do this is to set the PK after doing an automated TransferSpreadsheet so that I never have to actually deal with the table. Yeah, I'm lazy.

 

[UniqueTII]

Help was no help, but a quick search of some SQL pages got me exactly what I needed. Thanks a bunch!

 

[Mark Wild]

Here's a sample of PK's, indexes etc

Function CreateTable(strTable As String) As Boolean

' Creates the table structure , which needs to be re-built every import

On Error GoTo CreateError

Dim db As Database
Dim tbl As TableDef
Dim strTblName As String

Set db = CurrentDb
Set tbl = db.CreateTableDef(strTable)
' Creates the tabel outline and name

With tbl ' creates the field names and properties

.Fields.Append .CreateField("ImportDate", dbDate)
.Fields.Append .CreateField("Notes", dbText)
.Fields.Append .CreateField("ReviewedBy", dbText)
.Fields.Append .CreateField("RevDate", dbDate)
End With

ReRun:
db.TableDefs.Append tbl
' appends the field names and properties to the tabke to make it a "physical" entity

db.TableDefs.Refresh

DAOCreateIndex (strTable)
DAOCreatePrimaryKey (strTable)

CreateTable = True 'needed for when the module steps back up to where it's called from

ExitProc:
Set db = Nothing
Set tbl = Nothing
Exit Function

CreateError:

If Err.Number = 3010 Then
db.Execute "drop table ..."
Resume ReRun
Else
MsgBox "Error number: " & Err.Number & " - " & Err.Description, vbCritical
Resume ExitProc
End If

End Function


Sub DAOCreateIndex(strTblName)

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim idx As DAO.Index

' Open the database
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)

' Create Index object append Field object to the Index object.
Set idx = tbl.CreateIndex("AccountIndex")

idx.IgnoreNulls = True
idx.Fields.Append idx.CreateField("Account")
idx.Fields.Append idx.CreateField("Code")
idx.Required = True

' Append the Index object to the
' Indexes collection of the TableDef.
tbl.Indexes.Append idx

db.Close

Set db = Nothing
Set tbl = Nothing

End Sub

Sub DAOCreatePrimaryKey(strTableName)

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim idx As DAO.Index


' Open the database
Set db = CurrentDb

Set tbl = db.TableDefs(strTableName)

' Create the Primary Key and append table columns to it.
Set idx = tbl.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("Account")
idx.Fields.Append idx.CreateField("Code")

' Append the Index object to the
' Indexes collection of the TableDef.
tbl.Indexes.Append idx

db.Close

Set db = Nothing
Set tbl = Nothing

End Sub

HTH

 

[mission2java_78]

Sorry, I keep forgetting that help in A2K and AXP is no help at all. You would have found help had you been using A97.

Search for a file named: ACMAIN10.CHM

It contains the REAL Access help for XP including VBA, DAO, ADO, and SQL. I have just sent a question to Microsoft asking why this isn't the help file used when asking for help within Access.

Ya I'm certain the people at Microsoft will help you out and answer your e-mail *sigh*

Jon

 

[mission2java_78]

Sorry, I keep forgetting that help in A2K and AXP is no help at all. You would have found help had you been using A97.

Search for a file named: ACMAIN10.CHM

It contains the REAL Access help for XP including VBA, DAO, ADO, and SQL. I have just sent a question to Microsoft asking why this isn't the help file used when asking for help within Access.

win 2k/ access 2k...this file cannot be found.

Jon

 

[pono1]

From the horse's mouth...

Regards,
Tim