How to edit hyperlinks in Access field to display only encapsulated address

[ree8281]

I have a huge database that contains hyperlinks to many different Survey Monument Images. The current database has an "Alias" address and an #encapsulated# link address. I need to be able to bring the Access DB into ArcMap, so the alias's need to be taken out completely. I really do not have much experience using VBA in Access and was wondering if anyone would be able to help me figure out how to get this to work. The field is named "Picture_c" and the table name is "_ALL.

[ADezii]

I have a huge database that contains hyperlinks to many different Survey Monument Images. The current database has an "Alias" address and an #encapsulated# link address. I need to be able to bring the Access DB into ArcMap, so the alias's need to be taken out completely. I really do not have much experience using VBA in Access and was wondering if anyone would be able to help me figure out how to get this to work. The field is named "Picture_c" and the table name is "_ALL.

Just subscribing for now, will return.

1. By Alias, do you mean the Text displayed for the Hyperlink?
2. If the above is correct, you want the actual Link itself as previously indicated?

[NeoPa]

Check out How to Programmaticall y Create a Hyperlink on a Form.

Welcome to Bytes!

[ree8281]

Just subscribing for now, will return.

1. By Alias, do you mean the Text displayed for the Hyperlink?
2. If the above is correct, you want the actual Link itself as previously indicated?

To ADezii:
Thank you for responding.
On questions 1 & 2: Yes, I mean that I only want the field to display the actual address (path) to the file, not the "alias" name that appears before the hyperlink. When I take the table and import it into, for instance, ArcMap and view the attribute table, the links do not work inside ArcMap because the alias is displayed in front of the encapsulated address.

To NeoPa:
Thank you for the link, however, I already have populated values in the DB for the hyperlink field and would like to only modify them to display only the actual LINK. Also, I do not have a spreadsheet that I am importing data from - I have a technician that hand enters the original data given to him from the field survey crews.

[ADezii]

To ADezii:
Thank you for responding.
On questions 1 & 2: Yes, I mean that I only want the field to display the actual address (path) to the file, not the "alias" name that appears before the hyperlink. When I take the table and import it into, for instance, ArcMap and view the attribute table, the links do not work inside ArcMap because the alias is displayed in front of the encapsulated address.

To NeoPa:
Thank you for the link, however, I already have populated values in the DB for the hyperlink field and would like to only modify them to display only the actual LINK. Also, I do not have a spreadsheet that I am importing data from - I have a technician that hand enters the original data given to him from the field survey crews.

I threw this together before running out the door, but it should work. I assumed that you needed the '#'s' in the actual Link, if you don't, just modify the code. The code extracts the actual Hyperlink Address from the Link, then Updates the [Picture_c] Field with it.

Dim MyDB As Dao.Database
Dim rst As Dao.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("_ALL", dbOpenDynaset)

With rst
  Do While Not .EOF
    .Edit
      ![Picture_c] = "#" & HyperlinkPart(![Picture_c], acAddress) & "#"
    .Update
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[ree8281]

Hi Dezii,
Thanks for writing that code for me. However, I am getting an error when I run the script.

I was hoping you could help me troubleshoot the script.

My first question is...

Should I be changing the "MyDB" value to the name of the database (Control 05-20-10), at every instance that it occurs within the script? I did try doing this as well and it still came back with an error. I took a screenshot of the error, WITHOUT CHANGING THE CODE, if you'd like to see it.

My second question is procedural in nature....

