[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Managing Tabledefs in MS Access 2003

I am so used to ADO that I forgot how to use DAO.  I want to manage links from MS Access (Front End) to SQL Server 2005 (Back End).  For various reasons I want to "Link" tables when opening my program and "Unlink" the tables before leaving MS Access.  I have the "Link/ReLink" code, but want to have a function "unlink" all SQL tables.  Can I do this with ADO or do I have to use DAO?

Peter Allen
Peter Allen
1 Solution

From memory - so this will only put you on the right path - you do this by using DoCmd.TransferDatabase and setting the options to link.  That does the linking (and you'll have to work out for yourself how to iterate some list of the table names, the path to the linked db etc.).

Then to drop the link, you iterate through CurrentDB.TableDefs and delete the ones you don't want.

Public Sub LinkEmAll
   Open a recordset of table names
   For each table name
      DoCmd.TransferDatabase link the table
   Close the recordset
End Sub

Public Sub DropEmAll
   Dim tdf As DAO.TableDef

    Open a recordset of table names
    For Each table name
        For Each tdf in CurrentDB.TableDefs
            if tdf.Name = recordset table name then
            end if
    Close recordset  
End Sub

Sorry I can't do better than that, but I'm on site and can't get to Access right now.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To "unlink" a table you simply delete it in the Frontend (and yes, it's easiest to do this through DAO when working in Access):

Dim tdf As DAO.TableDef
Dim dbs As DAO.Datbase

Set dbs = CurrentDB
For each tdf in dbs.TableDefs
  If Len(tdf.Connect) > 0 Then
    DoCmd.DeleteObject acTable, tdf.Name
  End If
Next tdf

As to only working with SQL tables - you could examine the tdf.Connect string to see if it contains certain keywords (like your SErver name, of ODBC, or whatever makes sense in your app) and then delete the link based on those findings.
Peter AllenAuthor Commented:
Thank you so much!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now