How do I programmatically delete a link to a table in a linked file where the table has been deleted?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon A - 13 Apr 2005 05:18 GMT

I can't seem to figure this out.

I have a database where all the tables are in a separate
back-end linked file. There were some tables in the initial
version of the database that I want to delete.

I can delete the actual tables by using the procedure below.
That works successfully.

But the links in the front-end are still there. So when I
try to do any update down the road I will get an error when
linking the tables (I have a procedure that automatically
links the tables but when there is a link in the front-end
and no table in the back-end then I get an error.)

Like I said, the actual tables get deleted OK when I run the
code below - but not the links.

I have several things:

CurrentDB.TableDefs.Refresh (after deleting the table)
db.TableDefs.Delete "tbl_DefectTypes"

Neither of these seem to get rid of the links and I get
errors on the second one saying the table isn't there.

I'm stumped. Anyone can help?

Here is the code...
==========================================
Public Function DeleteUnnecessaryTables() As Boolean

Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "Drop Table tbl_DefectTypes;"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True
DeleteUnnecessaryTables = True

End Function 'DeleteUnnecessaryTables
==========================================

Reply to this Message

Paul Overway - 13 Apr 2005 05:27 GMT

You'd need to write code within the front end to delete the links that are
no longer needed, i.e.,

Sub DeleteInvalidLink()

Dim db as database
Set db =currentdb() 'OR Set db = OpenDatabase("path to file here")
db.TableDefs.Delete "tbl_DefectTypes"

End Sub

Realistically, you need to distribute an update to the front end of your
app...in which case, you can distribute it without the bad link anyway.
Unless you're saying the table links exist within yet another database, but
even then you'd still need to update the front end if any functions within
it use the old bad links.

Signature

Paul Overway
Logico Solutions
http://www.logico-solutions.com

>I can't seem to figure this out.
>
[quoted text clipped - 40 lines]
> End Function 'DeleteUnnecessaryTables
> ==========================================

Reply to this Message

Jon A - 14 Apr 2005 01:08 GMT

Thanks. I think that works. I tried a number of things and
they all did not work, so I appreciate your helping with the
good advice and the solution.

>You'd need to write code within the front end to delete the links that are
>no longer needed, i.e.,
[quoted text clipped - 12 lines]
>even then you'd still need to update the front end if any functions within
>it use the old bad links.

Reply to this Message