Turning a table design into a table - followup question

[dbaker]

This is a follow-up to what I posted Nov. 3, 2pm about
turning a table design view into its own dataview table.
A coworker is trying the response by Bogdan (I pasted it
at the very bottom).

Coworker:
Does anyone know how to get to the text value for the
description of a field in design view?
MS Access help says you have to use the CreateProperty
method, but that doesn't give you the existing value; it
just lets you define new ones.

Here's part of the adapted code from "Bogdan Zamfir"
<[email protected]> you showed me earlier:
'--------------------------------------
For Each fld In rs1.Fields
rs2.AddNew
rs2!FieldName = fld.Name
rs2!FieldType = fld.Type
rs2!FieldSize = fld.Size
rs2!FieldDescription = fld.CreateProperty(description,
dbText)
rs2.Update
Next
'

-------------------------------------
Thanks. Here's the original message:

Subject: Re: making table design into its own table
From: "Bogdan Zamfir" <[email protected]> Sent:
11/3/2003 3:12:06 PM


You should create the table with the fields you want:
Fieldname - text
Fieldtype - integer
Fieldsize - integer

Suppose you name it TableStruc

Then you should use a code as follows

dim db as database, rs1 as recordset, fld as field, rs2 as
recordset
set db = currentdb
db.execute "Delete from TableStruc" ' to be sure
you don't merge two
tables structure
set rs1 = db.openrecordset("YourTableName")
set rs2 = db.openrecordset("TableStruc")
for each oFld in rs.Fields
rs.AddNew
rs!FieldName = oFld.Name
rs!FieldType = oFld.Type
rs!FieldSize = oFld.Size
rs.Update
next

Regards,
Bogdan
_____________________________
Freelance developer

"(e-mail address removed)" <[email protected]>
wrote in message
news:[email protected]...

 

[Bogdan Zamfir]

Hi,

You should change the code as follows:

For Each fld In rs1.Fields
rs2.AddNew
rs2!FieldName = fld.Name
rs2!FieldType = fld.Type
rs2!FieldSize = fld.Size
rs2!FieldDescription = fld.Properties("Description")
rs2.Update
Next

Bogdan

 

[Gilligan]

The following code prints the field description if one exists, if it does
not exist then it creates a field description property with the value of
"Field Description":

Private Sub ShowFieldDescriptions()
On Error GoTo Err_Label
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim lngCounter As Long
Dim strTableName As String
Dim strFieldName As String
Dim intFieldCount As Integer
Dim prp As DAO.Property
Set db = CurrentDb
For lngCounter = 1 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(lngCounter)
strTableName = tdf.Name
If UCase(Left(strTableName, 4)) = "MSYS" Then
' system table: ignore
Else
For intFieldCount = 0 To tdf.Fields.Count - 1
Set fld = tdf.Fields(intFieldCount)
Debug.Print fld.Properties("Description")
' Could retrieve other properties besides Field Description at this
point.
Next intFieldCount
End If
Next lngCounter
Exit_Label:
Exit Sub
Err_Label:
If Err.Number = 3270 Then
' 3270 is property not found, then create a Description property
' for this field called "Description" data type is text, value is
' "Field Description"
Set prp = fld.CreateProperty("Description", dbText, "Field
Description")
fld.Properties.Append prp
Resume
Else
MsgBox Err.Description
Resume Exit_Label
End If
Resume Exit_Label
End Sub

 

[Dirk Goldgar]

Hi,

You should change the code as follows:

For Each fld In rs1.Fields
rs2.AddNew
rs2!FieldName = fld.Name
rs2!FieldType = fld.Type
rs2!FieldSize = fld.Size
rs2!FieldDescription = fld.Properties("Description")
rs2.Update
Next

But you'll want to trap and ignore the error that will be raised if fld
has no Description property set.

 

[Bogdan Zamfir]

Good point.
My code was not tested, just typed it here

Bogdan