I open my mdb file (Control 05-20-10.mdb), I open my table (_All), I go to <Tools><Macro>< Visual Basic Editor>. Once I am in Visual Basic Editor, I go to <Insert><Module >, I cut-and-paste your code into the module window(which has some text already in the module window before I cut-and-paste which says
"Option Compare Database
Option Explicit"). I then select <Run><Run Sub/UserForm>, I type in the name of the macro (I called it 'ExpelAlias'), then I selet <Create>. I then select <Run><Run Sub/UserForm> and it immediately pops up an error message that says:
"Compile Error: Invalid outside procedure"

Any thoughts? I did notice there were white spaces after all the lines, were those intentional? I tried taking those out and it still gave me the same error message. Thanks for taking the time to help me with this.

[ADezii]

Hi Dezii,
Thanks for writing that code for me. However, I am getting an error when I run the script.

I was hoping you could help me troubleshoot the script.

My first question is...

Should I be changing the "MyDB" value to the name of the database (Control 05-20-10), at every instance that it occurs within the script? I did try doing this as well and it still came back with an error. I took a screenshot of the error, WITHOUT CHANGING THE CODE, if you'd like to see it.

My second question is procedural in nature....

I open my mdb file (Control 05-20-10.mdb), I open my table (_All), I go to <Tools><Macro>< Visual Basic Editor>. Once I am in Visual Basic Editor, I go to <Insert><Module >, I cut-and-paste your code into the module window(which has some text already in the module window before I cut-and-paste which says
"Option Compare Database
Option Explicit"). I then select <Run><Run Sub/UserForm>, I type in the name of the macro (I called it 'ExpelAlias'), then I selet <Create>. I then select <Run><Run Sub/UserForm> and it immediately pops up an error message that says:
"Compile Error: Invalid outside procedure"

Any thoughts? I did notice there were white spaces after all the lines, were those intentional? I tried taking those out and it still gave me the same error message. Thanks for taking the time to help me with this.

The reason that you are getting this specific error is that you Pasted the code into the Declarations Section of a Code Module. Do not change anything, simply Copy the code to the Click() Event of a Command Button to see it work.

[NeoPa]

My apologies. I had intended to supply Error 7980: HyperlinkAddres s or HyperlinkSubAdd ress read-only for Hyperlink as the link but popped in the wrong one.

In it you can find out how hyperlinks are stored textually, and from there it is pretty straightforward to extract the element you require. I suggest the use of the command :

Split(YourHyperLink, "#")(X)

Where X (0, 1 or 2) specifies which element you are interested in.

[ree8281]

Dezii:
First of all, thank you for being so responsive. However, I am getting an error message now that says "Compile Error: User-defined type not defined." Can you walk me through this. I'm sorry for my ignorance on this topic. Let me tell you what I did first.

I went into Access, opened my form in Design view, when to the "picture_c" field box, right-clicked in the field box, selected <Build Event> (See Step 1 in zip file). It pops up a "Choose Builder" options box, I chose <Code Builder> then hit <OK> (See Step 2 in zip file). Microsoft Visual Basic opens up and I pasted the code into there (See Step 3 in zip file). I then closed Microsoft Visual Basic. I went back to Access and opened up my form. I then went to a record in the form that has a value in the "picture_c" field, clicked on the hyperlink and it brought me back to Microsoft Visual Basic with that error message that I referenced in the first paragraph.

What am I doing wrong?

[ADezii]

Dezii:
First of all, thank you for being so responsive. However, I am getting an error message now that says "Compile Error: User-defined type not defined." Can you walk me through this. I'm sorry for my ignorance on this topic. Let me tell you what I did first.

I went into Access, opened my form in Design view, when to the "picture_c" field box, right-clicked in the field box, selected <Build Event> (See Step 1 in zip file). It pops up a "Choose Builder" options box, I chose <Code Builder> then hit <OK> (See Step 2 in zip file). Microsoft Visual Basic opens up and I pasted the code into there (See Step 3 in zip file). I then closed Microsoft Visual Basic. I went back to Access and opened up my form. I then went to a record in the form that has a value in the "picture_c" field, clicked on the hyperlink and it brought me back to Microsoft Visual Basic with that error message that I referenced in the first paragraph.

What am I doing wrong?

Set a Reference to the Microsoft DAO X.X Object Library. If that doesn't do it, see the Attachment.