Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
...
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
The assembly that is failing is a visual basic procedure. Unfortunatly the Ace Driver is failing to connect to the DB for him but the same driver is working fine with my linked servers. We've been through tons of support and work with the assembly and at this point were looking to see if there is another option –  Lumpy Mar 1 '13 at 15:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.