G
Guest
Access 2003
I am using linked tables to access views from a database server
When I initially link the views a dialog box prompts me to select the column(s) that uniquely define a row in the view. The selection that I make at this point is stored in the "Indexes" properties of the linked tabledef object
The problem I am having is that when I use the tabledef.relink method to update the ODBC connection information on these linked views they are no longer updateable. The problem appears to be that when the relink method executes it removes any and all currently defined indexes within the tabledef object. Without this index information, Access does not know how to interact with the server-side view (it doesn't know what uniquely defines the rows) so the views are no longer updateable
I have been trying to figure out how to programmatically replace the index information when the link is refreshed. If been attempting to save the current index data into a variable prior to the relink so that it can be replaced afterwards using code like this
myTable DAO.tablede
myIndex DAO.indexe
loop through tableDef object
set myTable = current tableDef objec
set myIndex = myTable.Indexes
myTable.connect = .....
myTable.refreshlin
for i = 1 to myIndex.coun
myTable.Indexes.append(myIndex(i)
next
end loo
The problem with this is that the command "set myIndex = myTable.Indexes;" creates a reference to the Indexes of the current tableDef (and not a physical copy). Thus, when the refreshlink method executes it not only purges the indexes in the current tableDef, but it also purges them from the reference in the myIndexes variable. As a result I have no index data to restore afterwards so I am in the same boat
Can someone either
1. Show me how to create a copy of the Indexes collection (not a reference that will be lost when the relink executes
2. Suggest a better idea on how to refresh ODBC view links without loosing the updateability of the views
Thanks.
I am using linked tables to access views from a database server
When I initially link the views a dialog box prompts me to select the column(s) that uniquely define a row in the view. The selection that I make at this point is stored in the "Indexes" properties of the linked tabledef object
The problem I am having is that when I use the tabledef.relink method to update the ODBC connection information on these linked views they are no longer updateable. The problem appears to be that when the relink method executes it removes any and all currently defined indexes within the tabledef object. Without this index information, Access does not know how to interact with the server-side view (it doesn't know what uniquely defines the rows) so the views are no longer updateable
I have been trying to figure out how to programmatically replace the index information when the link is refreshed. If been attempting to save the current index data into a variable prior to the relink so that it can be replaced afterwards using code like this
myTable DAO.tablede
myIndex DAO.indexe
loop through tableDef object
set myTable = current tableDef objec
set myIndex = myTable.Indexes
myTable.connect = .....
myTable.refreshlin
for i = 1 to myIndex.coun
myTable.Indexes.append(myIndex(i)
next
end loo
The problem with this is that the command "set myIndex = myTable.Indexes;" creates a reference to the Indexes of the current tableDef (and not a physical copy). Thus, when the refreshlink method executes it not only purges the indexes in the current tableDef, but it also purges them from the reference in the myIndexes variable. As a result I have no index data to restore afterwards so I am in the same boat
Can someone either
1. Show me how to create a copy of the Indexes collection (not a reference that will be lost when the relink executes
2. Suggest a better idea on how to refresh ODBC view links without loosing the updateability of the views
Thanks.