Updating field attributes...

  • Thread starter Thread starter RzB
  • Start date Start date
R

RzB

I want to update some table/field/attributes from VBA code...

I need to make the fields Required, Indexed and NoDuplicates...

I have the syntax to do the Required thing

Set tdf = dbBE.TableDefs("tblWhatever")
tdf.Fields("fldWhatever").Required = True

but how do I do the Indexed and NoDuplicates

Have searched google and the help but can't find this info...

Also...

What happens to existing data in tables that does
not follow these "new" rules... When do I get the torrent
of errors ?

Thanks,
Roy
 
Na rijp beraad schreef RzB :
I want to update some table/field/attributes from VBA code...

I need to make the fields Required, Indexed and NoDuplicates...

I have the syntax to do the Required thing

Set tdf = dbBE.TableDefs("tblWhatever")
tdf.Fields("fldWhatever").Required = True

but how do I do the Indexed and NoDuplicates

Have searched google and the help but can't find this info...

Also...

What happens to existing data in tables that does
not follow these "new" rules... When do I get the torrent
of errors ?

Thanks,
Roy
Click to expand...

If memory serves me well, that is a property of the index
(tdf.indexes("<indexname>"), not from the table.
 
Gijis,
Ahh - yes - Hmm - Not sure I fully understand the VB
help on this. The help is OK if you are creating new tables
fields etc but not too clear if you are updating...

So - the index doesn't exist in the first place so I gues I
have to create it first...

so that would be something like....
dim inx as dao.index
dim tdf as dao.tabledef

Set tdf = dbBE.TableDefs("tblWhatever")
tdf.Fields("fldWhatever").Required = True

Set inx = tdf.CreateIndex("inxSomeName")

I suppose at this point I can set it's attributes...

inx.Unique = True

---
then I need to append it to the Table/Field ? - yes/no ?
or do I append the table/field to the index ?

inx.Fields.Append inx("inxSomeName")

but how does it know what field I'm interestd in?

Doh... I'm missing something... :-(

Many thanks for your help...
Roy
..
 
OK - by pouring over the help, books and google
archives for hours I seem to have magically made
this work. So I'll put this up in case anyone in the
future has a similar problem. If anyone sees a
problems please shout :-)

Thank you Gijs for pointing me in the right direction.

The one thing I still don't understand is that if there is
data in the table that breaks these new rules - what
happens ?

If I do these changes manually, I get an error if there
is data in the tables that breaks the rules. Doing it
from VBA I get no errors. What's going on under the
covers?

Many thanks,
Roy

==============================
Dim dbBE As dao.Database
Dim tdf As dao.TableDef
Dim inx As dao.Index

Set dbBE = DBEngine.OpenDatabase(strDbPathAndName)

'Set up tdf for the table in question
Set tdf = dbBE.TableDefs("tblWhatever")

'Make the field in question "Required"
tdf.Fields("fldWhatever").Required = True

'Now create an index and give it any old name
Set inx = tdf.CreateIndex("SomeName")

'Make it "No Duplicates"
inx.Unique = True

'Now apply the field to the index ?
inx.Fields.Append tdf.CreateField("fldWhatever")

'Now put the index in the collection
'of indexes for this table
tdf.Indexes.Append inx

'and freshen it up a little
tdf.Indexes.Refresh

set tdf = nothing
set inx = nothing

dbBE.Close
Set dbBE = Nothing
==============================
 
There's nothing in your sample code, but when I put error handling in, I got
error 3022 ("The changes you requested to the table were not successful
because they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again.") at tdf.Indexes.Append inx
 
I need to make the fields Required, Indexed and NoDuplicates...
Click to expand...

This is a major schema change, so it shouldn't be undertaken lightly
but...
You are probably sensible to do something like this first:

1) pick up all the missing data before you set it NOT NULL:

SELECT ALL identifier
FROM mytable
WHERE myfield IS NULL

2) collect all the duplicates before trying to make them unique

SELECT ALL myfield, COUNT(*) AS NumOfDuplicates
FROM mytable
GROUP BY myfield
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC

If these both return empty sets, then you can change the column
attributes (if it's not an integer, change it to the correct data type):

UPDATE mytable
ALTER COLUMN MyField INTEGER NOT NULL

Then create the unique index:

UPDATE mytable
ADD CONSTRAINT ix_MyField UNIQUE KEY (myfield)

Hope that helps


Tim F
 
Tim,

Very many thanks for that. Very helpful. Yes I am
working on code that goes and makes sure the data
is ok before I update the field attributes. However
your code is soooo much better than I was thinking
about. Very, very helpful.
Many thanks,

Roy
 
Douglas,
Yes I do have error handlig code - just left it all out for clarity..

Interestingly I don't get any errors. However the only error
I was expecting was down to some fields being null.
I expected some problems when setting the "required" attribute.

I know that there ar no duplicates in the tables, so wasn't
expecting problems there. However I'll create some and see
what happens...

I am working on code to make sure the data follows the new
rules before I fiddle with the attributes. Tim Ferguson gave
some excellent advice about this in another post in this thread.

Many thanks for your help.
Roy
 
Douglas,

Yes - I get the same error as you if I have duplicates.

But it doesn't complain about the null values.

Thanks,
Roy
 
Back
Top