Access 97 Hangs when Linking SQL Tables Through Proxy Server

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Jim Whit » Wed, 08 Dec 1999 04:00:00



Sorry for the extensive cross-post, but I'm integrating MS Products and I'm
stumped.

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,
-w

Jim White
Lizard Database & Web, Inc.
http://www.ldbw.com/

 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Simon Clar » Wed, 08 Dec 1999 04:00:00


Are you absolutely and totally convinced that clients are only connecting
via TCP/IP sockets?  Do you definitely know that your SQL Server is
listening on port 1433?

Have you tried going back to an earlier version of the ODBC driver set?
There are documented problems with the latest drivers.

--
Member, WorldWide SQL Server User's Group (www.sswug.org)

Quote:> Sorry for the extensive cross-post, but I'm integrating MS Products and
I'm
> stumped.

> 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,
> -w

> Jim White
> Lizard Database & Web, Inc.
> http://www.ldbw.com/


 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Jim Whit » Thu, 09 Dec 1999 04:00:00



Quote:> Are you absolutely and totally convinced that clients are only connecting
> via TCP/IP sockets?  Do you definitely know that your SQL Server is
> listening on port 1433?

Well, I'm connecting via the internet for the remote server, and I can fully
administer everything and correctly attach.  In my Local testbed, I only
have TCP/IP set up on all the clients.  I can telnet to port 1433 on both
ends, so it's listening and should be all set.

Quote:> Have you tried going back to an earlier version of the ODBC driver set?
> There are documented problems with the latest drivers.

Not yet, but I'll try that tonight.  Can I downgrade the MDAC by installing
an earlier version?  Any issues that anyone's tripped over?

Thanks,
-w

 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Melodi » Thu, 09 Dec 1999 04:00:00



Quote:>.......
> Have you tried going back to an earlier version of the ODBC driver set?
> There are documented problems with the latest drivers.

Anyone care to elaborate on these documented problems and/or where I can
find them?  I've been running into a mess of problems with the
latest-greatest drivers!

Thanks,
Melodie

 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Simon Clar » Tue, 14 Dec 1999 04:00:00


No - it's not that simple - look for occurrences of sqlsrv32.dll - you'll
find a few (hopefully).  Right click and look at the properties to see their
dates etc.

Regards
Simon

--
Member, WorldWide SQL Server User's Group (www.sswug.org)



> > Are you absolutely and totally convinced that clients are only
connecting
> > via TCP/IP sockets?  Do you definitely know that your SQL Server is
> > listening on port 1433?

> Well, I'm connecting via the internet for the remote server, and I can
fully
> administer everything and correctly attach.  In my Local testbed, I only
> have TCP/IP set up on all the clients.  I can telnet to port 1433 on both
> ends, so it's listening and should be all set.

> > Have you tried going back to an earlier version of the ODBC driver set?
> > There are documented problems with the latest drivers.

> Not yet, but I'll try that tonight.  Can I downgrade the MDAC by
installing
> an earlier version?  Any issues that anyone's tripped over?

> Thanks,
> -w

 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Simon Clar » Tue, 14 Dec 1999 04:00:00


Try searching the MSDN online support site for ODBC problems, or the
knowledge base....

Can you expand on the problems you have been experiencing - perhaps they
aren't even known in the big wide world...

Regards
Simon--
Member, WorldWide SQL Server User's Group (www.sswug.org)



> >.......
> > Have you tried going back to an earlier version of the ODBC driver set?
> > There are documented problems with the latest drivers.

> Anyone care to elaborate on these documented problems and/or where I can
> find them?  I've been running into a mess of problems with the
> latest-greatest drivers!

> Thanks,
> Melodie

 
 
 

Access 97 Hangs when Linking SQL Tables Through Proxy Server

Post by Melodi » Tue, 14 Dec 1999 04:00:00


I've searched the MSDN and haven't found much (if anything at all)...  I'll
give an example:

ODBC 4.0 dlls wreak havoc with '12:00:00AM' in a DateOfBirth field in
Access.   Any record that contains this (originally placed in there by
another utility) and used in our app will result in a 'datetime field
overflow' error on a save and occassionally "hstmt== SQL_NULL_HSTMT."  This
does not occur using the 3.5 dlls.

If the above type of record is ported from Access to SQL, then the
'12:00:00AM' will appear as '01/01/1900' and when this type of record is
retrieved another 'datetime field overflow' error will occur. (Again, using
the 4.0 dlls from MDAC 2.1).

Just one small example.. I'm sorry if it doesn't make much sense, I'm trying
to fit this in between taking calls and writing cases.
Cheers,
Melodie


Quote:> Try searching the MSDN online support site for ODBC problems, or the
> knowledge base....

> Can you expand on the problems you have been experiencing - perhaps they
> aren't even known in the big wide world...

> > Anyone care to elaborate on these documented problems and/or where I can
> > find them?  I've been running into a mess of problems with the
> > latest-greatest drivers!

> > Have you tried going back to an earlier version of the ODBC driver set?
> > There are documented problems with the latest drivers.

 
 
 

1. Access 97/SqlServer 7: Appending Access Tables into Sql Server tables doesnt work for big tables

Hello,

I am trying to append the data from the Access'97 tables into the linked SQL
server 7.0  tables. Although this process seems to work for smaller tables,
its fails to work for relatively bigger tables(9000 records).  I have tried
to append 500 records at one time, but then the same problem persists. I
find it difficult to digest the fact that the same process worked fine with
6.5 when i tried to append 1000 records at one time.

Is there something that i am missing?
Please advice. This is really urgent!!!

Rgds
Nsn.

2. aging of accounts

3. Access 97 global links to SQL Server db table

4. More on "NULL Concatenation" issue in SQL 7.0

5. SQL Server 7 and Access 97 - linked table problems

6. Would DTS Allow File's Update Across Servers?

7. SQL Server 7.0 tables Linked into Access 97 DB

8. Named Pipes connection problem via ASP.NET using ADO.NET

9. How to link temporary tables of SQL Server from Access 97

10. Link Table to SQL Server 2000 from MS Access 97

11. Date fields: Using Access 97 with link tables from Sql Server

12. Problem Linking to SQL Server 7 Table Through Access 97

13. Updating SQL Server linked table in Access 97