How to Programmatically Delete Linked Tables from Backend Only

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
Happy Friday, everyone!

I am working on code that will help me set up different Front Ends to different Back End tables without manually importing tables or using the Linked Table Manager. I have already figured out how to automate the import and relinking functionality, but now I want to add a functionality to delete the links already in the database. However, I DO NOT want to delete the Linked Excel Sheets.

Is there a way to exclude linked Excel tables from the rest of the linked tables to be deleted?

Here is the link where I got the code to delete the tables:

https://www.devhut.net/2011/06/10/ms-access-vba-delete-all-linked-tables/
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,694
Office Version
  1. 2013
Platform
  1. Windows
You should have tableDef properties to look at that tell you what kind of table/linked table you are dealing with. Probably the Connect property. I'd loop the tables in the DB (tabledefs collection) and check it out.
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
You should have tableDef properties to look at that tell you what kind of table/linked table you are dealing with. Probably the Connect property. I'd loop the tables in the DB (tabledefs collection) and check it out.

How would you do that? Just send it to the immediate window, or to a file? Is there a way to check that info in the GUI?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,694
Office Version
  1. 2013
Platform
  1. Windows
Using the immediate window is a good way to go. You can also print to the immediate window. Or to a message box.
For debugging in general in VBA see here:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

For instance, you could create a small code snippet:

PSEUDOCODE
Code:
for each td in currentDatabase.TableDefs
    debug.print td.Connect

Then you can see what kind of values are in that property (as well as paying particular attention to the difference between the linked tables you are interested in, and the rest.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,749
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top