Table does not exist

[FoFa]

2003 MDB working in 2007.
So I removed the ODBC linked table from Access.
I dropped the table on the SQL server.
I use VBA code to refresh the links.
I get an error that says the table does not exist (DAH).

I'm using DAO and it errors on the RefreshLink command as it is doing a FOR EACH through tabledefs.

So why does tabledefs still think this table is there?

Compact/Repair does NOT fix the problem.

 

[The_Doc_Man]

The definition is on the front-end in the local tabledefs but points to the back-end where the tables actually reside. The front-end needs the local definitions because the relationships are in the FE. They have to be in the FE in order for you to be able to create queries with JOINs handled automatically. After all, it is the FE that does the real work of a query. The BE is just an open database file that isn't really running ANY code.

I remember that from another post at least six to twelve months ago, darned if I remember the exact thread or the circumstances.

Access is (I'm guessing) designed to find out at run-time whether you have a lost link. But if you have the possibility of a lost link, it is a significant form of insanity to drop the table because of the lost link alone. You might just come back an hour later and be able to make the same link you couldn't make before just by stopping and restarting Access.

If I were writing Access myself, I wouldn't drop a linked table just because it failed across a network. I'd give my users a chance to fix the link and try again. As I said, it's a guess, but I think I understand a possible motive for keeping the table.

Your VBA code doesn't try to delete the table definition, does it? I would think that you should manually dump the table definition if you know it is really obsolete. It would be possible but a really questionable design decision to drop the table in your code unless you look at the error that comes back and quantify whether it is a dropped link to the backend or the backend actively telling you "Huh? What table?"

 

[gemma-the-husky]

i am sure you know but the tabledef stores these three things (among others)

a) the table name in THIS database
b) the connect string to the the backend DATABASE (which varies according to the type of backend)
c) the table name in the BACKEND database

now the connection could fail because either the path is no longer valid/cannot be resolved, or less likely, the backend table cannot be found.

if you drop the link to the table, you will then have to completely rebuild the link to get it back.