How to use T-SQL Linked Server to Get MS Access Data Dictionary Info For Columns

Question

Ok, so I have successfully linked MS Access to SQL Server 2012 via a linked server object and can query the db just fine. This MS Access database is published by the government and sometimes they make schema alterations.

My goal was to create a T-SQL stored proc that could make a copy of the MS Access Table/Column meta-data and compare it with a previous version to alert me when schema changes occurred.

I have tried using sp_columns_ex and the sp_tables_ex - sp_tables_ex works fine but the sp_columns_ex returns nothing...

I've tried sp_columns_ex with a varying array of parameters just in case... it doesn't error-out, just returns 0 records.

1. Works - sp_tables_ex @table_server = 'HCDB_CURRENT'
2. Doesn't Work - exec sp_columns_ex @table_server = 'HCDB_CURRENT'
3. Doesn't Work - exec sp_columns_ex @table_server = 'HCDB_CURRENT', @table_name = 'dbo_vwHQI_FTNT'

Any help/ideas would greatly be appreciated.

Answer 1

How about something like:

SELECT TOP 1 * 
INTO #temp
FROM [HCDB_CURRENT]..[dbo_vwHQI_FTNT]

SELECT name FROM tempdb.sys.columns 
WHERE OBJECT_ID = OBJECT_ID('tempdb..#temp');

DROP TABLE #temp;

It's a little hacky, I know, but you don't have to use the stored procs.

Answer 2

Well not my preferred solution at all, but since sp_tables_ex works i could loop over the table name list and generate a select Into statements creates/copies to local sql server tables thus replicating the tables. Then i can just use data dictionary information native to sql server to do the rest.

Would be better if i didn't have to do this and could rely on sp_columns_ex... but this is a path in lieu of something better.

Tim

Answer 3

MS Access has system tables that describe the metadata, just like sys.columns in SQL Server.

http://www.techonthenet.com/access/database/view_systables2007.php

http://www.datanumen.com/access-repair/articles/system-object.htm

can we list all tables in msaccess database using sql?

Can you select from MSysObjects through your linked server (try all of the methods - OPENQUERY, OPENDATASOURCE, OPENROWSET, four part name)

This link says you need to give the Admin user access:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cd6eadd-2d9d-4dbd-8920-e2847a74f80a/retrieve-all-msaccess-table-names-using-openrowset-funtion-in-sql-server?forum=transactsql

It says the following:

1. Press Tools, Security, User And Group Permissions to display the User and Group Permissions dialog box;
2. Select the MSysObjects table in the Object Name list, and give the Admin user permission to read data.

If you regularly get a new copy of this database this could be annoying. You might be able to automate it. It wouldn't hurt to verify it anyway.

Answer 4

As noted, since the access table is LINKED, then if the table in question is modified such as additional columns having been added THEY WILL NOT SHOW up in the linked table. (you would have to re-link).

So we need to get the columns from the sever side AND ALSO the linked table side. With SQL server, we can use this to get columns:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE Table_Name = 'faxbook'

The above thus will return all columns from the server side table and EVEN do so if fields are added.

However, we DO NOT need above. Simple grab the field collection from the server side and grab the field collection from the lined table side.

So save an Access pass though query:

Select * from Faxbook where 1 = 2

Now in VBA, we can do this: Function TableChanged() As Boolean

  Dim rstServer     As DAO.Recordset
  Dim rstLinked     As DAO.Recordset
  Dim f             As DAO.Field
  Dim MyBol         As Boolean

  Set rstServer = CurrentDb.QueryDefs("qPass").OpenRecordset
  Set rstLinked = CurrentDb.OpenRecordset("select * from dbo_FaxBook where 1 = 2", _
                  dbOpenDynaset, dbSeeChanges)


  TableChanged = False
  If rstServer.Fields.Count <> rstLinked.Fields.Count Then

     ' field count is differnt
     TableChanged = True
     Exit Function

  Else
     ' field count is same, check for field name change
     On Error Resume Next
     For Each f In rstLinked.Fields
        MyBol = (f.Name = rstServer.Fields(f.Name).Name)
        ' an error will occur if field name is differnt
        If Err.Number <> 0 Then
           TableChanged = True
           Exit Function
        End If
     Next f
  End If

End Function

So you can simple now go

If TableChanged() = True then
    Call RelinkOneTable('dbo_Faxbook')
End if