Example of querying from a database in VBA

This is a simple example of how to pull data from a datasource.

Prerequisites

First, from within the VBA editor, ensure that you have all the needed references. Under Tools->References, ensure that the Microsoft ActiveX Data Objects 2.6 or higher is selected.

Example

public sub myTest()

Dim cn As New ADODB.Connection

Dim rst As New ADODB.Recordset

On Error GoTo handleError

' This will ensure that the user will be prompted for a username/password

cn.Properties("Prompt") = 2

cn.Open "dsn=myODBCDSN;Pwd=;UID=;"

rst.ActiveConnection = cn

rst.Open "Select * from " & schemaName & "." & tablename

mRow = 0

If numRows > 0 Then

rst.MoveFirst

Do Until rst.EOF

mRow = mRow + 1

For mCol = 1 To rst.Fields.Count

' Copy the field names (ie, headers) if it is the first row

If (mRow = 1) Then

ActiveSheet.Cells(mRow + 3, mCol ).Value = rst.Fields(mCol - 1).Name

If (rst.Fields(mCol - 1).Type = 135) Then

' Date column

ActiveSheet.Columns(mCol).NumberFormat = "dd-mmm-yyyy"

End If

End If

If Not IsNull(rst.Fields(mCol - 1)) Then

tmpString = rst.Fields(mCol - 1)

If (Left(tmpString, 1) = "=") Then

tmpString = "'" + tmpString

End If

ActiveSheet.Cells(mRow + 4, i).Value = tmpString

End If

Next mCol

rst.MoveNext

Loop

End If

rst.Close

ActiveSheet.UsedRange.Columns.AutoFit

ActiveSheet.UsedRange.Rows.AutoFit

handleError:

If (Err.Number > 0) Then

ErrMsg = Error(Err)

MsgBox ("Error: " & ErrMsg)

End If

cn.Close

Set cn = Nothing

End Sub