Define a primary key during table generation

[Eric Lachowitz]

I wrote this code snippet for creating a table and want to add a
primary key... how/where in this code should I do so?

Sub Create_Table()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim tbl_NAME, fld_Name As String
Dim fld_TYPE As DataTypeEnum
Dim i, fld_SIZE, fld_POSITION, fld_QTY As Integer
Dim fld_REQUIRED, fld_ZERO_LENGTH As Boolean
tbl_NAME = "tbl_<YOUR TABLE NAME>" 'UPDATE: to reflect the name of
the table
Set db = CurrentDb()
Set tbl = db.CreateTableDef(tbl_NAME)
fld_QTY = 5 'UPDATE: the fld_Qty variable to reflect the number of
"Choose" statements below
For i = 1 To fld_QTY
fld_Name = Choose(i, "Field1", "Field2", "Field3", "Field4",
"Field5")
fld_TYPE = Choose(i, dbText, dbText, dbText, dbText, dbText)
'SEARCH: "Type Property (DAO)" for a list of types
fld_SIZE = Choose(i, 255, 255, 255, 255, 255)
fld_POSITION = Choose(i, 0, 1, 2, 3, 4)
fld_REQUIRED = Choose(i, False, False, False, False, False)
fld_ZERO_LENGTH = Choose(i, True, True, True, True, True)
Set fld = tbl.CreateField(fld_Name, fld_TYPE, fld_SIZE)
With fld
.OrdinalPosition = fld_POSITION
.Required = fld_REQUIRED
.AllowZeroLength = fld_ZERO_LENGTH
End With
tbl.Fields.Append fld
Next
db.TableDefs.Append tbl
End Sub

 

[Douglas J. Steele]

No offense, but that's really ugly code! <g>

First piece of advice: disambiguate your references. You're using DAO.
However, the Field data type exists in more than one model (it's also in the
ADO model), so you should replace the 1st three declarations with:

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

To add a primary key, add the following declaration:

Dim idx As DAO.Index

Then, after your db.TableDefs.Append tbl statement, add

Set idx = tbl.CreateIndex("PrimaryKey")
With idx
.Fields.Append .CreateField("Field1")
End With
idx.Primary = True
tbl.Indexes.Append idx

This assumes that your primary key should be Field1. If you need more than
one field in the pimary key, simply add more appends:

With idx
.Fields.Append .CreateField("Field1")
.Fields.Append .CreateField("Field2")
End With

Note that the name of the index (PrimaryKey above) isn't important: Access
always names its PKs PrimaryKey, so I simply followed suit.

 

[Eric Lachowitz]

No offense taken... and thank you for the detailed reply.
I was under the impression that if "ActiveX Data Objects - ADO" was
not selected in the references pane, then it wouldn't matter to add
"DAO" to my declaration statements (for this database, I will only be
in the realm of "DAO"). Nonetheless, I will adopt your new practice
for clarity as it makes sense.

Question: In terms of "ugly", did the wealth of "Choose" statements
add to my code's unsightliness? What other suggestions do you
recommend for building an array such as this? I would use the
Microsoft Scripting Runtime / Scripting.Dictionary, but it is too
slow. Your ideas?

 

[Granny Spitz via AccessMonster.com]

I wrote this code snippet for creating a table and want to add a
primary key... how/where in this code should I do so?

If you're looking for speed, both in writing the code and executing it, this
is it:

CurrentDb.Execute "CREATE TABLE tbl_NAME " & _
"(Field1 Text (255) NOT NULL, Field2 Text (255), " & _
"Field3 Text (255), Field4 Text (255), " & _
"Field5 Text (255), " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (Field1));", _
dbFailOnError

Your code snippet sets the AllowZeroLength property to true, which it already
is by default. If you wanted to set it to false you'd need a little more
code because SQL doesn't include a way to disallow zero length strings. In
that case you could do this:

Dim dbs As Database
Dim td As TableDef
Dim fd As DAO.Field

CurrentDb.Execute "CREATE TABLE tbl_NAME " & _
"(Field1 Text (255) NOT NULL, Field2 Text (255), " & _
"Field3 Text (255), Field4 Text (255), " & _
"Field5 Text (255), " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (Field1));", _
dbFailOnError

Set dbs = CurrentDb
Set td = dbs("tbl_NAME")
For Each fd In td.Fields
If (fd.Type = dbText) Then
fd.AllowZeroLength = False
End If
Next fd

Set fd = Nothing
Set td = Nothing
Set dbs = Nothing

 

[Eric Lachowitz]

Very insightful Granny! I really appreciate your post and will now
travel solely down the SQL road for all of my "Create Table" needs
(looking at your code, it does make for speedier and no-so-ugly
viewing). Question: Knowing little about the differences between ADO
and DAO, which is YOUR preference for dealing with tables/datasets?
From reading many newsgroup posts, it seems that many people like
using ADOX... your thoughts?

 

[Granny Spitz via AccessMonster.com]

which is YOUR preference for dealing with tables/datasets?

I use SQL whenever I can. If I'm dealing with Jet I nearly always use DAO
when SQL can't do what I want. When dealing with tables in another database
engine, I use ADO and ADOX if SQL alone isn't enough. Needless to say, I'd
rather use queries and passthrough queries than use VBA code.

From reading many newsgroup posts, it seems that many people like
using ADOX... your thoughts?

ADOX is still a little buggy, but it works for most normal activities you
would use it for in other database engines. It wasn't designed specifically
for Jet like DAO was, so it doesn't have all the same capabilities.

 

[Eric Lachowitz]

More often then not, I will be dealing with JET and remain within the
DAO constraint. However, I will adopt your principle of using SQL
whenever possible (using DAO as a backup). Thanks again...

 

[Jamie Collins]

[ADOX] wasn't designed specifically
for Jet like DAO was, so it doesn't have all the same capabilities.

DAO was designed specifically for Jet 3.n (circa 1996?), so it doesn't
have all the Jet 4.0 capabilities

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

DAO was designed specifically for Jet 3.n (circa 1996?), so it doesn't
have all the Jet 4.0 capabilities

True, which is why mastering ADO, ADOX and DAO is worthwhile even if you
prefer one library over another. You'll know which library is best to use in
any given situation.