CODEWITHSUNDEEP
ms-accessms-access-2010
user3020843
user3020843

Reputation: 11

Change Field Properties in table

I have an MS Access "Make Table" query that concatenates a SharePoint library URL and the path to each document uploaded. The result, however, is output as a Text field. Currently I have to manually change the field property from Text to Hyperlink in order to have the output displayed as the document name, with a hyperlink to the document. Is there a way to change the field property using a query?

Upvotes: 0

Views: 3603

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123419

As HansUp mentions, there is no way to do the conversion in SQL alone, but if you run a VBA procedure immediately after executing the make-table query it can "convert" the field for you.

(It actually has to create a new field, copy over the link data enclosed in # characters, drop the old field, and then rename the new one.)

Option Compare Database
Option Explicit

Sub ConvertFieldToHyperlink()
    Dim cdb As DAO.Database, tbd As DAO.TableDef, fld As DAO.Field
    Set cdb = CurrentDb
    Set tbd = cdb.TableDefs("yourTable")
    Set fld = New DAO.Field
    fld.Name = "zzzNew"
    fld.Type = vbVariant
    fld.Attributes = dbHyperlinkField
    tbd.Fields.Append fld
    Set fld = Nothing
    cdb.Execute "UPDATE [yourTable] SET [zzzNew]='#' & [linkField] & '#'", dbFailOnError
    tbd.Fields.Delete "linkField"
    tbd.Fields("zzzNew").Name = "linkField"
    Set tbd = Nothing
    Set cdb = Nothing
End Sub

Upvotes: 2

HansUp
HansUp

Reputation: 97101

The Hyperlink type field cannot be created with DDL. That Access SQL limitation applies regardless of whether you want to create a field or alter the type of an existing field. So your make table query can create a text field, but not a hyperlink field.

Separately, you could run VBA code to alter the field type in the table's DAO TableDef. However, I think it should be easier to create the destination table structure once and then load your data into that table. In other words, use an "append query" instead of a "make table" query. If you later need to replace the data in that table, don't drop the table and create a new version. Just DELETE the existing rows and then append the new data.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311123

You can use the concat operator (&) to manually create an href element:

SELELCT '<a href="' & link & '">' & name & '</a>'
FROM    documents

Upvotes: 0

Related Questions