What is the MS Access SQL syntax to create a field of type Hyperlink?

Question

I'm working on a C# project that uses System.Data.OleDb.OleDbCommand class to create and alter tables in an MS Access DB. I generate the SQL statement, pass it to the object, then call the ExecuteNonQuery function. I was able to figure out the proper MS Access SQL syntax to create columns of the following Access data types:

AutoNumber: ALTER TABLE table-name ADD COLUMN column-name COUNTER|AUTOINCREMENT
Currency:   ALTER TABLE table-name ADD COLUMN column-name MONEY
Date/Time:  ALTER TABLE table-name ADD COLUMN column-name DATE
Memo:       ALTER TABLE table-name ADD COLUMN column-name MEMO|TEXT
Number:     ALTER TABLE table-name ADD COLUMN column-name NUMBER
OLE Object: ALTER TABLE table-name ADD COLUMN column-name OLEOBJECT
Text:       ALTER TABLE table-name ADD COLUMN column-name CHARACTER
Yes/No:     ALTER TABLE table-name ADD COLUMN column-name BIT

The only type I haven't figured out how to create is Hyperlink. Does anyone know the syntax for that one?

Answer 1

There is no real concept in sql of a datatype of hyperlink. You would store the url as you would any text and then when you're using it in your application you would create the hyperlink on that end.

Answer 2

You would use two CHARACTER fields to represent the content of a hyperlink, one that contains the URL to the resource you want the hyperlink to link to (stored as plain text), and another that would hold the text that's shown in the browser (that is, if the text shown in the browser is different from the URL itself).

When displaying the link in a webpage in C#, you can create a hyperlink:

Hyperlink hyp1 = new Hyperlink
hyp1.Text = datarow.DisplayText
hyp1.NavigateURL = datarow.TargetURL

Answer 3

This is a case where you can't use DDL but must use DAO. What you're looking for is the .Attributes property of the DAO Field type. In VBA, this code does the trick (you'll have to figure out how to do this with DAO in C#):

  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field

  Set tdf = CurrentDB.TableDefs("MyTable")
  Set fld = tdf.CreateField("Hyperlink", dbMemo) ' dbMemo = 12
  fld.Attributes = dbHyperlinkField ' 32768
  tdf.Fields.Append fld
  Set fld = Nothing
  Set tdf = Nothing

If you check the data type of this field in Access:

  CurrentDB.TableDefs("MyTable").Fields("Hyperlink").Type

it returns 12, which is equal to the VBA global constant dbMemo (that's how I figured out how to do it, i.e., created a Hyperlink field in the Access UI, then checked its datatype). The value for dbHyperlinkField is 32768, but cannot be assigned directly as a data type -- it is instead a sub-attribute of a memo field.

This is a case of Access extending Jet by using a custom attribute for it to work with the data differently than the limited number of Jet data types. It's also clear that the Hyperlink field type is only relevant if you're using the data from Access itself. If you're using some other application to work with the data, you're gaining nothing at all from using a hyperlink data field.

Also, I'm with those who recommend not using a field of type Hyperlink because I've tried it and it's just a pain in the ass. Also, given that it's really a memo field, it's subject to all the problems that come with memo fields. That is, the data is not actually stored with the data table -- all that's stored in the main record is a pointer to the data page where the real data is stored, and those pointers are one of the frailer parts of Jet. Memos are worth that risk, because when you need to store more than 255 characters, you need to do it. I can't see that there's any significant functionality added by the Access-specific Hyperlink field type that it's worth the trouble in working with it in code or the risk that comes from the fact that it's implemented behind the scenes as a memo field.

Answer 4

I had a play around with this with ADOX in C# and the only way I found to do it was set the "Jet OLEDB:Hyperlink" property after creating the column in the table but before actually adding the table to the Tables collection.

The following example requires a COM reference to Microsoft ADO Ext. 2.8 for DDL and Security:

ADOX.Catalog cat = new CatalogClass();    
cat.let_ActiveConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" + 
    "Data Source=C:\\Tempo\\Test_Access2007.accdb");

ADOX.Table tbl = new ADOX.TableClass();
tbl.Name = "TestHyperlink";

tbl.Columns.Append("my_hyperlink", ADOX.DataTypeEnum.adLongVarWChar, 0);

ADOX.Column col = tbl.Columns["my_hyperlink"];

col.ParentCatalog = cat;
col.Properties["Jet OLEDB:Hyperlink"].Value = true;

cat.Tables.Append(tbl);

Answer 5

I had a update problem a hyperlink datatype field in qsl update construction. After start UPDATE (sql-command) hyperlink datatype field worked as the usual text. The decision: It is necessary to place text value of hyperlink between characters "#". For example: UPDATE Table1 SET Table1.HypLinkTypeField = "#http://h00-srv-9:8080/browse/xxxxxxx#";

Regards, YuSam