How to add a hyperlink field to an Access 2007 table using ADOX where the table already exists?

I have the following code and am I trying to add a hyperlink field to an already existing table.  

Most ADOX examples show adding a table and then appending fields - I have to do so to an already existing table.  That and many examples do not incorporate late binding.  

When I try to run the following I get an error - Item cannot be found in the collection corresponding to the requested name or ordinal.  Run - time error 3265 at line item - .Columns(sFld).Properties("Jet OLEDB:Hyperlink") = True.  

In the code, I'm passing the table name - sTbl and the field name sFld.

Even if I change it to the following I still get an error - 438 - object doesn't support this property or method.

With .Columns
        .Append sFld, adLongVarWChar       'Memo (for hyperlink)
        .Properties("Jet OLEDB:Hyperlink") = True
      End With

Thank you in advance for your time!
Function CreateTableAdox_Hyperlink(sTbl As String, sFld As String)
    'Purpose:   Create a table with various field types, using ADOX.
    
    Dim cat As Object
    Dim tbl As Object
    
    'Dim cat As New ADOX.Catalog
    'Dim tbl As ADOX.Table
    
    Set cat = CreateObject("ADOX.Catalog")
    cat.ActiveConnection = CurrentProject.Connection
    'Set cat.ActiveConnection = CurrentProject.Connection
    'Initialize the Contractor table.
    Set tbl = CreateObject("ADOX.Table")
    'Set tbl = New ADOX.Table
    tbl.name = sTbl
    
    'Append the columns.
    With tbl
      
      With .Columns
        .Append sFld, adLongVarWChar       'Memo (for hyperlink)
        
      End With
      
      .Columns(sFld).Properties("Jet OLEDB:Hyperlink") = True

    End With
    
    Set tbl = Nothing
    Set cat = Nothing
    
End Function

Open in new window

LVL 1
stephenlecomptejrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Somewhere in an obscure storage vault there is documentation that a hyperlink field is really three text strings separated by semi-colons.
In VBA, hyperlink is a field property and has subproperties.
So a Dim hypText as Hyperlink is valid  and
hypText.address = "http://experts-exchange.com"   'sets the main adress
hypText.subaddress = "expertsZone.jsp"  "sets a sub address and
hypText.TextToDisplay = "Click Here" 'Present a link to the user
0
stephenlecomptejrAuthor Commented:
DoDahD,

Coding is sometimes using the right or wrong syntax and then proper placement is required.  How may I adjust .Columns(sFld).Properties("Jet OLEDB:Hyperlink") = True to allow me to add the new hyperlink field?
Do I have it in the wrong spot?

Nothing you state above helps me solve my problem!
0
Richard DanekeTrainerCommented:
Sorry, I was confused with your code.  I thought you would need to sent TableDef properties and used the .CreateFields option.   Do you have a sample of this code working for other field types?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
stephen,

is it necessary that you use ADOX?
i normally use DAO when doing this

sub AddHyperlinkFld(sTbl as string, sFld as string)
dim db as dao.database, td as dao.tabledef, fld as dao.field
set db=currentdb
set td=db.tabledefs(stbl)
set fld=td.createfield(sfld,dbmemo)
fld.attributes=dbhyperlinkfield

db.close
end sub
0
stephenlecomptejrAuthor Commented:
capricorn1,

I already tried the DAO code but when I open the table the fields are still not created.
Um...probably because I already have a Set db=current db and I'm looping through another table that has the fields I need.

Basically by looping through the temp table -I"m creating fields in the other table.  Thus I need something else besides DAO to add the link.

