Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 9th, 2006, 03:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default Code to Detach Tables

I've not tried to manipulate tables using Access VBA in this manner, and so I'm not sure how to approach it. Here's the pseudo code of what I want.

Code:
For I = 1 to No. of Tables
    If Table is Linked from another dB Then
        Detach Table
    End If
Next I
I'm using Access XP (2002). Thanks in advance.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
 
Old February 9th, 2006, 06:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Here's what works. The tricky part was that when you actullay dropped a table, the TableDefs.Count changed so you had to adjust the counter for the loop. Also, if the last table was linked, your index ended up past the count because the count dropped by one, so the Exit For was necessary.

Code:
Private Sub UnLinkTables()

    Dim db As Database, tbf As TableDefs
    Dim bytCount As Byte, bytTotal As Byte
    Dim strName As String, fLinked As Boolean, fMsys As Boolean

    Set db = Application.CurrentDb
    Set tbf = db.TableDefs
    bytTotal = tbf.Count

    For bytCount = 0 To bytTotal - 1
        strName = tbf(bytCount).Name
        fMsys = (Left(strName, 4) = "MSys")
        fLinked = (Len(Nz(tbf(bytCount).Connect, "")) > 0)

        If Not fMsys And fLinked Then
            'Not a system table and table IS linked.  OK to drop.
            tbf.Delete strName

            bytTotal = tbf.Count
            bytCount = bytCount - 1
            If bytCount = bytTotal - 1 Then
                'Last table has been dropped.  Index past count.
                Exit For
            End If
        End If
    Next bytCount

    db.Close

    Set tbf = Nothing
    Set db = Nothing

End Sub

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division





Similar Threads
Thread Thread Starter Forum Replies Last Post
Creation of Database tables from code... TomW Visual Basic 2008 Essentials 11 February 29th, 2008 03:08 PM
How to link new tables using code to MSAccess Nordiers Access VBA 2 January 10th, 2008 11:35 PM
Attach/Detach Component AccessAasif@aol.com C# 2005 0 December 7th, 2007 04:17 PM
Detach an in-use database via code Sma_Soft SQL Server 2000 0 August 14th, 2007 10:20 AM
Want to code external linked tables in Access Michael Allaire Access VBA 1 July 23rd, 2003 06:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.