VBA, Adding a Field to a Linked table

Iain Gordo
#1 / 3

Adding a Field to a Linked table

Using Access97 SR2
I need to be able to add a field to a linked table either programatically or
via a button on a form.
So far I have not been able to track down any information on this.
Any help or suggestions gratefully received.
Iain, in sunny Bradford, UK

Wed, 05 Mar 2003 03:00:00 GMT

jon
#2 / 3

Adding a Field to a Linked table

You will need to first get the path to the database that the linked table is
in, and then use DAO to add a field.

Sub sCreateNewField()
Dim db As Database
Dim tdf As TableDef
Dim strDB As String
strDB = Mid(CurrentDb.TableDefs("tblLinked").Connect, 11)
Set db = OpenDatabase(strDB)
Set tdf = db.TableDefs("tblPub")
With tdf
.Fields.Append .CreateField("FieldName", dbText)
End With
tdf.Fields.Refresh
Set tdf = Nothing
db.Close
Set db = Nothing
End Sub

Having said that, I always find it slightly worrying when people want to
programatically change tables - it often implies a denormalized database
design.

--
Jon

http://www.applecore99.freeserve.co.uk

Quote:

> Using Access97 SR2
> I need to be able to add a field to a linked table either programatically
or
> via a button on a form.
> So far I have not been able to track down any information on this.
> Any help or suggestions gratefully received.
> Iain, in sunny Bradford, UK

Wed, 05 Mar 2003 03:00:00 GMT

Iain Gordo
#3 / 3

Adding a Field to a Linked table

Thanks Jon,
Its not a denormalised table, its just that I *may* have a need to add
another check box to a table. There are several copies of my application in
different locations and I think that if I have to add this field the easiest
way will be by distributing an updated Frontend with either some "silent"
code or a button on a form to add the field.
Regards,
Iain, in Bradford, UK

Quote:

> You will need to first get the path to the database that the linked table
is
> in, and then use DAO to add a field.

> Sub sCreateNewField()
> Dim db As Database
> Dim tdf As TableDef
> Dim strDB As String
> strDB = Mid(CurrentDb.TableDefs("tblLinked").Connect, 11)
> Set db = OpenDatabase(strDB)
> Set tdf = db.TableDefs("tblPub")
> With tdf
> .Fields.Append .CreateField("FieldName", dbText)
> End With
> tdf.Fields.Refresh
> Set tdf = Nothing
> db.Close
> Set db = Nothing
> End Sub

> Having said that, I always find it slightly worrying when people want to
> programatically change tables - it often implies a denormalized database
> design.

> --
> Jon

> http://www.applecore99.freeserve.co.uk

Wed, 05 Mar 2003 03:00:00 GMT