Jan 5, 2009 - The Actual SQL Server ODBC Driver lets you access your company's Microsoft SQL Server and Sybase databases from within Microsoft Excel. Connector/ODBC is a standardized database driver for Windows, Linux, Mac OS X, and Unix platforms. Online Documentation: MySQL Connector/ODBC Installation Instructions, Documentation and Change History.
Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection. Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone. Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database. Again, all the previous warnings about locking apply.
Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it. There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it. You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros.
Name the Macro, and then click Create. Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection.
Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone. Best personal banking software for mac. Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA).
If you've got any programming experience at all, you can write code against a database. Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it. There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it. You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database.
Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros. Name the Macro, and then click Create. A little late to help this situation but for anyone else with a similar problem of having the ODBC Data Sources dialog box open every time they execute code to return query data. I've found this issue is due to the fact that the QueryTable 'Save Password' has not been set to 'True' via code, or manually you can set this in the Connection Properties dialog box. As long as you are supplying the password with code and have the 'Save Password' property set to True (or checked in the dialog box) then the ODBC Data Sources dialog box should only appear the first time you execute the code, and sometimes it doesn't display even on the first time as long as you have the ODBC connection set-up on your machine.
'- - - - - - - - - -' Dim qt As QueryTable '' Set up the SQL Statement sqlstring = 'select au_fname, au_lname from authors' '' Set up the connection string, reference an ODBC connection '' Leave the name and password blank for NT authentication connstring = 'ODBC;DSN=pubs;UID=;PWD=;Database=pubs' '' Now implement the connection, run the query With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range('A1'), Sql:=sqlstring).Refresh End With '- - - - - - - - - -' simple and concise. Yes there are other ways; but your example was nicely done.