Excel, SQL Server & Pivot tables

[jcprince]

I have an Excel 2000 spreadsheet containing a pivot table driven from SQL Server 2000. It is currently updated via an system DSN on my PC, but to enable others to use this, I need to change to a DSN-less connection. Changing from DSN to DSN-less Pivot table

Can anyone supply an example of the VBA required to update a pivot table from SQL server without requiring a DSN on each user's PC. To further complicate matters, the SQL server can only be reached via the SQL Servers IP Address (not named pipes or any other supported protocols) as there is a firewall protecting the database server.

I found the following URL which supplies the syntax required but cannot make it connect.

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm

Thanks in advance.