primary key in VBA (1 Viewer)

[Geordie2008]

Guys,

I make a table using VBA, and I want to put a primary key on a field using code

Any ideas? Ive trawled the web with no joy.

M

 

[Geordie2008]

Hi,

Just to give a bit more background.... coz I really cant find an answer to this one anywhere on the web....

I am trying to add a primary key to a table that already exists using VBA or SQL code....

Tnx,
M

 

[KenHigg]

With your dao table def object create a new index and set it's primary property to true.

 

[Geordie2008]

Do you have the exact code for this? Ive tried and tried and cannot get any code that actually does this?

M

 

[KenHigg]

Give me a few minutes and I'll see if I can come up with a sample db...

 

[grendell2099]

Not to steal your thunder Ken, but might this be the sort of thing you are looking for? Supply the table name and name of your index field, it returns the next number.

Function GetTableIndex(GetTable As String, GetField As String) As Integer
'DATE: 04-17-08
'PURPOSE: get index number for a new record from the selected table & field
'NOTE: Function returns an integer
'YOU MAY USE THIS CODE, BUT YOU MUST CREDIT THE AUTHOR

'On Error GoTo ERR_HNDL

'DEFINE VARIABLES
'THIS MODULE CREATES THE INDEX NUMBER FOR NEW PTRs
Dim zst As DAO.Recordset 'TABLE CALLED FOR
Dim ErrorMessage As String 'for error reporting
'
'****************************************************************************************

Set zst = db.OpenRecordset(GetTable)

GetTableIndex = Nz(DMax("[" & GetField & "]", GetTable) + 1, 0)

'-----------------------------------------------------------------------------------
EXIT_SUB: 'CLEAR VARIABLES
zst.Close
Set zst = Nothing
DoCmd.SetWarnings True
ErrorMessage = ""
Exit Function

ERR_HNDL:
ErrorMessage = "Function GetTableIndex" & vbCr & "ERROR# " & Err.Number & vbCr & Err.Description
Call PlayMySound("fault.wav", SND_ASYNC) 'SND_SYNC) SND_LOOP)
Call ErrorReport(ErrorMessage)
Resume EXIT_SUB

End Function

 

[KenHigg]

Not to steal your thunder Ken,

No problem, I was having to tinker with it myself...

 

[DJkarl]

The easiest way in code to add a primary key that I've found is to use SQL

Currentdb.Execute "ALTER TABLE [ExampleTable] ADD CONSTRAINT PrimaryKey PRIMARY KEY ( [FieldName1],[FieldName2] );"

 

[KenHigg]

Didn't think of that -