DAO - Creating tables with VBA in Access

[Brad Pybus]

I am having a real hard time trying to figure out how to create a table with
VBA in MS Access '97. I have searching the MSDN Library for hours and can
not find any decent examples. They are always real simplistic examples.
Not enough detail.

Here is a code fragment that fails:

-------------
Set tdf = dbsData.CreateTableDef("MyData")

With tdf

Set idx = .CreateIndex("idxMyDataID")
idx.Primary = True

Set fld = .CreateField("MyID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField

idx.Fields.Append fld
.Indexes.Append idx

Set idx = .CreateIndex("idxAnother")
Set fld = .CreateField("Another", dbText, 7)
idx.Fields.Append fld
.Indexes.Append idx

End With

dbsData.TableDefs.Append tdf
--------------------------

When I hit the last line where the tabledef is appended to the database, I
get the following error message:

"No field defined--cannot append TableDef or Index."

I have tried several combinations and permutations and always end up with
the same error message. If I create fields with no indexes, it works just
fine.

Please help!

Brad

[Michael Harvell]

Although I can't speak for Access 97, here's a blurb right-out-a the
HELP in Access 2.0. It seems to me that it ought to work in '97. I
would sure like to know if it doesn't.

================ Begin Code Segment ====================
CreateTableDef Method Example
This example creates a new TableDef object.

Dim MyTableDef As TableDef, MyField As Field
Dim MyDatabase As Database
Dim ConnectString As String
ConnectString = "ODBC;UID=Fred;PWD=RHS;"
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
' Create new TableDef.
Set MyTableDef = MyDatabase.CreateTableDef("Customer Detail")
' Set other properties of MyTableDef.
MyTableDef.Attributes = DB_ATTACHEDODBC
MyTableDef.Connect = ConnectString
' Add field to MyTableDef.
Set MyField = MyTableDef.CreateField("MyField",DB_DATE)
MyTableDef.Fields.Append MyField
' Save TableDef definition by appending it to TableDefs collection.
MyDatabase.TableDefs.Append MyTableDef
================= End Code Segment =====================

Mike H
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

High Point, NC USA

[Ding Gung Bawm]

I have rearranged your code to following. For more infor read help on
"createfield" and "createindex"

With tdf
'create field

Set fld = .CreateField("MyID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField

.Fields.Append fld
'create another field

Set fld = .CreateField("Another", dbText, 7)

.Fields.Append fld
'create primary key index on two fields

Set idx = .CreateIndex("idxMyDataID")

With idx
.Primary = True
.Fields.Append .CreateField("MyID")
.Fields.Append .CreateField("Another")
End With
.Indexes.Append idx
'create another index on one field

Set idx = .CreateIndex("idxAnother")

With idx
.Fields.Append .CreateField("Another")
End With
.Indexes.Append idx
'refresh the collection
.Fields.Refresh
.Indexes.Refresh
End With

dbsData.TableDefs.Append tdf
'refresh the table collection
dbsData.TableDefs.Refresh
'refresh the database window
RefreshDatabaseWindow
Set tdf = Nothing

Ding Gung Bawm

Brad Pybus wrote in message ...

[Brad Pybus]

Thanks, this solved my problem. I now see what I was doing wrong. For some
reason you have to create another field with the same name as the field you
just created in order to add it to the index. None of the help documentation
I had made this clear. That is really wierd. I wonder why you can not just
add the field you already created to the index. The method below seems sort
of redundant.

By the way, you added two fields to the primary index and that gave a REALLY
goofy result. It created a table with two primary keys. And the one that
was supposed to be the real primary key showed up as not being indexed at
all. So I removed the second field from the primary key index and it worked
out just fine.

[Brad Pybus]

> Although I can't speak for Access 97, here's a blurb right-out-a the
> HELP in Access 2.0. It seems to me that it ought to work in '97. I
> would sure like to know if it doesn't.

I appreciate your desire to help me but if you would have read my whole
problem, you would have realized that I already knew how to do the example
you gave me. It was the indexes that were giving me trouble, not field
creating. The solution is given elsewhere in this thread.

Quote from my previous post:

[Ding Gung Bawm]

>By the way, you added two fields to the primary index and that gave a
REALLY
>goofy result. It created a table with two primary keys.

They are not two primary keys, rather it is composite primary key and index
on it - compose of two fields "MyId" and "Another". Combine of those fields
you get a one unique key "idxMyDataID". Composite key is used when data in
one field are not guarantee to be unique.

Depand on your situation, which is the case, you can create primary key on
one field which is unique.

Ding Gung Bawm