Linking to MASSIVE Sql Server DB

[Chris Vachio]

I'm using Access 2007 and I have a massive DB on SQL Server 2005. It's got
66,393 tables in it. There are roughly 12 of them I would like to hit via MS
Access for some audit reporting.

It usually times out when trying to create the external connection. I've
gotten past that, but then only saw the first 300 tables or so. Naturally,
the dozen or so that I need weren't in the first 300.

Anyone have any ideas on how to get around this?

 

[Duane Hookom]

If you want to use them for read-only purposes, consider creating
pass-through queries to retrieve the records.

 

[Chris Vachio]

Yep, that's all I want them for. I'm not familiar with doing that in access.
Can you point me in the right direction?

 

[Duane Hookom]

I usually start by creating a new standard query with no tables. Then switch
the type through the SQL Specific option. Use the connection wizard in the
properties window to specify a DSN or connection string. Then enter the SQL
statement like:

SELECT FieldA, FieldB, FieldC
FROM tblOne
WHERE FieldA >='1/1/2007'

The SQL syntax must match the database server so strings and dates are
delimited with single quotes.