Createfield with attributes

[Guest]

Is there any way with the following logic to set the following attributes for
this fields: Required, Allow Zero Length, Indexed, Format

Dim MyDatabase As Database, NewTable As TableDef
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
Set NewTable = MyDatabase.CreateTableDef("TabNewWiring2")
With NewTable
.Fields.Append .CreateField("RefNO", dbText)
.Fields.Append .CreateField("RefFlag", dbBoolean)
.Fields.Append .CreateField("RefDate", dbDate)
End With
MyDatabase.TableDefs.Append NewTable

Thanks
John

 

[Marshall Barton]

Is there any way with the following logic to set the following attributes for
this fields: Required, Allow Zero Length, Indexed, Format

Dim MyDatabase As Database, NewTable As TableDef
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
Set NewTable = MyDatabase.CreateTableDef("TabNewWiring2")
With NewTable
.Fields.Append .CreateField("RefNO", dbText)
.Fields.Append .CreateField("RefFlag", dbBoolean)
.Fields.Append .CreateField("RefDate", dbDate)
End With
MyDatabase.TableDefs.Append NewTable

Here's some air code that might get you started. Check Help
for details
Set fld = .CreateField("fldRefNO")
fld.Required = True
fld.AllowZeroLength = False
Set prp = fld.CreateProperty("Format",dbText,"@@-@@@@")
fld.Append prp
..Fields.Append fld
Set idx = .CreateIndex("idxRefNO")
Set fldx = idx.CreateField("RefNo")
idx.Append fldx
..Indexes.Append idx

 

[Guest]

Marshall

I copied the following function from help changing the "Set MyDatabase" from
"Northwind.mdb" to what I currently have.

When I run this I get the following error message on this statement:
Set fldTemp = tdfEmployees.CreateField("FaxPhone", dbText, 24)
"Run-time error '3265': Item not found in this collection."

Could you please explain why and provide some help please?

Function CreateTable1()
Dim MyDatabase As Database, tdfEmployees As TableDef
Dim rstEmployees As Recordset
Dim fldTemp As Field
Dim prp As Property

Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
On Error GoTo CreateTabledef1
DoCmd.DeleteObject acTable, "Employees"
CreateTabledef1:
On Error GoTo CreateTableErr1
Set tdfEmployees = MyDatabase.TableDefs("Employees")
Set fldTemp = tdfEmployees.CreateField("FaxPhone", dbText, 24)
fldTemp.AllowZeroLength = True
tdfEmployees.Fields.Append fldTemp

DoCmd.SetWarnings True
Exit Function

CreateTableErr1:
Stop
End Function

Thanks again
John

 

[Marshall Barton]

I copied the following function from help changing the "Set MyDatabase" from
"Northwind.mdb" to what I currently have.

When I run this I get the following error message on this statement:
Set fldTemp = tdfEmployees.CreateField("FaxPhone", dbText, 24)
"Run-time error '3265': Item not found in this collection."

Could you please explain why and provide some help please?

Function CreateTable1()
Dim MyDatabase As Database, tdfEmployees As TableDef
Dim rstEmployees As Recordset
Dim fldTemp As Field
Dim prp As Property

Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
On Error GoTo CreateTabledef1
DoCmd.DeleteObject acTable, "Employees"
CreateTabledef1:
On Error GoTo CreateTableErr1
Set tdfEmployees = MyDatabase.TableDefs("Employees")
Set fldTemp = tdfEmployees.CreateField("FaxPhone", dbText, 24)
fldTemp.AllowZeroLength = True
tdfEmployees.Fields.Append fldTemp

DoCmd.SetWarnings True
Exit Function

CreateTableErr1:
Stop
End Function

I don't know why you're getting that message on that line.
I would have expected the Set tdfEmployees line to fail
because you just deleted the table.

 

[Guest]

Marshall

Under references I have the following checked if that helps:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.6 Library

I put the delete in there since I thought TableDefs is like a CreateTable.

John

 

[Marshall Barton]

No, a TableDef is the object for an existing table. If you
delete it, the reference to the TableDef object will fail
because it doesn't exist.

The other way around is to leave the delete in there and
alway use the CreateTableDef method, but this would lose any
data records that were in the table.

 

[Guest]

Marshall

I add the following statment after the CreateTabledef1 label and received
the same error: Set tdfEmployees = MyDatabase.CreateTabledef("Employees").

Do I have to create the table the original way using ".Fields.Append
..CreateField("RefNO", dbText)", then use the tabledefs statement to change
the attributes? If so I guess I can't do this at the same time, it has to be
a 2 step process.

