Tweek some Access ? 1

[tusconpapa]

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

 

[DougP]

Keys I presume..<br>
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.<br>
<br>
Sub UniqueX()<br>
<br>
Dim dbsNorthwind As Database<br>
Dim tdfEmployees As TableDef<br>
Dim idxNew As Index<br>
Dim idxLoop As Index<br>
Dim prpLoop As Property<br>
<br>
Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot<br>
Set tdfEmployees = dbsNorthwind!Employees<br>
<br>
With tdfEmployees<br>
' Create and append new Index object to the Indexes <br>
' collection of the Employees table.<br>
Set idxNew = .CreateIndex(&quot;NewIndex&quot<br>
<br>
With idxNew<br>
.Fields.Append .CreateField(&quot;Country&quot<br>
.Fields.Append .CreateField(&quot;LastName&quot<br>
.Fields.Append .CreateField(&quot;FirstName&quot<br>
.Unique = True<br>
End With<br>
<br>
.Indexes.Append idxNew<br>
.Indexes.Refresh<br>
<br>
Debug.Print .Indexes.Count & &quot; Indexes in &quot; & _<br>
.Name & &quot; TableDef&quot;<br>
<br>
' Enumerate Indexes collection of Employees table.<br>
For Each idxLoop In .Indexes<br>
Debug.Print &quot; &quot; & idxLoop.Name<br>
<br>
' Enumerate Properties collection of each Index <br>
' object.<br>
For Each prpLoop In idxLoop.Properties<br>
Debug.Print &quot; &quot; & prpLoop.Name & _<br>
&quot; = &quot; & IIf(prpLoop = &quot;&quot;, &quot;[empty]&quot;, prpLoop)<br>
Next prpLoop<br>
<br>
Next idxLoop<br>
<br>
' Delete new Index because this is a demonstration.<br>
.Indexes.Delete idxNew.Name<br>
End With<br>
<br>
dbsNorthwind.Close<br>
<br>
End Sub<br>
<br>
<br>

 

[tusconpapa]

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

 

[DougP]

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

 

[Snaggs]

How about this?<br>
<br>
Dim idxNew as Index<br>
<br>
'From Dougs example above.<br>
Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot<br>
Set tdfEmployees = dbsNorthwind!Employees<br>
<br>
Set idxNew = tdfEmployees.CreateIndex(&quot;NewIndex&quot<br>
<br>
'Make this a unique index.<br>
idxNew.Unique = True<br>
<br>
or <br>
<br>
'Make this a Primary key index.<br>
idxNew.Primary = True<br>
<br>
'Make the field required.<br>
idxNew.Required = True<br>
<br>
Steve<br>
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

 

[tusconpapa]

Thanks Steve, I've got 2 but the &quot;required&quot; won't respond.<br>
I'll write some edit lines to make it required.<br>
TP.

 

[Snaggs]

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:<br>
<br>
dbsNorthwind.TableDefs(&quot;Employees&quot.Fields(&quot;Name&quot.Required = True<br>
<br>
Another example:<br>
<br>
dbsNorthwind.TableDefs(&quot;Employees&quot.Fields(&quot;Title&quot.Required = True<br>
<br>
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.<br>
<br>
Hope that helps,<br>
<br>
Steve<br>
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A><br>

 

[tusconpapa]

Steve ..THANKS ... IT WORKS<br>
Tusconpapa