Linking Access tables to SQL Server

The question was, how do I link from SQL Server to an Access db and loop through all of the Access tables that end with "Data" ... My example uses a 2000-format file C:\BILL\Test2000.mdb

First, run this one-time to create the linked server:

sp_addlinkedserver
@server = 'Test2000',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test2000.mdb'
GO

sp_addlinkedsrvlogin
@rmtsrvname='Test2000',
@useself='False',
@rmtuser='Admin',
@rmtpassword=''
GO

Then, you can use this to build a temp table with the desired table names, and loop thru to view the data using a cursor and dynamic sql (shame on me):

CREATE TABLE #temp
(
TABLE_CAT varchar(MAX),
TABLE_SCHEM varchar(MAX),
TABLE_NAME varchar(MAX),
TABLE_TYPE varchar(MAX),
REMARKS varchar(MAX))

INSERT INTO #temp
EXEC sp_tables_ex 'Test2000','%Data'

DECLARE @tablename varchar(64)

DECLARE curTBL CURSOR FOR
SELECT TABLE_NAME FROM #temp

OPEN curTBL
FETCH NEXT FROM curTBL INTO
@tablename

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('SELECT * FROM Test2000...' + @tablename)
FETCH NEXT FROM curTBL INTO
@tablename
END

CLOSE curTBL
DEALLOCATE curTBL

DROP TABLE #temp