Create table with Hyperlink field

[Greg Strong]

Hello All,

Ok I've read it is not necessarily a good ideal to create a hyperlink
field in a database, but I've tried. I've also read that a hyperlink
field is a memo field with attributes of dbHyperlinkField +
dbVariableField. So I tried using the code below, and received the error
below. Any ideals? Thanks!

,----- [ Error message on 'db.TableDefs.Append tdf' ]
| Run-time error '3010':
|
| Table 'Supplier' already exists.
`-----

=====================>Begin Code>===================================>
Sub CreateSuppliersWithHyperLink()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Suppliers")

With tdf
.Fields.Append .CreateField("SupplierID", dbLong)
.Fields.Append .CreateField("CompanyName", dbText, 40)
.Fields.Append .CreateField("ContactName", dbText, 30)
.Fields.Append .CreateField("ContactTitle", dbText, 30)
.Fields.Append .CreateField("Address", dbText, 60)
.Fields.Append .CreateField("City", dbText, 15)
.Fields.Append .CreateField("Region", dbText, 15)
.Fields.Append .CreateField("PostalCode", dbText, 10)
.Fields.Append .CreateField("Country", dbText, 15)
.Fields.Append .CreateField("Phone", dbText, 24)
.Fields.Append .CreateField("Fax", dbText, 24)
.Fields.Append .CreateField("Homepage", dbMemo)

For Each fld In .Fields
If fld.name = "Homepage" Then
Debug.Print "ok your in the loop"
fld.Attributes = dbHyperlinkField + dbVariableField
End If
Next fld

End With

db.TableDefs.Append tdf

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow

End Sub
=====================<End Code<===================================<

--
Regards,

Greg Strong

[Br]

Greg Strong wrote:

Hello All,

Ok I've read it is not necessarily a good ideal to create a hyperlink
field in a database, but I've tried. I've also read that a hyperlink
field is a memo field with attributes of dbHyperlinkField +
dbVariableField. So I tried using the code below, and received the
error below. Any ideals? Thanks!

,----- [ Error message on 'db.TableDefs.Append tdf' ]

Run-time error '3010':

Table 'Supplier' already exists.

So, the table exists already. Delete it and try again???

<>

--
regards,

Br@dley

[Allen Browne]

Greg, I suspect this is an issue of timing.

Try setting the attributes before appending the field to the table's Fields
collection:

Set fld = .CreateField("HomePage", dbMemo)
fld.Attributes = dbHyperlinkField + dbVariableField
.Append fld
--
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.

"Greg Strong" <NoJunk@NoJunk4U².com> wrote in message
news:tf********************************@4ax.com...

Hello All,

Ok I've read it is not necessarily a good ideal to create a hyperlink
field in a database, but I've tried. I've also read that a hyperlink
field is a memo field with attributes of dbHyperlinkField +
dbVariableField. So I tried using the code below, and received the error
below. Any ideals? Thanks!

,----- [ Error message on 'db.TableDefs.Append tdf' ]
| Run-time error '3010':
|
| Table 'Supplier' already exists.
`-----

=====================>Begin Code>===================================>
Sub CreateSuppliersWithHyperLink()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Suppliers")

With tdf
.Fields.Append .CreateField("SupplierID", dbLong)
.Fields.Append .CreateField("CompanyName", dbText, 40)
.Fields.Append .CreateField("ContactName", dbText, 30)
.Fields.Append .CreateField("ContactTitle", dbText, 30)
.Fields.Append .CreateField("Address", dbText, 60)
.Fields.Append .CreateField("City", dbText, 15)
.Fields.Append .CreateField("Region", dbText, 15)
.Fields.Append .CreateField("PostalCode", dbText, 10)
.Fields.Append .CreateField("Country", dbText, 15)
.Fields.Append .CreateField("Phone", dbText, 24)
.Fields.Append .CreateField("Fax", dbText, 24)
.Fields.Append .CreateField("Homepage", dbMemo)

