Add Hyperlink field to linked Access table using VBA

Question

I need to add a hyperlink field to an existing linked table using VBA in Access. The field gets created, but when I try to change the attribute to Hyperlink, I get an error. Is it not possible to set a hyperlink attribute on an existing table? Any ideas?

Code:

'dbs is the linked database

With dbs

   Set tdf = .TableDefs!tblTest

      With tdf

         .Fields.Append .CreateField("WebsiteURL", dbMemo)

         .Fields("WebsiteURL") .Attributes = dbHyperlinkField    <<== It fails here with message Error 3219 Invalid Operation

      End With

End With

Answer 1

Second, this works for me:

    Dim dbs         As DAO.Database

    Dim tdf         As DAO.TableDef

    Dim fld         As DAO.Field

    Set dbs = CurrentDb

    With dbs

        Set tdf = .TableDefs!Employees

        With tdf

            Set fld = .CreateField("WebsiteURL", dbMemo)

            fld.Attributes = dbHyperlinkField Or dbVariableField

            .fields.Append fld

        End With

    End With

Answer 2

The space is correct in that syntax. It compiles (I think my syntax is just wrong). But your second solution works perfectly. Thanks

A little more analysis:

This code works fine when creating a new linked table:

            .Fields.Append .CreateField("Website", dbMemo)

            .Fields("Email").Attributes = dbHyperlinkField

Just not when adding a field to an existing table. I think the difference is when adding a new table, it isn't actually appended until all the fields and attributes are created and set.

Anyway, thank you for your help.

Answer 3

First off, there is a Space before ".Attributes"