Remove a table link with vba or macro in Access

The followign command in code will delete a table link, and not the data nor the linked table:

docmd.DeleteObject acTable,"tblcontacts"

Be careful with the above code and command. If you enter the name of a real non linked table in above, then that table will be deleted and will do so without any kind of prompts or questions to the user that you about to delete a real table.  So, this type of command needs to be used with caution.

Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada

Thank You

Hi Albert,

When I use the code it deletes the whole table?

Thanks for any help

Hi to All,

Can vba or a macro be used for the following task in Access:

 

There is a table that is linked from another database, is it possible to remove the link and keep the data.

 

Thanks for any help

What do you mean by "remove the link and keep the data"?  The data is stored in the table in the other database, so deleting the table link in this database doesn't delete the data in that database.  But when you delete a linked table, that data becomes (normally) inaccessible.

You *can* create queries that pull data from tables in other databases, even without using a linked table.  But I don't know if that is the sort of thing you have in mind.

Hi Dirk,

I have a linked table and basiclly want to change it to an imported table.

Another user has this table linked. When I go into the database I want to have a macor or code only delete  the link.

Thanks for any help

Hi Dirk,

I have a linked table and basiclly want to change it to an imported table.

Another user has this table linked. When I go into the database I want to have a macor or code only delete  the link.

Thanks for any help

 

So you want to replace the linked table with a local table, imported in its place?  If you know the path to the database containing the original table at the time that you write the code, you can do it with code along these lines:

'------ start of example code ------
Const TableName As String = "YourTableName"
Const SourceDBPath As String = "C:\Some Path\To\SomeDatabase.mdb"

' Delete the linked table.
If Len(CurrentDb.TableDefs(TableName).Connect) > 0 Then

    DoCmd.DeleteObject acTable, TableName

    DoCmd.TransferDatabase _
        acImport, _
        "Microsoft Access", _
        SourceDBPath, _
        acTable, _
        TableName, _
        TableName

Else
    MsgBox _
        "Hey!  '" & TableName & "' is not a linked table! " & _
            "I won't delete it.", _
        vbExclamation, _
        "Not a Linked Table"
End If
'------ end of example code ------

Dirk,

Thank you for your reply

I am currently using process similar to what you have above. The problem I have is that the linked table is on a shred drive and it takes a long time to import the table. So I thought if I could just delete the link it would save time.

If i'm uderstanding the responses correctly the only option I have is to delete the table ( if this is not correct please let me know).

Thanks again for your time

I am currently using process similar to what you have above. The problem I have is that the linked table is on a shred drive and it takes a long time to import the table. So I thought if I could just delete the link it would save time.

 

If i'm uderstanding the responses correctly the only option I have is to delete the table ( if this is not correct please let me know).

Either the table is linked (stored in some other database) or it is local (stored inside the current database).  If it's linked, deleting the link won't automatically make it local, it just makes the table completely unknown.  To change it into a local table, you have to import it.  To import it under the same name as the linked version, you have to delete the link first.  Your could import it under some other name, but then you'd still have to delete the linked table and rename the new local table to give it the original name.  So there's no advantage to doing that.

If I understand you, there is really no alternative, if you want to replace a linked table with a local one.

I found a similar question That may better explain what I'm trying to accomplish:

For clarification I have an mdb, let's call it "A". This mdb has links to tables in another mdb, "B". I want to remove those links from "A", and not touch the actual table in "B". Correct?

The following solution was offered but it didn't work for me

Public Function Delink(tblName As String)
Dim tdf As DAO.TableDef
CurrentDb.TableDefs.Delete tblName
End Function

Thanks for any help

I found a similar question That may better explain what I'm trying to accomplish:

For clarification I have an mdb, let's call it "A". This mdb has links to tables in another mdb, "B". I want to remove those links from "A", and not touch the actual table in "B".

The following solution was offered but it didn't work for me

Public Function Delink(tblName As String)
Dim tdf As DAO.TableDef
CurrentDb.TableDefs.Delete tblName
End Function

Thanks for any help

Thank you for your patience