Updating the structure of an Access Database file using VB 6

[33216CLC]

1. I used the code below to add several fields to a table and to add another field to the primary Key.

'''edit table employee
Set tbl = dbs.TableDefs("employee&quot
With tbl
.Fields.Append .CreateField("photograph", dbText, 20)
.Fields.Append .CreateField("IsEmployee", dbText, 1)
.Fields.Append .CreateField("salary_group", dbInteger, 2)
.Fields.Append .CreateField("jobcode", dbText, 10)
.Fields.Append .CreateField("jobname", dbText, 20)
.Fields.Append .CreateField("Division", dbText, 30)
.Fields.Append .CreateField("Branch", dbText, 30)
End With
dbs.TableDefs.Append tbl

With tbl
Set idxnew = .CreateIndex("company_id&quot
idxnew.Fields.Append idxnew.CreateField("company_id&quot
idxnew.Primary = True
End With
''''

The fields were added ok, but the primary key did not include the company_id, it remained the same as before.

2. I used the below to change the names and size of several fields of another table.

'''edit table group_hm_class
Set tbl = dbs.TableDefs("group_hm_class&quot
Set fld = dbs.TableDefs("GHGM&quot
fld.Name = "deduction_id"
fld.Size = 8
Set fld = dbs.TableDefs("name&quot
fld.Name = "deduction_id"
fld.Size = 30
tbl.Fields.Refresh
''''

The names were changed, but the field sizes remained the same.

Are any of these properties read-only, or is there something I can add to my code to get what I want?

Thanks

 

[MichaelRed]

Without checking in detail, most/all of the properties which did not change are Read Only [r]AFTER[/r] they are appended to the collection.

Weird? Perhaps. But true.

If your needs are modest (e.g. as posted) you should probably just opne the db and make the changes manually.

The big [red]ELSE[/red] option:

Not much fun, and I would STRONGLY advise adding a Make a back up copy of the table(s) before starting.

Delete the current (Incorrect) Index. Create a New Index with the proper (desired) porperties.

Create New Fields with the desired Properties (use dummy names for now, such as "deduction_id_NEW".

Update the new field(s) [deduction_id_NEW] to have the same data as the current fields ([deduction_id]).

Delete the original Field(s).

ReName the New fields to the original field names.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[33216CLC]

Thanks Michael. I was hoping to avoid your first suggestion, but it seems safer. I'll experiment a little with the second suggestion and see what happens.