Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tweek some Access ?

Tweek some Access ?

Tweek some Access ?

I,m hard coding some vb5 to build a database in Access. using page 1463 of microsoft ref lib (language ref)
How do i add in both unique and Primary . Thanks

RE: Tweek some Access ?

Keys I presume..
This example sets the Unique property of a new Index object to True, and appends the Index to the Indexes collection of the Employees table. It then enumerates the Indexes collection of the TableDef and the Properties collection of each Index. The new Index will only allow one record with a particular combination of Country, LastName, and FirstName in the TableDef.

Sub UniqueX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxNew As Index
Dim idxLoop As Index
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees
' Create and append new Index object to the Indexes
' collection of the Employees table.
Set idxNew = .CreateIndex("NewIndex")

With idxNew
.Fields.Append .CreateField("Country")
.Fields.Append .CreateField("LastName")
.Fields.Append .CreateField("FirstName")
.Unique = True
End With

.Indexes.Append idxNew

Debug.Print .Indexes.Count & " Indexes in " & _
.Name & " TableDef"

' Enumerate Indexes collection of Employees table.
For Each idxLoop In .Indexes
Debug.Print " " & idxLoop.Name

' Enumerate Properties collection of each Index
' object.
For Each prpLoop In idxLoop.Properties
Debug.Print " " & prpLoop.Name & _
" = " & IIf(prpLoop = "", "[empty]", prpLoop)
Next prpLoop

Next idxLoop

' Delete new Index because this is a demonstration.
.Indexes.Delete idxNew.Name
End With


End Sub

RE: Tweek some Access ?

Thanks Doug, It seems I was not clear enough on the problem.
when the program reads
With idxnew
Fields Append ...........
Unique = true (This is the focus of the question)
(Where do I insert) Primary = true (AND)
Required = true [required is new for today]


RE: Tweek some Access ?

Not sure I don't use code to create a database I use the Access.

RE: Tweek some Access ?

How about this?

Dim idxNew as Index

'From Dougs example above.
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set tdfEmployees = dbsNorthwind!Employees

Set idxNew = tdfEmployees.CreateIndex("NewIndex")

'Make this a unique index.
idxNew.Unique = True


'Make this a Primary key index.
idxNew.Primary = True

'Make the field required.
idxNew.Required = True


RE: Tweek some Access ?

Thanks Steve, I've got 2 but the "required" won't respond.
I'll write some edit lines to make it required.

RE: Tweek some Access ?

TP, You know I just thought of this. The thing you're trying to set is not at the index level. The Required part is actually at the field level. So you need to reference the property of the field. Do that like this:

dbsNorthwind.TableDefs("Employees").Fields("Name").Required = True

Another example:

dbsNorthwind.TableDefs("Employees").Fields("Title").Required = True

For the TableDefs part, you put the table you are interested in. In the Fields part you put the name of the field you're interested in. Then you select the property to set. In this example, I chose the Employees table and set the Name field to Required. You can also get to the Zero length property at this point. In fact there are a ton of things you can set at this level.

Hope that helps,


RE: Tweek some Access ?


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close