If you need to enumerate the tables in an Access database, the easiest method is through the ActiveX Data Objects Extensions for Data Definition Language and Security. The ADOX library, as it is better known, can be used just like other COM objects from within ActiveX script, such as a task or workflow script.

The code below is a simple example that enumerates the tables and just displays the table name in a message box for demonstration purposes. The sample code can be pasted directly into an ActiveX Script Task. In addition to the script task the code expects there to be a DTS connection in the package, called "Microsoft Access". This allows the script to get the Access database file location directly from the connection's DataSource property.

Option Explicit

Function Main()

        ' Declare variables
        Dim oPkg
        Dim oConn
        Dim sConnString
        Dim oCatalog
        Dim oTable

        ' Get reference to current package
        Set oPkg = DTSGlobalVariables.Parent
        ' Get reference to Access connection, by name
        Set oConn = oPkg.Connections("Microsoft Access")

        ' Build Access MDB connection string using DTS connection
        sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & oConn.DataSource

        ' Create Catalog Object
        Set oCatalog = CreateObject("ADOX.Catalog")

        ' Open Access MDB
        oCatalog.ActiveConnection = sConnString

        ' Enumerate tables in catalog
        For Each oTable In oCatalog.Tables
                ' Filter on user tables only
                If oTable.Type = "TABLE" Then
			' Display table name for Demo purposes
			' Add you "real" code here
                        MsgBox "Table Name: " & oTable.Name
                End If
        Next

        Set oTable = Nothing
        Set oCatalog = Nothing
        Set oConn = Nothing
        Set oPkg = Nothing

        Main = DTSTaskExecResult_Success
End Function