VBA Code to Create Index for Table Using DAO?

[Detra]

Hello,
Can someone tell me how to create an index for a table using DAO? I have a userform, with a combobox called "cmb_VENT_NAME". When the user chooses a value in the combobox, it runs a make-table query, but then the index that I had set up on the resulting table's field, "PROC_CODE" does not retain the index when the table is recreated through the make-table query. So then I tried to recreate the index after the make-table query runs, but it doesn't work. I don't get an error message or anything, but when I open the table, the index is not set.

I saw this link, but it looks like the solution is for ADO, not DAO.

http://www.mrexcel.com/forum/showthread.php?t=330637&highlight=create+index

Can someone tell me how to solve my problem using DAO? Below is my code. I am using Access 2000. Many thanks!

Private Sub cmb_VENT_NAME_AfterUpdate()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
DoCmd.SetWarnings False

List_CODE1.RowSource = ""
List_CODE2.RowSource = ""
DoCmd.Close acTable, "tbl_SER_LIST"
DoCmd.OpenQuery "qry_SER_Choose_main"

'create index
Set db = CurrentDb
Set tdf = db.TableDefs("tbl_SER_LIST")
Set idx = tdf.CreateIndex("proc_code")
idx.Primary = True
idx.Required = True
idx.IgnoreNulls = True
Set fld = idx.CreateField("proc_code", dbText)

'update list boxes
List_CODE1.RowSource = "qry_main_code1"
List_CODE2.RowSource = "qry_main_code2"
List_CODE1.Requery
List_CODE2.Requery

DoCmd.SetWarnings True
End Sub

 

[Detra]

I thought of a solution, which of course, came to me in a slap-my-forehead kind of moment hours after I posted. I was able to solve this by changing the make-table query to a delete query and then running an append query afterwards. All I would have to do is set the index the first time on the original table.

Still, it would be nice if someone could teach me how to write up code to set the index through VBA using DAO. Thank you.

 

[claymation957]

Below is the code for a function I use to do exactly what you appear to be asking for. You supply the table name when you call it, and the index field in the table must be named "Index" for it to work. Hope this helps.

Function GetTableIndex(GetTable As String) As Integer
'DATE: 04-17-08
'PURPOSE: get index number (integer) for a new record from the selected table
'IMPORTANT!- Index field in table must be named "INDEX"

'On Error GoTo ERR_HNDL

'DEFINE VARIABLES
Dim db as DAO.Database
Dim zst As DAO.Recordset 'TABLE CALLED FOR
Dim Eindex As Integer
Dim vStatusBar As String 'STATUS BAR TEXT
Dim iRetValue As Long 'FOR SOUNDS
Dim ErrorMessage as string 'for error reporting
'***********************************************

set db = current db()
Set zst = db.OpenRecordset(GetTable)
If zst.RecordCount = 0 Then
GetTableIndex = 1
Else
Eindex = DMax("[index]", GetTable) + 1
GetTableIndex = Eindex + 1
End If
'------------------------------------------------------------
EXIT_SUB: 'CLEAR VARIABLES
Eindex = 0
zst.Close
set zst = nothing
DoCmd.SetWarnings True
ErrorMessage = " "
Exit Function

ERR_HNDL:
ErrorMessage = "Function GetTableIndex" & vbcr & "ERROR# " & err.number & VBCR & err.description
msgbox (ErrorMessage)
call ErrorReport(ErrorMessage)
GOTO EXIT_SUB
End Function
'==============================================

 

[Detra]

Claymation,
Thanks! I tried it out and it worked out really well. I appreciate your helpfulness!
Detra

 

[claymation957]

Just noticed an error in the code I posted:

'as posted above, this will index by two
GetTableIndex = Eindex + 1

'it should be
GetTableIndex = Eindex