How to create hyperlink field w/CREATE TABLE

[AvGuy]

Access has the ability to create tables by using SQL data definition queries through SQL. I'm trying to create a table with a hyperlink field type, but am having trouble with the syntax for that field. Access gags if I just specify HYPERLINK, e.g.

DoCmd.RunSQL "CREATE TABLE MyTable (MyLink HYPERLINK)"

I've also tried combinations of the 3 parts of a hyperlink, but no luck there either.

Any ideas?

AvGuy

 

[RobCPA]

The Access SQL DDL language does not support the Hyperlink data type which is why your query isn't working. There is another way - create a table using DAO. The hyperlink field is actually a memo type field with an attribute of hyperlink. You can use the following code to create a table with a hyperlink field. You can also create whatever other fields that you want using this code:

Function CreateMyTable()
dim db as DAO.database
dim tdf as DAO.tabledef
dim fld as DAO.field

set db = currentdb
set tdf = db.createtabledef("Mytable&quot

set fld = tdf.createfield("Hyper", dbmemo)
fld.attributes = dbhyperlinkfield
tdf.fields.append fld

set fld = tdf.createfield("Textfield", dbtext)
tdf.fields.append fld

db.tabledefs.append tdf
End Function


I was thinking that you could create a table using your SQL code setting the field as type Memo, then in a short VBA code modifying the table's field attribute to dbHyperlinkField, but that doesn't work. The attributes setting can only be written when the field is being created, and is read-only after the table is created.

Rob