This is really a onetime process to create the table for a conversion. That
is why I have to delete in there to remove the table before I do create it.
If I have a lot of fields instead of creating them manually, I would rather
do something like this if possible.

John

No, a TableDef is the object for an existing table. If you
delete it, the reference to the TableDef object will fail
because it doesn't exist.

The other way around is to leave the delete in there and
alway use the CreateTableDef method, but this would lose any
data records that were in the table.
--
Marsh
MVP [MS Access]

Under references I have the following checked if that helps:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.6 Library

I put the delete in there since I thought TableDefs is like a CreateTable.

 

[Marshall Barton]

I add the following statment after the CreateTabledef1 label and received
the same error: Set tdfEmployees = MyDatabase.CreateTabledef("Employees").

Do I have to create the table the original way using ".Fields.Append
.CreateField("RefNO", dbText)", then use the tabledefs statement to change
the attributes? If so I guess I can't do this at the same time, it has to be
a 2 step process.

This is really a onetime process to create the table for a conversion. That
is why I have to delete in there to remove the table before I do create it.
If I have a lot of fields instead of creating them manually, I would rather
do something like this if possible.

Since you deleted the TableDef, then yes,when you
(re)create the TableDef, you have to r(e)create the fields,
properties, indexes, etc.

If this is a "one" time operation, how about creating the
table manually using the table design window? When you want
to test you can Copy/Paste the "template" table (in the DB
window) to make a fresh version of the working table.

 

[Guest]

Marshall

The reason I'm trying to do this is I want to create a table in code instead
of from the design view with the correct properties.

If i use the CreateTable first and then call the code below, I receive:
"There are several tables with that name. Please specify owner in the format
'owner.table'."Set NewTable = MyDatabase.TableDefs("TabNewWiring2")"
statement.

If I take an existing table and remove all the fields except for 1, run the
following it works. If I create a table from scratch in the design view with
1 field and run the same thing, I recieve "Item not found in this collection"
on the "Set NewTable = MyDatabase.TableDefs("TabNewWiring2") statement. What
is the reason for this?

Function CreateOthers()
Dim MyDatabase As Database, NewTable As TableDef
Dim fldTemp As Field
On Error GoTo CreateOtherdef
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
Set NewTable = MyDatabase.TableDefs("TabNewWiring2")
' Create a new Field object and append it to the Fields
' collection of the Employees table.
Set fldTemp = NewTable.CreateField("Field2", dbText, 10)
fldTemp.AllowZeroLength = True
NewTable.Fields.Append fldTemp

DoCmd.SetWarnings True
Exit Function

CreateOtherdef:
Debug.Print Error$
Stop
End Function

 

[Guest]

Marshal

Once I changed the Set MyDB from DBEngine.Workspaces(0).Databases(0) to
OpenDatabase("h:\access\hsbb\HSBB.mdb"), everything worked fine. I was able
to use the following:

Function CreateTable()
Dim MyDatabase As Database, NewTable As TableDef

Set MyDatabase = OpenDatabase("h:\access\hsbb\HSBB.mdb")
DoCmd.SetWarnings False
On Error GoTo CreateTabledef
DoCmd.DeleteObject acTable, "TabNewWiring"
CreateTabledef:
On Error GoTo CreateTableErr
Set NewTable = MyDatabase.CreateTabledef("TabNewWiring")
With NewTable
.Fields.Append .CreateField("RefNO", dbLong)
End With
MyDatabase.TableDefs.Append NewTable
Call CreateOthers
DoCmd.SetWarnings True
Exit Function
CreateTableErr:
Stop
End Function

Function CreateOthers()
Dim MyDatabase As Database, NewTable As TableDef
Dim fldTemp As Field
On Error GoTo CreateOtherdef
Set MyDatabase = OpenDatabase("h:\access\hsbb\HSBB.mdb")
Set NewTable = MyDatabase.TableDefs("TabNewWiring")
Set fldTemp = NewTable.CreateField("NewDConnections", dbBoolean)
NewTable.Fields.Append fldTemp
DoCmd.SetWarnings True
Exit Function
CreateOtherdef:
DoCmd.SetWarnings True
Debug.Print Error$
Stop
End Function

John

 

[Marshall Barton]

It sounds like you got it to work.

You've kind of lost me with all the different things you've
tried. Just remember that you can only use CreateTableDef
if it doesn't already exist. And you can only reference an
already existing TableDef.

What I was suggesting before by precreating a table in
design view is to then use it as a "template". Then when
you wanted a fresh copy of the empty table with all its
fields and their properties, all you would have to do is
delete any existing copy and make a new copy from the
"template" using:
DoCmd.CopyObject , TabNewWiring, acTable, TemplateTable