Field Type changing to hyperlink via VBA

[Andreas Meffert]

Hello,

How can I change the Type of a field in a table from Memo to hyperlink? I
import a table from Oracle to Access 2003. After that, the field type of
some hyperlink-fields is "Memo".
How can I schwitch back to Hyperlink via VBA because its boring doing it
manually often (I import the table each week for doing updates).
When I do it manually the hyperlink works well.

Thanks in advance.
Regards
Andreas

[David Lloyd]

Re: Field Type changing to hyperlink via VBA

Andreas:

You can access the data through the Type property of the Field object in
DAO. However, once the table is created, this property becomes read-only.


"Andreas Meffert" <andreas_mef@gm x.de> wrote in message
news:4278d8c2$0 $303$4d4ebb8e@b usinessnews.de. uu.net...[color=blue]
> Hello,
>
> How can I change the Type of a field in a table from Memo to hyperlink? I
> import a table from Oracle to Access 2003. After that, the field type of
> some hyperlink-fields is "Memo".
> How can I schwitch back to Hyperlink via VBA because its boring doing it
> manually often (I import the table each week for doing updates).
> When I do it manually the hyperlink works well.
>
> Thanks in advance.
> Regards
> Andreas
>
>[/color]

[Allen Browne]

Re: Field Type changing to hyperlink via VBA

A Hyperlink field is a Memo type field, with the Attributes set to:
dbHyperlinkFiel d + dbVariableField
but I don't think Access will let you change the attributes for an existing
field.

To set up the field programmaticall y would therefore consist of these steps:

1. CreateField() to make a new field of type dbMemo, set the Attribributes,
and Append to the Field of the TableDef.

2. Exeute an Update query statement to update the new hyperlink field with
the contents of the memo field, making sure you include the two # characters
between the components of the hyperlink.

3. Execute a DDL query statement to get rid of the original text field:
"ALTER TABLE MyTable DROP COLUMN MyMemo;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andreas Meffert" <andreas_mef@gm x.de> wrote in message
news:4278d8c2$0 $303$4d4ebb8e@b usinessnews.de. uu.net...[color=blue]
>
> How can I change the Type of a field in a table from Memo to hyperlink? I
> import a table from Oracle to Access 2003. After that, the field type of
> some hyperlink-fields is "Memo".
> How can I schwitch back to Hyperlink via VBA because its boring doing it
> manually often (I import the table each week for doing updates).
> When I do it manually the hyperlink works well.[/color]

[Steve Jorgensen]

Re: Field Type changing to hyperlink via VBA

Hyperlink fields in Access are problematic to begin with. Add ODBC links, and
they're just plain broken. Controls bound to them still appear in blue with
an underline, they no longer act like links.

What I generally do is simply give up on the hyperlink field altogether, and
just store URLs in text fields. You can use the click or double-click event
for the control and call FollowHyperlink from VB to actually open the target.

On Wed, 4 May 2005 16:14:23 +0200, "Andreas Meffert" <andreas_mef@gm x.de>
wrote:
[color=blue]
>Hello,
>
>How can I change the Type of a field in a table from Memo to hyperlink? I
>import a table from Oracle to Access 2003. After that, the field type of
>some hyperlink-fields is "Memo".
>How can I schwitch back to Hyperlink via VBA because its boring doing it
>manually often (I import the table each week for doing updates).
>When I do it manually the hyperlink works well.
>
>Thanks in advance.
>Regards
>Andreas
>[/color]

[Lyle Fairfield]

Re: Field Type changing to hyperlink via VBA

Steve Jorgensen wrote:[color=blue]
> Hyperlink fields in Access are problematic to begin with. Add ODBC links, and
> they're just plain broken. Controls bound to them still appear in blue with
> an underline, they no longer act like links.
>
> What I generally do is simply give up on the hyperlink field altogether, and
> just store URLs in text fields. You can use the click or double-click event
> for the control and call FollowHyperlink from VB to actually open the target.[/color]

I'm glad you posted this because this is what I do too, and I was
wondering between Why am I doing it that way and What have I missed in
not finding hyperlink fields so convenient to work with.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet