Sorry for the extensive cross-post, but I'm integrating MS Products and I'm
We have an application built in Access 97 which uses linked tables from MS
SQL Server 7.0 SP1 (via an ODBC System DSN, drivers installed from MDAC 2.1
SP2). All network libraries are set to TCP/IP.
This works just fine when both the SQL Server and the Client are on the same
network, but if I put MS Proxy Server between these two machines, Access
will hang when refreshing the link to exactly 4 (of 25ish) tables. I can
refresh all the other tables and change the DSN just fine, but these 4
tables will hang Access every time.
All of these "problem" tables are simple matching tables, 2 columns of
Integers and about 500 rows each. There's no bad data that I can find, and
each table is involved in one or two relationships which don't spit out
errors at all. We do have replication set up, but the problem showed prior
to setting up replication. Other testing has led me to rule out SQL
replication as a cause.
As near as I can tell, Proxy Server's interaction with ODBC is the culprit
here, but I don't know where to look to narrow things down. Port 1433 is
accessible through the Proxy Server (I can connect with Enterprise Manager,
Query Analyzer, and the rest of the tables in Access just fine).
The same problem is happening in 2 locations (with Proxy Server 1.0 and
2.0), so it is reproducible. On my production server in-house running Proxy
Server 2.0 and SQL Server, I can point the Client DSN to the internal IP
address and everything links up and works like a dream. If I change to the
outside IP address, it bombs on the 4 tables.
This problem is just downright bizarre. Any ideas would be welcome as we're
on a short time schedule (what else is new, right?).
Thanks in advance,
Lizard Database & Web, Inc.