Danny's VBA Scripts

DAO Example Importing SQL Data Using ODBC

• Project Management Template Kit
• Get Everify To Uncover The Truth About Someone

The final example of using DAO to bring data into your Excel project will focus on getting data from an SQL server (or other ODBC-compliant database). The process is identical to what we just did in our previous example, with the exception of a new connection string:

sConn = "ODBC;DATABASE=msdb;DSN=mySQL"

We're still using the ODBC reference in the string, but now we're passing in the database name and the DSN name. Here's the complete code. (You must reference a valid database and DSN for this to provide you with output.)

Sub GetDAOSQLODBC() Dim wrk As DAO.Workspace Dim cnn As DAO.Connection Dim rs As DAO.Recordset Dim sConn As String Dim xlSheet As Worksheet Dim iFieldCount As Integer Dim i As Integer

Set xlSheet = Sheets("Sheet1")

xlSheet.Activate

Range("A1").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range(

sConn = "ODBC;DATABASE=msdb;DSN=mySQL"

Set wrk = CreateWorkspace("", "", "", dbUseODBC) Set cnn = wrk.OpenConnection("", , , sConn)

Set rs = cnn.OpenRecordset("SELECT * FROM msdbms", dbOpenDynamic)

iFieldCount = rs.Fields.Count For i = 1 To iFieldCount xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name Next i xlSheet.Cells(2, 1).CopyFromRecordset rs xlSheet.Select Range(

"A1").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range(

"A1").Select

'close workspace wrk.Close

'release objects Set xlSheet = Nothing Set rs = Nothing Set wrk = Nothing Set cnn = Nothing End Sub

0 0