Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA & ODBC Data Sources
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA & ODBC Data Sources
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/de...tion1_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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA & ODBC Data Sources
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA & ODBC Data Sources
Alternatively, you could create an OLE DB querytable (opposed to ODBC
querytable). -- Rob van Gelder - http://www.vangelder.co.nz/excel "Tim Allen" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switching ODBC sources in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel Data Sources | Charts and Charting in Excel | |||
Connection to external data sources in Excel | Excel Discussion (Misc queries) | |||
Two Excel Data Sources | Excel Discussion (Misc queries) | |||
Pivot tables, external data sources and ODBC links | Excel Worksheet Functions |