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