Relinking ODBC Views

[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.

 

[Deville]

U can use this to fix that problem

CREATE INDEX NewIndex ON LinkedViewName (FieldName1,
FieldName2);

Hope this helps...

-----Original Message-----
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.tabledef
myIndex DAO.indexes

loop through tableDef objects

set myTable = current tableDef object

set myIndex = myTable.Indexes;

myTable.connect = ......
myTable.refreshlink

for i = 1 to myIndex.count
myTable.Indexes.append(myIndex(i))
next i

end loop

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.