Automatically re-linking tables after they have been renamed:need code 1

[alaskaguru]

Thanks for trying to help. Here is what I have:<br>
<br>
Backend: Oracle 8.0.5 26 tables with views based on username<br>
Front end: Access 97 Links to those views which are then renamed to be user-friendly.<br>
<br>
I have 26 views built from 26 tables in the back end. When I modify a table in oracle then I have to re-link the view to it in access or I get the message &quot;This is not an updatable recordset&quot;. If I use the Linked Table Manager to relink it then I still get the same message. The manual says that I can't use the linked table manager if the names of the linked tables are changed--which is what I have done to make the table names more user-friendly. I have code to rename the tables but all of the code I have written using the help files and manuals has not worked. I can manually delete the linked tables and then relink them and everything works great--but I wouldn't want to have to talk a remote user through it over the phone. If anyone has some code for this could they pass it on? I'd appreciate it. The oracle views I'm linking to all have a &quot;v&quot; in their name as in &quot;vemployee&quot;. I am using an ODBC connection to the database.<br>

 

[DougP]

Look at: docmd <br>
<br>
first delete the table link <br>
DoCmd.DeleteObject [objecttype, objectname]<br>
<br>
then add it back in using the.<br>
DoCmd.DoMenuItem menubar, menuname, command[, subcommand][, version]<br>
<br>
DoCmd.DoMenuItem acFormBar, acfilemenu, acgetexternaldata, aclinktables, acMenuVer70<br>
<br>
I tried this but I get an error I'm using Access '97<br>
<br>
<br>
This example adds a new record and does work<br>
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

Alaska,<br>
Here's what I do for ODBC relinking:<br>
<br>
dim db as database, i as integer<br>
set db = currentdb<br>
For i = 0 to db.tabledefs.count -1<br>
IF INSTR( db.tabledefs(i).connect,&quot;ODBC&quot then<br>
db.tabledefs(i).refreshlink<br>
end if<br>
next i<br>
<br>
This is all that's needed. Also, if you use a 'utility' module in your apps as many of us do, you might like the following for convenience:<br>
Sub RF(tn As String)<br>
'REFRESHES TABLE DEF (USED FOR CALLING FROM DEBUG WINDOW)<br>
On Error GoTo ERRRF<br>
CurrentDb.TableDefs(tn).RefreshLink<br>
Debug.Print tn & &quot; REFRESHED&quot;<br>
EXRF:<br>
Exit Sub<br>
ERRRF:<br>
Debug.Print &quot;ERROR REFRESHING &quot; & tn & &quot; &quot; & Err & &quot; &quot; & Error<br>
Resume EXRF<br>
End Sub<br>
<br>
Then, after you've made a change, ie, through SQLPlus, go in the Debug window, just type RF &quot;tablename&quot; and you're ready. <br>
--Jim

 

[alaskaguru]

Nope. It doesn't work after the tables are modified in SQLPlus. Once that happens the refresh method becomes worthless. Maybe it has to do with selecting the unique identifier for the table (Access, when linking using ODBC asks for the unique key for each table before linking to it). I had used similar code to try to refresh the links. I even tried renaming them back to their original names before I refreshed the link. Under oracle, the tables have the owner's name associated with the table as in DEMO.vemployee_salary and these are renamed to look like DEMO_vemployee_salary in Access. I delete the &quot;DEMO_v&quot; part using code.<br>
I think Doug's suggestion above has the same intrinsic problem--selecting the unique keys. Maybe you can't get there from here? Any other ideas? <br>
<br>

 

[JimHorton]

Alaska,<br>
If Access is asking for a unique identifier, then you probably don't have a primary constraint in Oracle. Access should be able to even use a unique index, even if no explicit Primary constraint exists in Oracle. Let me know if this gets you closer...<br>
--Jim

 

[alaskaguru]

Thank you Jim, but I used one of the demo tables and I think just about every column is indexed. I wonder if there is a way to let access know what the primary key columns are using code? It's five AM and I've been working on this all night (not just this one problem of course) The error message I got when I ran your code was that I wasn't attached to the network. The other unchanged tables refreshed fine. But the one where I added a column never did. The funny thing was that I was still able to add data to that table--it should have given me an error. <br>
<br>
Again, I do appreciate your help. <br>
<br>
...Ward<br>
<br>
It must be a feature.

 

[JimHorton]

Ward,<br>
I'm at the end of my rope. You might try doing the same, but linking tables instead of views, if this works then you know it has to do with how the ODBC driver links to a view as opposed to a table. But I can say that with a table, if that table has at least one *unique* index, then the ODBC driver (I'm using 8.00.6, I believe) will refresh fine using the code above, without prompting for a key. I'm curious about the network error message, though--maybe the original connect string may have been different on that one table? You could try to use the connect string from a known good link:<br>
<br>
strConnect = db.tabledefs(&quot;good table&quot.connect<br>
db.tabledefs(&quot;table needing refresh&quot.connect = strConnect<br>
db.tabledefs(&quot;table needing refresh&quot.refreshlink<br>
<br>
Sorry I couldn't be more help...<br>
--Jim

 

[alaskaguru]

Actually, I used the demo tables which came with Oracle 8.0.5 as a testbed without success. This morning I had a table who's structure had been unchanged but the data had been mixed case and I updated it to upper case. For some reason access continued to see the data as mixed case. So I used the refresh method above to relink the table and it seemed to run fine. But the table still appeared as mixed case until I manually deleted the linked table and relinked it! I even checked the properties of the table in design view to be sure it was linked by ODBC to Oracle. Also, whenever I delete a linked table and then add one back Access stops responding and I have to end the task. When I go back into the Access database everything is linked normally. What a nightmare. I am using version 8.00.05 of the Oracle ODBC driver. Perhaps an upgrade is in order. If anyone knows where to get the latest version of the oracle driver please let me know at <A HREF="mailto:ward@dbsalaska.com">ward@dbsalaska.com</A><br>
Jim, once again, thank you very much for your time.<br>
<br>
....Ward<br>