Create Index in Table/2xxx vs 2007

[LarryP]

I'm QC'ing an older database for up-migration to 2007, and there's a VBA
procedure that creates an index in various tables on various field(s). It
was written by a former colleague, long gone, probably back in the Access
2000 or maybe even Access 98 era, and Access 2007 doesn't like it at various
points -- object not found, object not supported, yadda yadda.

Can anyone point me to a reasonably simple code snippet that will index a
table (generally speaking, newly created by VBA) on one or more fields, with
a reasonable amount of error checking for table doesn't exist, field doesn't
exist, etc? My departed colleague, bless his heart, never saw a procedure he
couldn't make more complicated, but I'm of the opposite mindset. I could
spend a day fixing this, but if there's a copy-and-paste answer I'm all for
that.

 

[Clifford Bass]

Hi Larry,

The easiest I think is to just issue an SQL create index statement.
Here is an example that uses error handling to report problems and roll back
all changes when an error happens:

=======================================

Public Sub CreateTableAndIndexes()

Dim wrkCurrent As DAO.Workspace

On Error GoTo Handle_Error

Set wrkCurrent = DBEngine.Workspaces(0)
wrkCurrent.BeginTrans
With CurrentDb
.Execute _
"create table tblCreatedInCode (" & _
"Record_ID smallint not null, " & _
"Last_Name char(30) not null, " & _
"First_Name char(30) not null)", _
dbFailOnError

.Execute _
"create unique index PrimaryKey " & _
"on tblCreatedInCode " & _
"(Record_ID) " & _
"with primary", _
dbFailOnError

.Execute _
"create index NamesKey " & _
"on tblCreatedInCode " & _
"(Last_Name, First_Name)", _
dbFailOnError
End With
wrkCurrent.CommitTrans
RefreshDatabaseWindow

Exit_Sub:
Set wrkCurrent = Nothing
Exit Sub

Handle_Error:
MsgBox "Error #" & Err.Number & ": " & Err.Description
wrkCurrent.Rollback
Resume Exit_Sub

End Sub

==========================================

And here is another method using a DAO.Index object.

==========================================

Public Sub CreatingTableAndIndexes2()

Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

On Error GoTo Handle_Error

Set td = CurrentDb.CreateTableDef("tblCreatedInCode3")
Set fld = td.CreateField("Double_Field", dbDouble)
fld.Required = True
fld.DefaultValue = 123
td.Fields.Append fld
Set fld = td.CreateField("Text_Field", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld

Set idx = td.CreateIndex("Primary")
idx.Primary = True
idx.Unique = True
Set fld = idx.CreateField("Double_Field")
idx.Fields.Append fld
td.Indexes.Append idx

CurrentDb.TableDefs.Append td
RefreshDatabaseWindow

Exit_Sub:
Set idx = Nothing
Set fld = Nothing
Set td = Nothing
Exit Sub

Handle_Error:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_Sub

End Sub

==========================================

Hope that helps,

Clifford Bass

 

[LarryP]

Thanks for your reply. I posted this quite awhile ago and since I got no
responses just kept dinking around, finally got it to work. But you are
correct in that a big part of the solution was to declare various things as
DAO.whatever, thereby getting past 2007's starting bias toward adodb. That
may be overstating it, but it was the DAO vs ADODB thing.

 

[Clifford Bass]

Hi Larry,

You are welcome. Yeh, I noticed that it was awhile ago. Glad to hear
you did get it to work Access 2007 does use both ADO and DOA under the hood.
I find it useful always to preface the DAO and ADO objects in their
declarations with ADO or DAO as appropriate even if only using one of ADO or
DAO. Then if for some reason there is a later need for both types in a
project, they will always use the proper types regardless. One of those
things learned through experience.

Clifford Bass