Forgot to say that this needs the Firebird ODBC driver to be set up.

RBS

On Wed, May 2, 2012 at 12:03 AM, Bart Smissaert <bart.smissaert@...>wrote:

> 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;" & _
> "UID=bbbb;"
>
> OpenDBConnection strConn
>
> Set oRs = New ADODB.Recordset
>
> strSQL = "SELECT * FROM TABLE1"
>
> 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
>
>
> RBS
>
>
>
> On Tue, May 1, 2012 at 2:33 PM, Maury Markowitz <maury.markowitz@...
> > wrote:
>
>> **
>>
>>
>> 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]