Unless you can show me a way to add the hyperlink fields that should be added in a collection and after the looping process go back and add the hyperlink fields...here's my code that I'm using to call AddHyperlink
Private Sub Add_Fields(sTableFrom As String, sTableTo As String)
On Error GoTo Err_This

  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim tdfC As DAO.TableDef
  Dim fldC As DAO.Field
  Dim intFieldType As Integer
  Dim strTypeName As String
  Dim strFieldName As String
  
  Dim intCounter As Integer
  Dim sSQL As String
  
  Set db = CurrentDb

  Set tdf = db.TableDefs(sTableFrom)

  ' Loop through all the fields (columns)
  For Each fld In tdf.Fields

      strFieldName = fld.name
      intFieldType = fld.Type
    
      Select Case intFieldType
        
        Case 2    'Byte
          strTypeName = "YESNO"
        Case 3    'Integer
          strTypeName = "INTEGER"
        Case 4    'Long
          strTypeName = "LONG"
        Case 6    'Single
          strTypeName = "SINGLE"
        Case 7    'Double
          strTypeName = "DOUBLE"
        Case 12
          strTypeName = "HYPERLINK"
        Case 10
          strTypeName = "TEXT"
        Case Else 'Not a Number
          strTypeName = "TEXT"
      End Select
      
      If strTypeName <> "N/A" Then
      
        If strTypeName = "HYPERLINK" Then
          
           'Call CreateTableAdox_Hyperlink(sTableTo, strFieldName)
           Call AddHyperlinkFld(sTableTo, strFieldName)
           
           
        Else
      
          If TableContainsField(sTableTo, strFieldName) = False Then
        
          sSQL = "ALTER TABLE [" & sTableTo & "] ADD COLUMN [" & strFieldName & "] " & strTypeName
          
          If strTypeName = "TEXT" Then
          
            If Left(strFieldName, 2) = "yn" Then
              sSQL = "ALTER TABLE [" & sTableTo & "] ADD COLUMN [" & strFieldName & "] " & strTypeName & " (1)"
            Else
              sSQL = "ALTER TABLE [" & sTableTo & "] ADD COLUMN [" & strFieldName & "] " & strTypeName & " (25)"
            End If
            If strFieldName = "Manuf" Or strFieldName = "Model_no" Or strFieldName = "fJPG" Then
              sSQL = "ALTER TABLE [" & sTableTo & "] ADD COLUMN [" & strFieldName & "] " & strTypeName & " (255)"
            End If
            
          End If
          
          Submit_Msg ("Adding field: " & strFieldName)
          CurrentDb.Execute sSQL
          
          End If
        
        End If
        
    End If
    
  Next
  
  
  Set fld = Nothing
  Set tdf = Nothing
  Set db = Nothing
  
Exit_This:
  On Error Resume Next
  Exit Sub
  
Err_This:
  Err = 0
  Resume Exit_This
End Sub

Open in new window

0
stephenlecomptejrAuthor Commented:
Even when I change to it below it still doesn't work.
Sub AddHyperlinkFld(sTbl As String, sFld As String)

Dim dbAdd As DAO.Database, tdAdd As DAO.TableDef, fldAdd As DAO.Field
Set dbAdd = CurrentDb
Set tdAdd = dbAdd.TableDefs(sTbl)
Set fldAdd = tdAdd.CreateField(sFld, dbMemo)
fldAdd.Attributes = dbHyperlinkField

dbAdd.Close

Set tdAdd = Nothing
Set dbAdd = Nothing

End Sub

Open in new window

0
stephenlecomptejrAuthor Commented:
DoDahD,

http://www.everythingaccess.com/tutorials.asp?ID=ADOX-programming-examples

I'm not sure how to adjust the above to give me the correct syntax utilizing ADOX!
0
Rey Obrero (Capricorn1)Commented:
try the codes by itself and see the result.. tested it and it is working.
0
stephenlecomptejrAuthor Commented:
capricorn1,

I've put the code in a public module and did - Call AddHyperlinkFld("PROJ_ME", "webdata") in the Immediate window and even changed it to give me a message box - then when I go into PROJ_ME.... it still doesn't add the field in the PROJ_ME table.

Could you please look at the hyperlink I gave DoDahD and edit my AddHyperlinkCode?
Option Compare Database
Option Explicit

Public Sub AddHyperlinkFld(sTbl As String, sFld As String)

Dim dbAdd As DAO.Database, tdAdd As DAO.TableDef, fldAdd As DAO.Field
Set dbAdd = CurrentDb
Set tdAdd = dbAdd.TableDefs(sTbl)
Set fldAdd = tdAdd.CreateField(sFld, dbMemo)
fldAdd.Attributes = dbHyperlinkField

dbAdd.Close

Set tdAdd = Nothing
Set dbAdd = Nothing

MsgBox ("Finished adding hyperlink")

End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
are your doing this in a local table?
0
stephenlecomptejrAuthor Commented:
yes. It's not linked.
0
Rey Obrero (Capricorn1)Commented:
my mistake, sorry i am missing one line

sub AddHyperlinkFld(sTbl as string, sFld as string)
dim db as dao.database, td as dao.tabledef, fld as dao.field
set db=currentdb
set td=db.tabledefs(stbl)
set fld=td.createfield(sfld,dbmemo)
fld.attributes=dbhyperlinkfield
td.fields.append fld       '<<<< forgot to append the field
db.close
end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenlecomptejrAuthor Commented:
Caps,

You the only one ever can help me on this site!   Much appreciation from the heart for help saving my job many times!

0
stephenlecomptejrAuthor Commented:
Capricorn, is a blessing and the only one makes this site worth visiting and paying for!
0
stephenlecomptejrAuthor Commented:
Well I shouldn't say the only one -not to make others jealous.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.