I am getting a "User defined type not defined" error with the line:
Public Function GetConnection(db As DBConnection) As ADODB.connection
highlighted

I am trying to run a test macro but I am not sure how to envoke the 'db'
part of your function using:

Sub test()
Dim str, book, WSname , myDb As String
Dim x
myDB="DMCC1"
book = ActiveWorkbook.Name
WSname = "TEMP"
SQL = "select * from HEIDS.HE_ACAPS_LOCS"
x= SelectData(book, myDB, SQL, WSname)
End Sub

" wrote:

This is VBA that works with DB2. It might be helpful if your new to
SQL in Excel ...

(DBConnection is a Class Module. See code below.)

Option Explicit

Private Const DEFAULT_COMMAND_TIMEOUT = 2000

Public Function GetConnection(db As DBConnection) As ADODB.connection

Dim cn As ADODB.connection
Set cn = New ADODB.connection

On Error GoTo ErrorHandler
cn.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
cn.Open GenerateConnectionString(db), db.userName, db.password

Set GetConnection = cn
Exit Function

ErrorHandler:
Set cn = Nothing
Set GetConnection = Nothing
Exit Function

End Function

Public Function GenerateConnectionString(db As DBConnection) As String
Dim str As String
str = "Provider=SQLOLEDB.1;Data Source=" & db.server & _
";Initial Catalog=" & db.database & ";User ID=" & db.userName
& _
";Password=" & db.password
GenerateConnectionString = str
End Function


'Fetches data, inserts the data into a Worksheet named sheetName

Public Function SelectData(book As Workbook, db As DBConnection, SQL
As String, sheetName As String) As Integer

Dim connection As ADODB.connection
Set connection = CreateObject("ADODB.Connection")
Dim count As Integer
connection.Open DBUtils.GenerateConnectionString(db), db.userName,
db.password

Dim command As ADODB.command
Set command = CreateObject("ADODB.Command")

Set command.ActiveConnection = connection
command.CommandTimeout = DEFAULT_COMMAND_TIMEOUT
command.CommandType = adCmdText
command.CommandText = SQL


Dim sheet As Worksheet
Set sheet = book.Sheets(sheetName)
sheet.Visible = True
sheet.Cells.Clear

Dim rs As ADODB.Recordset
Set rs = command.Execute

Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Dim colCount As Integer
colCount = rs.Fields.count

Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

Set command = Nothing
SelectData = count
End Function



''''''''' Put this code in a Class Module named DBConnection '''''''''

'DatabaseConnectionInfo
Option Explicit

Dim propDatabase As String
Dim propServer As String
Dim propPort As String
Dim propPrjName As String
Dim propPassword As String

Public Property Get database() As String
database = propDatabase
End Property

Public Property Let database(newDatabase As String)
propDatabase = newDatabase
End Property

Public Property Get server() As String
server = propServer
End Property

Public Property Let server(newServer As String)
propServer = newServer
End Property

Public Property Get Port() As String
Port = propPort
End Property

Public Property Let Port(newPort As String)
propPort = newPort
End Property

Public Property Get userName() As String
userName = propPrjName
End Property

Public Property Let userName(newUserName As String)
propPrjName = newUserName
End Property

Public Property Get password() As String
password = propPassword
End Property

Public Property Let password(newPassword As String)
propPassword = newPassword
End Property