Changing properties of field in backend table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gibson - 27 Nov 2005 16:20 GMT

I am trying to change the Indexed property field of a backend table from
'Indexed (No Duplicates)' to 'Indexed (Duplicates OK)' . I am trying to do
this through code from the front end. I am trying the following code below.
I realize there most probably substantial errors in the code. I am new to
this. A problem occurs when I get to the line that deletes FIELD1. I
receive an Item not found in collection error. As you can readily see, I am
floundering a bit. First of all do I have to delete the field and or index
in order to change it? Can anybody give me some guidence here or guide me to
somewhere I can find some answers. This is something I will be using in the
future so I need to learn this.

Thanks for any help.

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("C:\BACKEND.MDB", True)
'Table to Change
Set tdf = db.TableDefs("flkpTABLE1")
'Delete problem index
tdf.Indexes.Delete ("FIELD1")
'Build new index
With tdf
Set idx = .CreateIndex("INDEX")
With idx
.Fields.CreatField ("NewField")
End With
'Add index t collection
.Indexes.Append idx
End With
db.Close
Set db = Nothing
End Sub

Reply to this Message

Allen Browne - 27 Nov 2005 16:32 GMT

You're pretty close, but you need to append the fields to the index before
you append the index to the table's Indexes.

This should work:

Set tdf = db.TableDefs("flkpTABLE1")
Set idx = tdf.CreateIndex("NewField")
With ind
.Fields.Append .CreateField ("NewField")
.Unique = False
End With
tdf.Indexes.Append idx

Signature

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.

>I am trying to change the Indexed property field of a backend table from
>'Indexed (No Duplicates)' to 'Indexed (Duplicates OK)' . I am trying to do
[quoted text clipped - 27 lines]
> Set db = Nothing
> End Sub

Reply to this Message

Gibson - 27 Nov 2005 21:45 GMT

Thanks for the help Allen. I used the code below but receive an error
"Invalid Argument" when the code gets to the
.Fields.Append.CreateField("FIELD1") line. I'm not sure why. Field1 is the
name of the existing field I want to change the Indexed property from
Indexed(NoDuplicates) to Indexed(Duplicates OK). I assume I am missing
something simply, hopefully. Thanks again.

Set db = wrkJet.OpenDatabase("C:\BACKEND.MDB", True)
Set tdf = db.TableDefs("'Delete problem index
Set idx = tdf.CreateIndex("FIELD1")
With idx
.Fields.Append.CreateField ("FIELD1")
.Unique = False
End With
tdf.Indexes.Append idx

Reply to this Message

Douglas J. Steele - 27 Nov 2005 22:22 GMT

Your syntax is slightly off. It shouldn't be .Fields.Append.CreateField
("FIELD1") but rather .Fields.Append .CreateField ("FIELD1") (i.e.: a space
before the .CreateField)

What you're actually doing is using two commands in one line. You're using
idx.CreateField("Field1") to create the field, then idx.Fields.Append to
append that field to the index. It's the equivalent of:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = wrkJet.OpenDatabase("C:\BACKEND.MDB", True)
Set tdf = db.TableDefs("flkpTABLE1")
Set idx = tdf.CreateIndex("FIELD1")
Set fld = idx.CreateField ("FIELD1")
idx.Fields.Append fld
idx.Unique = False
tdf.Indexes.Append idx

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for the help Allen. I used the code below but receive an error
> "Invalid Argument" when the code gets to the
[quoted text clipped - 43 lines]
>> Set db = Nothing
>> End Sub

Reply to this Message

Gibson - 28 Nov 2005 14:36 GMT

Thanks fo the help Douglas. Of course you were correct and your suggestions
worked. When I get to the line of code trying to append the idx to the Index
I receive an error tellimg me Index Already Exists. Can I not change the
index of an existing field? If I have to delete the field first and create
a new one, what about the existing data held in that field? The field name
is used in code throughout the frontend. It would be bothersome to have to
change it everywhere.

Thanks for the on going help.

> Your syntax is slightly off. It shouldn't be .Fields.Append.CreateField
> ("FIELD1") but rather .Fields.Append .CreateField ("FIELD1") (i.e.: a
[quoted text clipped - 64 lines]
>>> Set db = Nothing
>>> End Sub

Reply to this Message

Gibson - 28 Nov 2005 14:57 GMT

I tried deleting the index first then recreating it and that ran me through
the code without an error but made no change to the table index when I
reviewed the table. Could the problem be the Index I am trying to change
belongs the a field that happens to be the Primary Key. Is it possible
through code to change the Primary Key field to another field then change
the index of the first field? That, actually would be ideal.

Thanks again.

> Thanks fo the help Douglas. Of course you were correct and your
> suggestions worked. When I get to the line of code trying to append the
[quoted text clipped - 75 lines]
>>>> Set db = Nothing
>>>> End Sub

Reply to this Message

Douglas J Steele - 28 Nov 2005 17:21 GMT

It should be possible, but it also shouldn't be necessary...

Your code is trying to create an index named Field1. If you're actually
trying to use an existing index, replace

Set idx = tdf.CreateIndex("FIELD1")

with

Set idx = tdf.Indexes("FIELD1")

and remove the tdf.Indexes.Append idx line.

However, you say this is the primary key, yet you're trying to use

idx.Unique = False

A primary key MUST be unique.

FWIW, the primary key index is normally named PrimaryKey.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I tried deleting the index first then recreating it and that ran me through
> the code without an error but made no change to the table index when I
[quoted text clipped - 84 lines]
> >>>> Set db = Nothing
> >>>> End Sub

Reply to this Message

Gibson - 28 Nov 2005 21:42 GMT

Thanks for you expertise.Your clue on the name of the primary key index as
PrimaryKey is what did it. It could not find the name in the collection
because I was using the wrong name. Once changed to PrimaryKey it worked
fine. I do have one another question dealing with the same project but
slightly different topic. I don't know the protacol meaning if I should ask
it here or create a new thread. I'll include it here and if this is
incorrect feel free to let me know.
I'm trying to create a table in the backend mdb from code within the
frontend. I am using the following code:

Dim cat As ADOX.Catalog
Dim Tbl As ADOX.Table

On Error GoTo Err_Tbl
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
Set Tbl = New Table
With Tbl
.Name = "flkpNumber"
With .Columns
.Append "Number", adDouble, 10
End With
End With
cat.Tables.Append Tbl

The problem is this is creating the new table in the frontend instead of in
the backend mdb. I assume it has to do with the setting of the
cat.ActiveConnection line but I'm lost. How can I direct this code to
create the new table in the backend mdb.

Thanks again for all the help, it's greatly appreciated.

> It should be possible, but it also shouldn't be necessary...
>
[quoted text clipped - 117 lines]
>> >>>> Set db = Nothing
>> >>>> End Sub

Reply to this Message

Douglas J. Steele - 28 Nov 2005 22:44 GMT

Why are you using ADOX here, but DAO in the previous example?

Your previous example was working the backend database, wasn't it?

To be honest, I never use ADOX with Access: DAO was developed specifically
to be used with Jet databases (i.e. MDB files), and is the preferred
approach.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for you expertise.Your clue on the name of the primary key index as
> PrimaryKey is what did it. It could not find the name in the collection
[quoted text clipped - 27 lines]
>
> Thanks again for all the help, it's greatly appreciated.

Reply to this Message