Solved: Field type designation in make table query

[Adonaioc]

I have a make table query that add 2 fields in the same table , a hyperlink and display text,

Expr1: tblCableCombos!tblSlot1_ItemNumber & "#" & tblCableCombos!tblSlot1_CablePrints

is in the "field:" section but when it makes my table the field is not a hyperlink. If i open it up from the table side i can change the data type to hyperlink but surely there is way to do it from the make table query. I need to automate it because this table is overwritten frequently.

any tips are appriciated

[CreganTur]

This is from ExpertsExchange: (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20808343.html)

As I understand it Jet knows nothing about Hyperlinks. A hyperlink field is really a memo field with an <Access> interpretation applied to it.
If this is correct, then you will not be able to create a hyperlink field in JET SQL.

To create a hyperlink field in code you can use the following example as a guide.
This code creates a table with a single field which is a hyperlink.

Pete


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

Set db = CurrentDb
Set tdf = db.CreateTableDef("Tablename")
Set fld = tdf.CreateField("MyHyperlink", dbMemo)
fld.Attributes = dbHyperlinkField
tdf.Fields.Append fld
tdf.Fields.Refresh
db.TableDefs.Append tdf
db.TableDefs.Refresh
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

[Adonaioc]

I am not sure where to put this code, or how to use it. what activates it?

[CreganTur]

You could put it wherever you want it- put it behind a click event for a button or somewhere else.

You can use this code as a guide to create a new table that has a valid field setup for hyperlinks. Then, instead of a make table query, you can use an append query to load your records into the new table.

[bguidry]

How could one adjust the code above to simply set existing table field to a "Hyperlink" field (you know, like after a make-table query event runs), instead of creating the table with the code?

I have tried editing the code as below, but am getting an error "Wrong number of arguments or invalid property assignment", at the third line.

Set db = CurrentDb
Set tdf = db.TableDefs("Tablename")
Set fld = tdf.Fields("MyHyperlink", dbMemo)
fld.Attributes = dbHyperlinkField
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

[bguidry]

So, I got it to work somewhat now, with the additional help from hxxp://accessblog.net/2007/03/how-to-change-field-type-using-dao.html. But now am getting an error that the "Recordset is not updateable". This is the same error I was getting when re-creating my table, including the hyperlink field, with a "make-table" query, in which Access would make the field a text field. Though, the code now changes the field to a hyperlink field after running the make-table query. And, if I manually changed the table's field to hyperlink type previously the code to open the hyperlink worked.

I don't understand what else could now be causing this problem. My current code (qryLithLogLinks is a query which basically calls the hyperlink value which is attached to a subform combo box):

Private Sub cmdOK_Click()
Dim qryLithLogLinks As Hyperlink
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

DoCmd.OpenQuery ("qryUpdateLithLogLinks")
Set db = CurrentDb
'---Create New Field
db.TableDefs.Refresh
Set tdf = db.TableDefs("tblLithLogLinks")
Set fld = tdf.CreateField("fldLITH_LOGS", dbMemo)
fld.Attributes = dbHyperlinkField
fld.OrdinalPosition = 3
tdf.Fields.Append fld
'Copy values to new field
db.Execute ("Update tblLithLogLinks Set fldLITH_LOGS=LITH_LOGS")
'Delete old field
tdf.Fields.Delete "LITH_LOGS"
tdf.Fields.Refresh
'Rename new field to old
tdf.Fields("fldLITH_LOGS").Name = "LITH_LOGS"
tdf.Fields.Refresh
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

DoCmd.OpenQuery ("qryLithLogLinks")
DoCmd.RunCommand acCmdOpenHyperlink

DoCmd.Close acQuery, "qryLithLogLinks", acSaveNo

End Sub