Using VBA to detect whether Primary Key exists

[xtcal]

Hello -

I have copied into my database some VBA code which automatically designates an existing field as a Primary Key, which works. However, if this code is referenced after the Primary Key is created, then I get the Primary Key Already Exists error.

I want to add lines to the code that first checks whether or not my table already contains a primary key. Can anybody offer tips on how to do this?

Any assistance is most appreciated.

Thanks!

 

[BrettStah]

Hello -

I have copied into my database some VBA code which automatically designates an existing field as a Primary Key, which works. However, if this code is referenced after the Primary Key is created, then I get the Primary Key Already Exists error.

I want to add lines to the code that first checks whether or not my table already contains a primary key. Can anybody offer tips on how to do this?

Any assistance is most appreciated.

Thanks!

One alternate solution is to just ignore the error that you're seeing by using error-handling code in your sub or function. If you need assistance with this, just post your code, along with the error number that you're getting when a table already has a primary key.

 

[xtcal]

Hi Brett - thanks for your quick reply. Such a simple solution, and it works for my database's purposes! :>

Thanks again!

 

[Moniker]

For the record, you could use a DDL (Data Definition Language) query in Access to return the primary key. If it returned a null, you would have your answer.

 

[The_Doc_Man]

In VBA, you have a way to see the TableDefs collection. In each TableDef in that collection [... TableDefs("tablename")... ] you find other collections - Fields and Indexes. If you step through the Indexes collection you would be able to see the properties of each index.

If you are in doubt, the keywords for Access Help lookup would be TableDefs, Fields, and Indexes. You can drill down to the latter from TableDefs. Usually there are links on the help pages to show you what is underneath the layer you are examining.

 

[raskew]

Try this, calling it as shown from the debug (immediate) window:

Public Sub PrimKey(tblName As String)
'*******************************************
'Purpose:   Programatically determine a
'           table's primary key
'Coded by:  raskew
'Inputs:    from Northwind's debug window:
'              Call PrimKey("Products")
'Output:    "ProductID"
'*******************************************

Dim db        As Database
Dim td        As TableDef
Dim idxLoop As Index

    Set db = CurrentDb
    Set td = db.TableDefs(tblName)
    For Each idxLoop In td.Indexes
       If idxLoop.Primary = True Then
          Debug.Print Mid(idxLoop.Fields, 2)
          Exit For
       End If
    Next idxLoop
    
    db.Close
    Set db = Nothing

End Sub

HTH - Bob