by Hello ADO Gurus
I've written some code to go through my Oracle database and create linked
tables, or if necessary to update the link properties. This works fine,
however I need to create indexes on the linked tables to allow my apps to
update records through Access. Try as I might, every time I try to
programmatically create an index for a linked table the procedure fails,
with "Invalid Argument".
Has anyone out there run into this before? I'd really like my app to be able
to generate new linked tables with indexes, such that the underlying tables
in oracle can be edited through Access.
Thanks in Advance
- John
Sub RefreshLinks(strDBLinkFrom As String, _
strDBLinkSource As String)
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
Dim idx As ADOX.Index
Dim columns As ADOX.columns
Dim column As ADOX.column
sConnString = "DSN=PUOPS;" & _
"Uid=puamfm;" & _
"Pwd=puamfm"
oConn.Open sConnString
' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & g_strDB & ";"
For Each oTable In oCat.Tables
Debug.Print oTable.Name
If oTable.Indexes.Count = 0 Then 'not updateable
For Each column In oTable.columns
'If oTable.Type = "TABLE" Then
Select Case UCase(column.Name)
Case "MSLINK"
Set idx = New ADOX.Index
idx.Name = "__" & oTable.Name & column.Name
idx.columns.Append column.Name
idx.Unique = True
oTable.Indexes.Append idx
Case "QUADNAME"
Set idx = New ADOX.Index
idx.Name = oTable.Name & "_" & column.Name
idx.columns.Append column.Name
oTable.Indexes.Append idx
End Select
'End If
Next column
End If
If oTable.Type = "PASS-THROUGH" Then
Debug.Print oTable.Name
With oTable
'.Properties("Jet OLEDB:Create Link") = False
'.Properties("Jet OLEDB:Remote Table Name") = "authors"
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
End If
Next
oCat.Tables.Refresh