Alter column hyperlink problem

[vvilan]

Hi, I am very new to access and I have to create this application for work. The application imports information from a text document and transfers it into an access database. One such column is a Link such as \\computer\folder1\file.pdf. Setting this column as hyperlink does not hyperlink the file to anything. However a solution is to import it as a txt and then change the field type to hyperlink. And now here is my question:

How do you use SQL or a query to change the data type of a field to hyperlink?

ALTER TABLE [tablename] ALTER COLUMN [columnname] Hyperlink; does not work.

THANKS

 

[Remou]

Are you sure you wish to do this? Hyperlink columns are a nuisance to maintain for the most part. Presumable you and your users will be using a form to view the data, do FollowHyperlink in a suitable event, DblClick, for example will work very nicely.

 

[vvilan]

Well, I am a fairly new user and this thing that i'm making isn't a big database but just a way to organize and search for the massive amount of data that exists. I would like to hyperlink it but how can this be achieved in VBA or some other way such that it can be done automatically?

 

[Remou]

Create a form of any description: Continuous, Single

Add some code to the DblClick event for the control with the hyperlink address:

If Trim(Me.[Name Of Control] & "") <> "" Then
   FollowHyperlink Me.[Name of Control]
Else
   MsgBox "Nothing to do."
End If

If you wish to use the default email programme:

If Trim(Me.[Name Of Control] & "") <> "" Then
   FollowHyperlink "MailTo:" & Me.[Name of Control]
Else
   MsgBox "Nothing to do."
End If

It is possible to make a field a hyperlink by changing the attributes, but hyperlink fields are a nuisance in a number of ways. For example, if you wish to view the data in a query, you end up with something like:

http://tek-tips.com#http://tek-tips.com#

 

[vvilan]

Thanks for the help but I'm new and I don't understand that syntax. I don't even know what are controls. The best solution for me is to find a working statement that does this:

ALTER TABLE [tablename] ALTER COLUMN [columnname] Hyperlink;

However, I'm starting to doubt that is possible. Anyways basically I call a search query and it returns a whole bunch of links in a table ex:

//server/computer/folder/file1.txt
//server/computer/folder/file2.txt
//server/computer/folder/file3.txt

And they are supposed to be linked directly to the files.

 

[vvilan]

EDIT:

\\server\computer\folder\file1.txt
\\server\computer\folder\file2.txt
\\server\computer\folder\file3.txt

I meant backslashes

 

[Remou]

A control is what displays a field when you you create a form. There are wizards that do most of this for you. There is no SQL statement that will change a field to a hyperlink field, a hyperlink is a memo with a different Attributes property, you need DAO to create one.

Sub Hyper()
'Needs reference to Microsoft DAO 3.x Object Library
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As DAO.Database

Set db = CurrentDb
Set tdf = db.TableDefs("NameOfTable")
Set fld = tdf.CreateField("NameForNewField", dbMemo)
fld.Attributes = fld.Attributes + dbHyperlinkField
tdf.Fields.Append fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[vvilan]

Big thanks to that function but my problem is to change the data type of an existing column to hyperlink rather than to instantiate a column with datatype hyperlink.

 

[Remou]

That cannot be done, as far as I know. You will need to create a new column then run an update query based on the existing column, eg:

Update tblImp Set NewField=OldField & "#" & OldField & "#"

This is why I suggested a form, which is what Access is good at.