473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Determine Primary Key through VBA

Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index, or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark

Jul 18 '07 #1
1 5275
Loop through the Indexes collection to find the one that has the Primary
property set. Note that the primary key consist of more than one field.

The example below shows how to loop the indexes, and assigns upper case P
for the first field in the primary key, or the lower case p for subsequent
fields. It's part of this routine:
http://allenbrowne.com/AppRelReportCode.html

Note that if you were trying to identify the AutoNumber field, you will need
to check the Attributes of the Field in the TableDef. There's an example in
the DescribeFields() function on the same page (above.)

Private Function DescribeIndexField(tdf As DAO.TableDef, strField As String)
As String
'Purpose: Indicate if the field is part of a primary key or unique
index.
'Return: String containing "P" if primary key, "U" if uniuqe index,
"I" if non-unique index.
' Lower case letters if secondary field in index. Can have
multiple indexes.
'Arguments: tdf = the TableDef the field belongs to.
' strField = name of the field to search the Indexes for.
Dim ind As DAO.Index 'Each index of this table.
Dim fld As DAO.Field 'Each field of the index
Dim iCount As Integer
Dim strReturn As String 'Return string

For Each ind In tdf.Indexes
iCount = 0
For Each fld In ind.Fields
If fld.Name = strField Then
If ind.Primary Then
strReturn = strReturn & IIf(iCount = 0, "P", "p")
ElseIf ind.Unique Then
strReturn = strReturn & IIf(iCount = 0, "U", "u")
Else
strReturn = strReturn & IIf(iCount = 0, "I", "i")
End If
End If
iCount = iCount + 1
Next
Next

DescribeIndexField = strReturn
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mark_aok" <ma******@hotmail.comwrote in message
news:11*********************@z24g2000prh.googlegro ups.com...
Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index, or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark
Jul 18 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Phillip J. Allen | last post by:
Hi all, I have a table with an “autonumber” primary key field that also acts as a foreign key in 2 other tables. I would like to programmatically add a new record to the first table and the...
7
by: adm | last post by:
There are a few ways to go about this, but what is the fastest when called from VBA? This if for an ADP with a SQL Server back-end.
11
by: LordHog | last post by:
Hello, I recently wrote an application that is used for testing units in a burn-in chamber. It uses two external library that require the use of P\Invoke in order to work with them. There is a...
3
by: jaYPee | last post by:
is there a way to know if the form is edited or not after calling addnew? Me.BindingContext(DsStudentCourse1, "Students").AddNew() because i got an error after closing the form. because in my...
15
by: Robby Russell | last post by:
I am trying to track down a method of determining what a sequence name is for a SERIAL is in postgresql. For example, CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT); \d foo...
6
by: Robin Lawrie | last post by:
Hi, Looking for some help here! I'm adding records to a SQL 2000 DB using the objRS.AddNew and objRS.Update methods. What I'd like to do is determine the new primary key value of the added...
7
by: bazzer | last post by:
i am having trouble updating a datset because i get the follwing error in the Data Adapter configuration Wizard: Generated UPDATE statement. could not determine which columns uniquely identify...
6
by: erick-flores | last post by:
Hello, I want to make a report for a single compressor. Instead of showing a report for all the compressors. I would like to place a button in the compressor form, so whenever I want to display a...
3
by: Jeff | last post by:
Is there a way in C# to determine the Primary Domain Controller when a users logs in on to a network. I would prefer not to use WMI query method as this may not be available. Regards Jeff
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.