Check for Primary Key

[Guest]

Is there a way to programmatically check a table to see if it has a primary
key?

I've build a program that does a QA on a folder that contains approximately
80 access databases and have been able to automate the whole process except
for this part.

Thanks,
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Douglas J. Steele]

While the default name for the primary key index is PrimaryKey, it's
probably better not to rely on that. Try the following:

Function TableHasPK(TableName As String) As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim booExists As Boolean

' Be pessimistic and assume there isn't one.
' That way, you'll be pleasantly surprised
' when you find one!

booExists = False

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary Then
booExists = True
MsgBox idxCurr.Name
Exit For
End If
Next idxCurr

TableHasPK = booExists

End Function

 

[RoyVidar]

Is there a way to programmatically check a table to see if it has a
primary key?

I've build a program that does a QA on a folder that contains
approximately 80 access databases and have been able to automate the
whole process except for this part.

Thanks,

There are probably several ways, here's one, though ADO is a bit
frowned upon in these groups ;-)

Function TestPK(ByVal v_strTable As String) As Boolean
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaPrimaryKeys, _
Array(Empty, Empty, v_strTable))
TestPK = ((Not rs.BOF) And (Not rs.EOF))
rs.Close
End Function

Should you want names of primary key field(s), check out the what the
following returns, if there are records

debug.print rs.getstring

 

[Guest]

Thanks, I forgot to mention that I was already using ADO in my program
--
Billy Rogers

Dallas,TX

Currently Using Office 2000