Code to add index when making linked table

[Ben Silvert]

When I link to a SQL table from the Access File-->External Data etc it gives
me a chance to designate the Primary Key. That allows the linked table to be
updatable and insertable, without the key it is read only.

What I can't seem to figure out is how to add an index to a linked table
when I'm doing it programmatically. The table gets linked without the line
tbl.Keys.Append objKey
and fails with it.

Thanks for any help on this.

Dim Con As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim objKey As ADOX.Key
Dim remotedbpath As String
Dim bol_Ret As Boolean

On Error GoTo errEH:

bol_Ret = True

'Hardcode path to SQL backend: only a DSN
remotedbpath = "ODBC;DSN=amkSQL;Trusted_Connection=Yes;DATABASE=aMK;"

With Con
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.Open "Data Source=" & Application.CodeDb.Name & ";"
End With

Cat.ActiveConnection = Con

Set tbl = New ADOX.Table
With tbl
Set .ParentCatalog = Cat
.Name = pstr_ACCESSLINKEDTableName
.Properties("Jet OLEDB:Create Link").Value = True
.Properties("Jet OLEDB:Link Provider String").Value = remotedbpath
.Properties("Jet OLEDB:Remote Table Name").Value = "dbo." &
pstr_SQL_Table
.Properties("Jet OLEDB:Cache Link Name/Password").Value = False

'This fails, will not link with an index.
If pstr_KeyField <> "" Then
Set objKey = New ADOX.Key
objKey.Name = "__uniquekey"
objKey.Type = adKeyPrimary
objKey.Columns.Append pstr_KeyField
tbl.Keys.Append objKey

End If
End With
' Append the table to the Tables collection.
Cat.Tables.Append tbl

 

[Stefan Hoffmann]

hi Ben,

When I link to a SQL table from the Access File-->External Data etc it gives
me a chance to designate the Primary Key. That allows the linked table to be
updatable and insertable, without the key it is read only.

You may use the easier way:

CurrentDb.Execute "CREATE INDEX pk_" & ADestinationName & _
" ON " & ADestinationName & "(" & APrimaryKey & _
") WITH PRIMARY;"

http://groups.google.de/group/micro...oracle+execute+stefan&rnum=4#6a40d6530a4c007f


mfG
--> stefan <--

 

[Ben Silvert]

Thanks Stefan. This worked. I used the CREATE INDEX statement and it added an
index:

Public Function testAddLinkIndex()
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim strCommand As String

With Cn
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.Open "Data Source=" & Application.CodeDb.Name & ";"
End With
With Cmd
strCommand = "CREATE INDEX pk_" & "1" & _
" ON " & "SQL_FINANCIAL_PROJECT_TRANSACTIONS" & "(" &
"Transaction_Type" & _
") WITH PRIMARY;"
.ActiveConnection = Cn
.CommandType = adCmdText
.CommandText = strCommand
.Execute

End With
End Function