Okay.. So i've managed to get my head around this now.
You need to use the ADO Extension library
Firstly, add a reference to 'Microsoft ADO Ext. 2.x for DDL and Security'
The following code will output all of the information of an index.
Code:
Dim strTableName as string
Dim objCat As ADOX.Catalog
Dim objIdx As ADOX.Index
Dim objIdxCol As ADOX.Column
Dim objConn As New ADODB.Connection
objConn.ConnectionString = gstrGetConnectionString
Set objCat = New ADOX.Catalog
objCat.ActiveConnection = objConn
For Each objIdx In objCat.Tables(strTableName).Indexes
Debug.Print " Index Name: " & objIdx.Name
If objIdx.PrimaryKey = True Then
Debug.Print " PK"
End If
For Each objIdxCol In objIdx.Columns
Debug.Print " Column Name: " & objIdxCol.Name
Next
Next objIdx