What is the MS Access SQL syntax for creating a field of type Hyperlink?

Question

I am working on a C # project that uses the System.Data.OleDb.OleDbCommand class to create and modify tables in an MS Access database. I generate the SQL statement, pass it to the object, and then call the ExecuteNonQuery function. I was able to find the correct MS Access SQL syntax for creating 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 that I did not understand how to create is a hyperlink. Does anyone know the syntax for this?

Answer 1

sql . URL-, , , , .

Answer 2

, DDL, DAO. , , .Attributes DAO. VBA ( , DAO #):

  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

Access:

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

12, VBA dbMemo ( , , .. , ). dbHyperlinkField 32768, - memo.

Jet -, Jet. , Hyperlink , Access. - , .

, , Hyperlink, , . , , memo, , memo. - , , , , Jet. , , 255 , . , - , , , , , memo.

Answer 3

ADOX #, , , "Jet OLEDB:Hyperlink" , .

COM Microsoft ADO Ext. 2.8 DDL :

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 4

Microsoft , , , : -

myTable SET HyperlinkField = "( ) # ( URL) # ( , )" WHERE

, , , - ( , , ). URL- , . "..\\myFile1.dat".

, , .

, , , .

Answer 5

CHARACTER, URL- , ( ), , , ( , , , URL).

- # :

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

Answer 6

, (, ) , , VBA, .

Sub AddLinkField(Table, FieldName)
    Dim dbs As DAO.Database
    Set dbs = CurrentDb()
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set tdf = dbs.TableDefs(Table)
    Set fld = tdf.CreateField(FieldName, dbMemo) ' dbMemo = 12
    fld.Attributes = dbHyperlinkField ' 32768
    tdf.Fields.Append fld
    Set fld = Nothing
    Set tdf = Nothing
End Sub

SQL () c:\is..

SQL = "INSERT INTO Table VALUES (...,'LinkVisualizedText #c:\#')"

...

CurrentDB.Execute SQL