Developer Forum

I have an mdb file created by a third party tool that exports from another
database format. This tool does not create indexes for the new mdb.
Is there a way to use ADO or some other method to change the Indexed
property to True for an existing field in the table?

Thanks in advance.

I hope someone can jump in here with a more definitive answer.

I think it can be done, using ADOX. Go to msdn.micorosoft.com and search
for "ADOX Object Model", without the quotes. VB sample code can often be
easily changed to VBScript.

You might also want to Google the scripting newsgroups for ADO. Go to
Google.com, and, from the line listing type of search (Web Images
Video News Maps more ), click more which should get you a
short list which includes 'Groups'. Click Groups. In the input box to the
left of the 'search groups ' button, paste in 'ado adox group:*.scripting',
without the quotes, then click 'search groups'. You should get links to
lots of good info.

If you decide you like searching just certain newsgroups, search the
scripting groups for an HTA called googlegroups.hta. Once when I was
whining about how plain google searches for scripting-related stuff gave me
so many irrelevant hits, Michael Harris was kind enough to share this useful
tool with the newsgroup. With this tool you can even limit the results to
the author of your choice (some responders give more reliable answers than
others). Being an HTA, you can modify the list of authors or groups that
can be selected.

-Paul Randall

Thanks for the head start. Here's the solution I came up with:

'Table name to work with
tbl = "TABLE NAME"

'Define provider information
cst = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='FULL DATABASE PATH\NAME.mdb;"

'Open a connection
Set conn = CreateObject("ADODB.Connection")
conn.open cst

'Access the catalog
Set adox = CreateObject("ADOX.Catalog")
adox.ActiveConnection = conn

'Create the index information for
' the FIRST field
Set idx = CreateObject("ADOX.Index")
idx.Name = "COL1 NAME"
idx.Columns.Append "COL1 NAME"
idx.IndexNulls = 0
'Append the index information
adox.Tables(tbl).Indexes.Append idx


'Now for the SECOND field.
Set idx = Nothing
Set idx = CreateObject("ADOX.Index")
idx.Name = "COL2 NAME"
idx.Columns.Append "COL2 NAME"
idx.IndexNulls = 0
'Append the index information
adox.Tables(tbl).Indexes.Append idx

'Refresh the table
adox.Tables.Refresh


'clean up
Set idx = Nothing
Set adox = Nothing
conn.Close
Set conn = Nothing