Creating ODBC link within SELECT statement

  • Thread starter Thread starter Preacher Man
  • Start date Start date
P

Preacher Man

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.
 
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
 
Preacher,
Did you create your ODBC link using excel menus Data \ Get External
Data \ New Database Quey
?
 
Back
Top