Pull Access schema into a SQL Server table

Question

We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no longer working and reworking it is not an option at this time.

Is there a way to pull a listing of table names and columns from a linked server Access database? If not, is it possible to pull this information from an SSIS package? The output I need is really simple:

Customer    First Name
Customer    Last Name
Customer    Email
Product     Product ID
Product     Product Name
...

Answer 1

The way we solved this was to use a template db to auto create views against the access linked tables. Each view was created as

CREATE VIEW AS 
SELECT *
  FROM LinkedServer...Table

Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.

SELECT v.name, c.name
  FROM sys.columns c 
       JOIN sys.views v ON c.object_id = v.object_id 
 WHERE v.name = @viewname

We could then insert this into the table we needed to work with.

Answer 2

You need to pass by a Visual Basic procedure and use the Microsoft DAO 3.6 object library(Doc available here).

Code example:

Sub Test()
    Dim aField As DAO.Field
    Dim aTable As DAO.TableDef

    For Each aTable In CurrentDb.TableDefs
        Debug.Print aTable.Name
        For Each aField In aTable.Fields
            Debug.Print "-" & aField.Name
        Next
    Next
End Sub

Idealy, you need to write (by SQL statement) each aTable.Name and aField.Name values in an empty table and export to the required format.