Remove a table link with vba or macro in Access

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

 

Question Info


Last updated October 15, 2019 Views 19,440 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank You

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Albert,

 

When I use the code it deletes the whole table?

 

Thanks for any help

 

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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 Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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



 

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 

 

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.