469,156 Members | 2,112 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,156 developers. It's quick & easy.

How to set Primary Key with VBA?

31
Hi All.

I was wondering if there was a way to set the primary key of a table with VBA? I have a make table query that runs, however when it creates the table it does not set a primary key. I would like to do this with VBA instead of manually.

Any help would be great. Thanks.
Jan 28 '11 #1
4 21556
TheSmileyCoder
2,321 Expert Mod 2GB
Ive written some code for you, that will set the primary key of any table. If a primary key index exists, it will be removed. Hope it helps you.
Expand|Select|Wrap|Line Numbers
  1. Public Sub setPrimaryKey(strTblName As String, strFieldName As String)
  2.     'This proc sets the primary key of a table.
  3.     'BEWARE it will delete all other index existing in the table
  4.     'Copyright: TheSmileyCoder
  5.     'This code may be freely used and distributed as long as the copyright notice remains
  6.  
  7. On Error GoTo err_Handler
  8.  
  9.     Dim tdf As TableDef
  10.     Dim bTableFound As Boolean
  11.  
  12.  
  13.     For Each tdf In CurrentDb.TableDefs
  14.       If tdf.Name = strTblName Then
  15.         bTableFound = True
  16.         Exit For
  17.       End If
  18.     Next tdf
  19.  
  20.     If Not bTableFound Then
  21.         MsgBox "Table not found"
  22.         Exit Sub
  23.     End If
  24.     Dim myField As Field
  25.     Dim bFieldFound As Boolean
  26.     For Each myField In tdf.Fields
  27.         If myField.Name = strFieldName Then
  28.             bFieldFound = True
  29.             Exit For
  30.         End If
  31.     Next myField
  32.  
  33.     If Not bFieldFound Then
  34.         MsgBox "Field not found"
  35.         Exit Sub
  36.     End If
  37.  
  38.  
  39.  
  40.  
  41.         'Delete the current primary indexes if it exists
  42.  
  43.         Dim idxLoop As Index
  44.         For Each idxLoop In tdf.Indexes
  45.             If idxLoop.Primary Then
  46.                 'Existing primary index found, delete it
  47.                 tdf.Indexes.Delete idxLoop.Name
  48.                 Exit For
  49.             End If
  50.         Next
  51.  
  52.         'Add our new index
  53.         Dim idxNew As Index
  54.         Set idxNew = tdf.CreateIndex("myPrimary")
  55.  
  56.         idxNew.Fields.Append idxNew.CreateField(strFieldName)
  57.         idxNew.Primary = True
  58.         tdf.Indexes.Append idxNew
  59.  
  60. exit_Sub:
  61.     Set myField = Nothing
  62.     Set idxNew = Nothing
  63.     Set tdf = Nothing
  64.     Exit Sub
  65.  
  66. err_Handler:
  67.     MsgBox "Error [" & Err.Number & "] occured." & vbNewLine & Err.desscription
  68.     GoTo exit_Sub
  69. End Sub
Jan 28 '11 #2
BarbQb
31
Thank you very much for the code. Will this work for a Composite Primary Key? I forgot to mention that the PK consists of 3 fields.
Jan 28 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
To be honest it bugs me quite a bit, that you didn't supply this information up front. Bear in mind that I used atleast 30 mins piecing together that solution for you, and yet you couldn't spend more then 2 minutes posing your question properly. Please try to bear this in mind next time you ask a question.

Here is the modified code which allows you to add up to 3 fields to a composite primary index. It can be be expanded to include more if you want to.
Expand|Select|Wrap|Line Numbers
  1. Public Sub setPrimaryKey(strTblName As String, strFieldName As String, Optional strFieldName2 As String, Optional strFieldName3 As String)
  2.     'This proc sets the primary key of a table.
  3.     'BEWARE it will delete all other index existing in the table
  4.     'Copyright: TheSmileyCoder
  5.     'This code may be freely used and distributed as long as the copyright notice remains
  6.  
  7. On Error GoTo err_Handler
  8.  
  9.     Dim tdf As TableDef
  10.     Dim bTableFound As Boolean
  11.  
  12.  
  13.     For Each tdf In CurrentDb.TableDefs
  14.       If tdf.Name = strTblName Then
  15.         bTableFound = True
  16.         Exit For
  17.       End If
  18.     Next tdf
  19.  
  20.     If Not bTableFound Then
  21.         MsgBox "Table not found"
  22.         Exit Sub
  23.     End If
  24.     Dim myField As Field
  25.     Dim bFieldFound As Boolean
  26.     For Each myField In tdf.Fields
  27.         If myField.Name = strFieldName Then
  28.             bFieldFound = True
  29.             Exit For
  30.         End If
  31.     Next myField
  32.  
  33.     If Not bFieldFound Then
  34.         MsgBox "Field not found"
  35.         Exit Sub
  36.     End If
  37.  
  38.  
  39.  
  40.  
  41.         'Delete the current primary indexes if it exists
  42.  
  43.         Dim idxLoop As Index
  44.         For Each idxLoop In tdf.Indexes
  45.             If idxLoop.Primary Then
  46.                 'Existing primary index found, delete it
  47.                 tdf.Indexes.Delete idxLoop.Name
  48.                 Exit For
  49.             End If
  50.         Next
  51.  
  52.         'Add our new index
  53.         Dim idxNew As Index
  54.         Set idxNew = tdf.CreateIndex("myPrimary")
  55.  
  56.         idxNew.Fields.Append idxNew.CreateField(strFieldName)
  57.         If strFieldName2 & "" <> "" Then
  58.             'Add extra field (composite index)
  59.             idxNew.Fields.Append idxNew.CreateField(strFieldName2)
  60.         End If
  61.  
  62.         If strFieldName3 & "" <> "" Then
  63.             'Add extra field (composite index)
  64.             idxNew.Fields.Append idxNew.CreateField(strFieldName3)
  65.         End If
  66.  
  67.         idxNew.Primary = True
  68.         tdf.Indexes.Append idxNew
  69.  
  70. exit_Sub:
  71.     Set myField = Nothing
  72.     Set idxNew = Nothing
  73.     Set tdf = Nothing
  74.     Exit Sub
  75.  
  76. err_Handler:
  77.     MsgBox "Error [" & Err.Number & "] occured." & vbNewLine & Err.desscription
  78.     GoTo exit_Sub
  79. End Sub
Jan 28 '11 #4
Nordo
1 Bit
Smiley, I just mooched the code you wrote to add a key field (the first response to Barb). Super useful. Clean kill. Worked like a champ with zero mod. Thanks a ton. -Nordo
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by John Simmons | last post: by
5 posts views Thread by Ghulam Farid | last post: by
9 posts views Thread by 101 | last post: by
18 posts views Thread by Thomas A. Anderson | last post: by
4 posts views Thread by Peter | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.