Excel VBA & ODBC Data Sources

[Tim Allen]

I've built an excel application that I need to share with many people
within the company, problem is almost none of them have the
non-standard data source defined on their PC, and, seeing as this uses
a service account to access a SQL Server, it would be better that they
not have this data source defined in case they need to access that
server using their own user id... Here is my connection string in VBA:

ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=aProduction SQLServer -
9;UID=*ServiceAccount*;PWD=*ServiceAccountPasswd*;APP=Microsoft Office
XP;WSID=SYS9962;DATABASE=QDB;Network=DBMSSOCN" _
, Destination:=Range("A1"))

This connection string includes the username and password used for the
connection, it does not include the hostname of the sql server, but
instead, the datasouce name, that is what i'm interested in changing...
Is there any chance I can bypass ODBC when connecting to SQL Server
from excel? or anyway i can, from within VBA, create and drop data
sources on the fly?

Thanks for your time,

-Tim

 

[Guest]

Tim,
You can use ActiveX Data Objects to connect directly to SQL Server without
an ODBC connection. There are a number of examples and instructions
regarding this subject in the MSDN library
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscsection1_ado.asp).

The following is a routine I use to extract data from a SQL Server database
and then display the results in Excel. You can modify the routine to prompt
the user to input information to be used in the query and set up the query to
use these variables to make the query more useful.

There are more elegant ways of doing this, but this has worked for me and it
is fairly easy to troubleshoot.

Hope this helps,

Raul

Sub ADOEXAMPLE1()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim i As Long, j As Long, l As Long, m As Long
Dim numcol As Long, icount As Long
Dim CombinedArray(500, 30) As String, FieldNames(30) As String
Dim ThisSheet As String
Dim strtrow Long, endrow As Long, strtcol As Long, endcol As Long
Dim offset As Long

On Error Resume Next

Sheets.Add
ThisSheet = ActiveSheet.Name
Worksheets(ThisSheet).Activate

Application.ScreenUpdating = False

strtrow = 1
strtcol = 1
offset = 4


oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Data is being retrieved from the database. " & _
"Please wait until complete."
sql = "SELECT * FROM TableX " & _
"WHERE whatever < 'whatever you want' " & _
"ORDER BY whatever you want to order by"

cn.Provider = "SQLOLEDB.1"
cn.ConnectionString = "DATABASE=your database name;SERVER=your server
name;uid= your user id;Password=your password;Persist Security Info=True;"

i = 0
Err = 0
cn.Open
rs.Open sql, cn
If Err = 0 Then
While Not rs.EOF And Err = 0
If Not IsNull(rs(0)) Then
i = i + 1
For j = 0 To rs.Fields.Count - 1
CombinedArray(i, j + 1) = rs(j).Value
Next j
End If
rs.MoveNext
Wend
End If
icount = i

For j = 0 To rs.Fields.Count - 1
FieldNames(j + 1) = rs.Fields(j).Name
Next j
numcol = rs.Fields.Count

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
' Loop to populate the worksheet
For j = 1 To icount
Application.StatusBar = _
"Generating display row " & j & " of " & icount & ""
For k = 1 To numcol
Worksheets(ThisSheet).Cells(strtrow + offset + j, strtcol - 1 + k).Value =
CombinedArray(j, k)
Next k
Next j

'Loop to ouput column names
For m = 1 To numcol
Worksheets(ThisSheet).Cells(strtrow + offset, strtcol - 1 + m).Value =
FieldNames(m)
Next m

End Sub

 

[Tim Allen]

Raul,

Thanks a ton, i hadn't looking into ADO but its a much better way to do
this than through ODBC data sources... I had just spent the last hour
or so trying to build an msi package / installer for the users to run
to create the datasource if it didn't exist on their machine, and those
data sources are stored as hex values in the registry... its VERY
messy... Thanks again...

-Tim

 

[Rob van Gelder]

Alternatively, you could create an OLE DB querytable (opposed to ODBC
querytable).