For Each fld In .Fields
If fld.name = "Homepage" Then
Debug.Print "ok your in the loop"
fld.Attributes = dbHyperlinkField + dbVariableField
End If
Next fld

End With

db.TableDefs.Append tdf

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow

End Sub
=====================<End Code<===================================<

--
Regards,

Greg Strong

[Greg Strong]

On Mon, 20 Mar 2006 16:17:28 +1100, "Br@dley" <do***********@google.com>
wrote:

So, the table exists already. Delete it and try again???

It is deleted. I should have originally stated this explicitly. This is
why the error message is so strange. In the procedure that I run, I
delete all of the relationships and tables, then start from scratch.

Thanks!
--
Regards,

Greg Strong

[Greg Strong]

On Mon, 20 Mar 2006 13:59:12 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Try setting the attributes before appending the field to the table's Fields
collection:

Set fld = .CreateField("HomePage", dbMemo)
fld.Attributes = dbHyperlinkField + dbVariableField
.Append fld

I used your suggestion. It worked. The short answer is I think that I
may have had some conflicts with references set in my working copy of
NorthWind which is where I was doing some testing. After dumping module,
and re-importing module into fresh NW, then the code worked. The main
reason was I did this was because I couldn't find a way to create a
hyperlink field type using DDL running from code. FWIW the code is
below.

Thanks!

--
Regards,

Greg Strong

=====================>Begin Code>===================================>
Sub CreateSuppliersWithHyperLink()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fldSupplierID As DAO.Field
Dim fldCompanyName As DAO.Field
Dim fldContactName As DAO.Field
Dim fldContactTitle As DAO.Field
Dim fldAddress As DAO.Field
Dim fldCity As DAO.Field
Dim fldRegion As DAO.Field
Dim fldPostalCode As DAO.Field
Dim fldCountry As DAO.Field
Dim fldPhone As DAO.Field
Dim fldFax As DAO.Field
Dim fldHomepage As DAO.Field

Set db = CurrentDb()

On Error Resume Next
' Delete the table if it already exists.
db.TableDefs.Delete "Suppliers"
On Error GoTo 0

Set tdf = db.CreateTableDef()
tdf.name = "Suppliers"

Set fldSupplierID = tdf.CreateField("SupplierID", dbLong)
Set fldCompanyName = tdf.CreateField("CompanyName", dbText, 40)
Set fldContactName = tdf.CreateField("ContactName", dbText, 30)
Set fldContactTitle = tdf.CreateField("ContactTitle", dbText, 30)
Set fldAddress = tdf.CreateField("Address", dbText, 60)
Set fldCity = tdf.CreateField("City", dbText, 15)
Set fldRegion = tdf.CreateField("Region", dbText, 15)
Set fldPostalCode = tdf.CreateField("PostalCode", dbText, 10)
Set fldCountry = tdf.CreateField("Country", dbText, 15)
Set fldPhone = tdf.CreateField("Phone", dbText, 24)
Set fldFax = tdf.CreateField("Fax", dbText, 24)
Set fldHomepage = tdf.CreateField("Homepage", dbMemo)
fldHomepage.Attributes = dbHyperlinkField + dbVariableField

With tdf.Fields
.Append fldSupplierID
.Append fldCompanyName
.Append fldContactName
.Append fldContactTitle
.Append fldAddress
.Append fldCity
.Append fldRegion
.Append fldPostalCode
.Append fldCountry
.Append fldPhone
.Append fldFax
.Append fldHomepage
End With

With db.TableDefs
.Append tdf
.Refresh
End With

Set fldSupplierID = Nothing
Set fldCompanyName = Nothing
Set fldContactName = Nothing
Set fldContactTitle = Nothing
Set fldContactTitle = Nothing
Set fldAddress = Nothing
Set fldCity = Nothing
Set fldRegion = Nothing
Set fldPostalCode = Nothing
Set fldCountry = Nothing
Set fldPhone = Nothing
Set fldFax = Nothing
Set fldHomepage = Nothing
Set tdf = Nothing
Set db = Nothing
Application.RefreshDatabaseWindow

End Sub
=====================<End Code<=====================================<