Creating ODBC link within SELECT statement
 

Hi Preacher Man,

Please don't cross-post to multiple newsgroups.

This bit of code sets a connection to Northwind on my local server

Sub UpdatePivot()
Dim pvt As PivotTable
Dim pCache As PivotCache
Dim sSQL, sServer, sUser As String

Set pvt = PivotTables(1)
Set pCache = pvt.PivotCache

' write a SQL query
sSQL = "SELECT Orders.* FROM master.dbo.Orders Orders"

' select the server and login
sServer = "EDSLAP"
sUser = "sa" ' log in as sa, will ask for pwd

pCache.CommandText = sSQL

pCache.Connection = "ODBC; DRIVER=SQL SERVER;" _
& "SERVER=" & sServer & ";" _
& "UID=" & sUser & ";" _
& "APP=Microsoft Office 2003"

pCache.Refresh

End Sub

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup.
Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?

Thanks.