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

jbruen wrote:

>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

--
Marsh
MVP [MS Access]

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

jbruen wrote:
>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.

--
Marsh
MVP [MS Access]

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" wrote:

> jbruen wrote:
> >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.
>
> --
> Marsh
> MVP [MS Access]
>

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]



jbruen wrote:
>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.
>
>
>> jbruen wrote:
>> >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
>>
>>
>"Marshall Barton" wrote:
>> 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.

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

"Marshall Barton" wrote:

> 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]
>
>
>
> jbruen wrote:
> >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.
> >
> >
> >> jbruen wrote:
> >> >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
> >>
> >>
> >"Marshall Barton" wrote:
> >> 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.
>
>

jbruen wrote:
>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.

--
Marsh
MVP [MS Access]

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

"Marshall Barton" wrote:

> jbruen wrote:
> >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.
>
> --
> Marsh
> MVP [MS Access]
>

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" wrote:

> jbruen wrote:
> >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.
>
> --
> Marsh
> MVP [MS Access]
>