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 AllenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.