By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Finding the primary key using VBA

P: n/a
Hi all,

I need to determine the name of the primary key in a table, but this
must be done using VBA.

I was looking for something like,

for each field in someRecordset.fields
if field.type="primaryKey" then
do something
end if
Next

Does anyone know if a property exists for primary key??

Thanks

Mark

Jul 18 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Wed, 18 Jul 2007 19:10:36 -0000, mark_aok <ma******@hotmail.com>
wrote:

You can iterate the Indexes collection for the table, looking for one
with the Primary property set to True.

-Tom.

>Hi all,

I need to determine the name of the primary key in a table, but this
must be done using VBA.

I was looking for something like,

for each field in someRecordset.fields
if field.type="primaryKey" then
do something
end if
Next

Does anyone know if a property exists for primary key??

Thanks

Mark
Jul 19 '07 #2

P: n/a
On Jul 18, 3:10 pm, mark_aok <mark_...@hotmail.comwrote:
Hi all,

I need to determine the name of the primary key in a table, but this
must be done using VBA.

I was looking for something like,

for each field in someRecordset.fields
if field.type="primaryKey" then
do something
end if
Next

Does anyone know if a property exists for primary key??

Thanks

Mark
Solved here,
http://groups.google.com/group/comp....dfe3fec1853126

Jul 19 '07 #3

P: n/a
Using DAO:

CurrentDb.TableDefs("MyTable").Indexes("PrimaryKey ").Fields

With a table named 'MyTable' and an Index on your key field named
"PrimaryKey" (which is the Access default name for a table Index
flagged as key..
Ron, King of Chi

On Jul 18, 2:10 pm, mark_aok <mark_...@hotmail.comwrote:
Hi all,

I need to determine the name of the primary key in a table, but this
must be done using VBA.

I was looking for something like,

for each field in someRecordset.fields
if field.type="primaryKey" then
do something
end if
Next

Does anyone know if a property exists for primary key??

Thanks

Mark

Jul 19 '07 #4

P: n/a
Mark-

Try this:

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

mark_aok wrote:
>Hi all,

I need to determine the name of the primary key in a table, but this
must be done using VBA.

I was looking for something like,

for each field in someRecordset.fields
if field.type="primaryKey" then
do something
end if
Next

Does anyone know if a property exists for primary key??

Thanks

Mark
--
Message posted via http://www.accessmonster.com

Jul 19 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.