Pull Access schema into a SQL Server table 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
...
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.