Listbox of table names and export to external d/base 1

[air1access]

Hey now..

What I'm wanting to do is:

-Have a list box that is populated with the current d/base table names
-From there, the user can select 1 or more of the table names to have them exported to an external d/base...

I can't figure out how to get a listbox to show the table names...
And I have some ideas on how to export a table, but not from a multi select listbox..

Any suggestions..? Any ideas...?? or examples..???

Thanks in advance..!!
air1access

 

[MazeWorX]

So ... here is a bunch of code some I wrote some came from as far as I can remember an unknown source. It assumes you have a listbox named lstTables. The code to export the tables is not included but Im sure you can figure it out. The OnClick code will loop through the lstbox selections(table names)

The functions are pretty simple they check to see if the table is system or hidden and ignores them if they are then populates the list box with the results

Populate the list box when the form opens

Private Sub Form_Open(Cancel As Integer)
    lstTables.RowSourceType = "Value List"
    lstTables.RowSource = GetTableList()
    lstTables = 0
End Sub

Get the table names from our db to populate the listbox

Private Function GetTableList() As String

    Dim fSystemObj As Boolean
    Dim strName As String
    Dim fShowHidden As Boolean
    Dim fIsHidden As Boolean
    Dim strOutput As String
    Dim fShowSystem As Boolean
    Dim objCollection As Object
    Dim obj As AccessObject

    On Error GoTo HandleErrors
    DoCmd.Hourglass True

    fShowHidden = Application.GetOption("Show Hidden Objects")
    fShowSystem = Application.GetOption("Show System Objects")

    Set objCollection = CurrentData.AllTables

    For Each obj In objCollection
        fIsHidden = IsHidden(obj)
        strName = obj.Name
        fSystemObj = IsSystemObject(obj)
        If (fSystemObj Imp fShowSystem) Then
            strOutput = strOutput & ";" & strName
        End If
    Next obj
    strOutput = Mid$(strOutput, 2)

    DoCmd.Hourglass False
    GetTableList = strOutput
    Exit Function

HandleErrors:
    MsgBox "Error Number " & Err.Number, "GetObjectList"
    Resume ExitHere
End Function

function called to check if its a hidden table

Private Function IsHidden(obj As AccessObject) As Boolean
    If Application.GetHiddenAttribute(obj.Type, obj.Name) Then
        IsHidden = True
    End If
End Function

function called to see if the table is a system table

Private Function IsSystemObject(obj As AccessObject) As Boolean

    Const conSystemObject = &H80000000
    Const conSystemObject2 = &H2

    If (Left$(obj.Name, 4) = "USys") Or Left$(obj.Name, 4) = "~sq_" Then
        IsSystemObject = True
    ElseIf (obj.Attributes And conSystemObject) = conSystemObject Then
        IsSystemObject = True
    ElseIf (obj.Attributes And conSystemObject2) = conSystemObject2 Then
        IsSystemObject = True
    End If
End Function

Command button to loop through list box selections

Private Sub cmdTransferTables_Click()
    Dim lst As ListBox
    Dim varItem As Variant
    Dim strTable As String

    Set lst = Me![lstTables]
    DoCmd.Hourglass True
    If lst.ItemsSelected.Count = 0 Then
        MsgBox "Please select at least one table to export"
        lst.SetFocus
        Exit Sub
    End If
    For Each varItem In lst.ItemsSelected
        strTable = Nz(lst.Column(0, varItem))
        'code to Export your table here and anything else you want to do
    Next varItem
    DoCmd.Hourglass False

End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

The undocumented method would be to use a query of the msysobjects table like:

SELECT Msysobjects.Name
FROM Msysobjects
WHERE (((Msysobjects.Name) Not Like "msys*") AND ((Msysobjects.Type) In (1,6)));

If you have other than Access tables, you might need to add other types.

Duane
Hook'D on Access
MS Access MVP

 

[air1access]

Thank you very much..!! That worked perfectly..!!!