Subject Re: [firebird-support] Calling Firebird from Excel?
Author Bart Smissaert
This is some simple VBA code that demonstrates how this can be done.
In Excel you need to set a reference (in the VBE: Tools, References) to
Microsoft ActiveX Data Objects 2.x Library.

Option Explicit
Public oADOConn As ADODB.Connection

Sub OpenDBConnection(strConnString As String, _
Optional strUserName As String, _
Optional strPassWord As String)

If oADOConn Is Nothing Then
Set oADOConn = New ADODB.Connection
End If
If oADOConn.State = 0 Then
oADOConn.Open strConnString, strUserName, strPassWord
End If

End Sub

Sub testing()

Dim strConn As String
Dim strSQL As String
Dim oRs As ADODB.Recordset
Dim arr() As Variant
Dim arr2() As Variant

strConn = "Driver={Firebird/InterBase(r) driver};" & _
"Dbname=localhost:C:\folder\xxx.fdb;" & _
"PWD=aaaa;" & _

OpenDBConnection strConn

Set oRs = New ADODB.Recordset


oRs.Open strSQL, oADOConn, adOpenForwardOnly, adLockReadOnly

arr = oRs.GetRows

arr2 = Transpose_Array(arr)

Range(Cells(1), Cells(UBound(arr2) + 1, UBound(arr2, 2) + 1)) = arr2

End Sub

Function Transpose_Array(vArr() As Variant) As Variant()

If Not IsArray(vArr) Then
Transpose_Array = vArr
Exit Function
End If

Dim r As Long
Dim c As Long
Dim LB As Long
Dim UB1 As Long
Dim UB2 As Long
Dim arrT() As Variant

LB = LBound(vArr)
UB1 = UBound(vArr)
UB2 = UBound(vArr, 2)

ReDim arrT(LB To UB2, LB To UB1)

For r = LB To UB1
For c = LB To UB2
arrT(c, r) = vArr(r, c)
Next c
Next r

Transpose_Array = arrT

End Function


On Tue, May 1, 2012 at 2:33 PM, Maury Markowitz

> **
> In a former life I was a SQLServer+Excel guru, but today I am merely the
> noob. Does anyone have some sample VBA of calling Firebird via ADODB from
> Excel, or Office in general? I'm sure I can get up to speed quickly, but
> that first step...

[Non-text portions of this message have been removed]