Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

Oh, I think this might be in. According to some forum posts over on MS "Live" 
(love that name!) ADODB does not run under 64-bit. Sweet!

Chime in if you're using ADO on 64-bit Excel, this post might just be wrong.

According to the same post, there are other technologies I might have to use, 
but I'm not entirely clear whether or not these can be used from within Excel.



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

RE: [firebird-support] Calling Firebird from Excel?

[Michael Möhle]

Maybe it is the following problem:
http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissue
s/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13

Michael

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Maury Markowitz
Sent: Thursday, May 03, 2012 7:47 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Calling Firebird from Excel?

On 2012-05-03, at 12:56 PM, Bart Smissaert wrote:

>> Yes, I have tried calling the DSN from the connection string in VBA
> 
> Can you show that exact connection string?

   conn.Open "DSN=Fishbowl;", "GONE", "FISHING"


Wait wait wait… is there a possibility that I'm using the wrong ADO inside
Excel? Is there a separate 64-bit version that I should be including?



++

Visit http://www.firebirdsql.org and click the Resources item on the main
(top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 12:56 PM, Bart Smissaert wrote:

>> Yes, I have tried calling the DSN from the connection string in VBA
> 
> Can you show that exact connection string?

   conn.Open "DSN=Fishbowl;", "GONE", "FISHING"


Wait wait wait… is there a possibility that I'm using the wrong ADO inside 
Excel? Is there a separate 64-bit version that I should be including?



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

> Yes, I have tried calling the DSN from the connection string in VBA

Can you show that exact connection string?

RBS


On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-03, at 12:49 PM, Leyne, Sean wrote:
>> Let's start by setting up an ODBC DSN -- we will use that to ensure that
>> the basic connectivity is working.
>>
>> When you press the "Test" button on the ODBC DSN want happens?
>
> It works perfectly.
>
> And that DNS also works perfectly in iSQL and many other SQL-front-end
> programs.
>
> And one of those working programs is MSQuery, which happily dumps the
> expected data into Excel.
>
> But when I call that same DNS from ADODB in VBA, it fails. Yes, I have tried
> calling the DSN from the connection string in VBA. That fails too.
>
>
>

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

Yes, you are right, but I took it that worked otherwise no point
in trying to use it in Excel.
Also I think he did manage to get data in Excel via Data, Import
external data etc.

RBS



On 5/3/12, Leyne, Sean  wrote:
> Bart,
>
>> > The driver box says "IscDbc".
>>
>> That is not a file name as far as I know.
>> The file I have is C:\WINDOWS\system32\OdbcJdbc.dll
>
> Let's step back.
>
> Let's start by setting up an ODBC DSN -- we will use that to ensure that the
> basic connectivity is working.
>
> When you press the "Test" button on the ODBC DSN want happens?
>
>
> Sean
>

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 12:49 PM, Leyne, Sean wrote:
> Let's start by setting up an ODBC DSN -- we will use that to ensure that the 
> basic connectivity is working.
> 
> When you press the "Test" button on the ODBC DSN want happens?

It works perfectly.

And that DNS also works perfectly in iSQL and many other SQL-front-end programs.

And one of those working programs is MSQuery, which happily dumps the expected 
data into Excel.

But when I call that same DNS from ADODB in VBA, it fails. Yes, I have tried 
calling the DSN from the connection string in VBA. That fails too.

RE: [firebird-support] Calling Firebird from Excel?

[Leyne, Sean]

Bart,

> > The driver box says "IscDbc".
> 
> That is not a file name as far as I know.
> The file I have is C:\WINDOWS\system32\OdbcJdbc.dll

Let's step back.

Let's start by setting up an ODBC DSN -- we will use that to ensure that the 
basic connectivity is working.

When you press the "Test" button on the ODBC DSN want happens?


Sean

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

I am on Firebird 1.5, so maybe my setup and code doesn't apply to you.
The code I showed you is for a DSN-less connection.
You could try instead with a DSN like for example this connection string:

DSN=DSN_Name;UID=;PWD=


RBS


On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-03, at 11:59 AM, Bart Smissaert wrote:
>>> The driver box says "IscDbc".
>>
>> That is not a file name as far as I know.
>> The file I have is C:\WINDOWS\system32\OdbcJdbc.dll
>
>
> So I went to the System32 folder… but are we not talking about 64bit stuff?
> Do they put 64bit stuff in System32?!
>
> Looking in the folder I see a range of Odbc___.dll files. The ones of
> interest appear to be OdbcFb.dll, .lib and .pdb
>
> There is also a CHM. It does not contain any reference to OdbcFb.dll, but it
> does refer to IscDbc.dll and OdbcJdbc.dll, neither of which exist on my
> system. The file also refers to itself as OdbcJdbc.chm, when that is not its
> actual name.
>
> So I suspect that the driver was recently renamed from OdbcJdbc.dll to
> OdbcFb.dll
>
> However, I also strongly suspect that none of this would have any effect on
> the problem. In particular, the help file has a number of connection string
> examples, but all of them are the same as what we are attempting here, with
> no luck.
>
> The file that supplied the driver can be found here:
>
> http://www.firebirdsql.org/en/odbc-driver/
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 11:59 AM, Bart Smissaert wrote:
>> The driver box says "IscDbc".
> 
> That is not a file name as far as I know.
> The file I have is C:\WINDOWS\system32\OdbcJdbc.dll


So I went to the System32 folder… but are we not talking about 64bit stuff? Do 
they put 64bit stuff in System32?!

Looking in the folder I see a range of Odbc___.dll files. The ones of interest 
appear to be OdbcFb.dll, .lib and .pdb

There is also a CHM. It does not contain any reference to OdbcFb.dll, but it 
does refer to IscDbc.dll and OdbcJdbc.dll, neither of which exist on my system. 
The file also refers to itself as OdbcJdbc.chm, when that is not its actual 
name.

So I suspect that the driver was recently renamed from OdbcJdbc.dll to 
OdbcFb.dll

However, I also strongly suspect that none of this would have any effect on the 
problem. In particular, the help file has a number of connection string 
examples, but all of them are the same as what we are attempting here, with no 
luck.

The file that supplied the driver can be found here:

http://www.firebirdsql.org/en/odbc-driver/



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

> The driver box says "IscDbc".

That is not a file name as far as I know.
The file I have is C:\WINDOWS\system32\OdbcJdbc.dll

RBS


On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-03, at 11:15 AM, Michael Möhle wrote:
>> It looks you may have a different driver than me.
>> What does it say in the Driver box under System DSN, select the
>> Firebird/Interbase driver and then Confirgure?
>> I have there lscDbc and the file is ODBCJDNC.dll
>> Different driver may mean different connection string needed.
>
> The driver box says "IscDbc". It's a combo, but there's nothing else in it.
>
> I do not have that file, where is it being referred to?
>
> The file I have is fbclient64.dll. That is referred to in the Client line of
> the Configure… screen.
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 11:51 AM, Bart Smissaert wrote:
> Here are various ODBC connection strings for Firebird:
> http://www.connectionstrings.com/firebird

No joy. I tried them all, they all fail with the same useless error message.

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 11:15 AM, Michael Möhle wrote:
> It looks you may have a different driver than me.
> What does it say in the Driver box under System DSN, select the
> Firebird/Interbase driver and then Confirgure?
> I have there lscDbc and the file is ODBCJDNC.dll
> Different driver may mean different connection string needed.

The driver box says "IscDbc". It's a combo, but there's nothing else in it.

I do not have that file, where is it being referred to?

The file I have is fbclient64.dll. That is referred to in the Client line of 
the Configure… screen.



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

Here are various ODBC connection strings for Firebird:
http://www.connectionstrings.com/firebird

RBS


On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-03, at 11:34 AM, Thomas Steinmaurer wrote:
>
>> Open Windows Task Manager. On Windows 64-bit. If you see a "*32" behind
>> the process name, then it is 32-bit, otherwise 64-bit.
>
> It is 64 bit.
>
> That *suggests* that I'm using the correct driver, installed from
> Firebird_ODBC_2.0.0.150_x64.exe
>
> What is interesting, however, is that the error message implied it was
> trying to contact the 32bit version of some dll.

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 11:34 AM, Thomas Steinmaurer wrote:

> Open Windows Task Manager. On Windows 64-bit. If you see a "*32" behind 
> the process name, then it is 32-bit, otherwise 64-bit.

It is 64 bit.

That *suggests* that I'm using the correct driver, installed from 
Firebird_ODBC_2.0.0.150_x64.exe

What is interesting, however, is that the error message implied it was trying 
to contact the 32bit version of some dll.

Re: [firebird-support] Calling Firebird from Excel?

[Thomas Steinmaurer]

>> Following Versions must be installed:
>>
>> -  Excel 64 Bit:
>>
>> -  Excel 32 Bit:
>>
>
> Hey Michael, is there an easy way to see which version of Excel I'm running?
>
> I think this may be the issue...

Open Windows Task Manager. On Windows 64-bit. If you see a "*32" behind 
the process name, then it is 32-bit, otherwise 64-bit.


-- 
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

Sorry, should read ODBCJDBC.dll

RBS


On 5/3/12, Michael Möhle  wrote:
> Following Versions must be installed:
>
> -  Excel 64 Bit:
>
> o   Firebird Client 64 Bit (included in normal Firebird Server Install)
>
> o   Firebird ODBC 64 Bit
>
> -  Excel 32 Bit:
>
> o   Firebird Client 32 Bit (included in normal Firebird Server Install)
>
> o   Firebird ODBC 32 Bit
>
>
>
> Michael
>
>
>
> It looks you may have a different driver than me.
> What does it say in the Driver box under System DSN, select the
> Firebird/Interbase driver and then Confirgure?
> I have there lscDbc and the file is ODBCJDNC.dll
> Different driver may mean different connection string needed.
>
> RBS
>
> On 5/3/12, Maury Markowitz   > wrote:
>> On 2012-05-03, at 9:41 AM, Maury Markowitz wrote:
>>
>>> On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
 What is your ODBC driver?
>>>
>>> I don't know, it came with the Fishbowl system. Is there a way to get
> more
>>> information?
>>
>> Ok, I found it:
>>
>> Firebird/Interbase® driver, 2.00.00.150, IBPhoenix Inc., ODBCFB.DLL,
>> 11/4/2010
>>
>> 
>>
>> ++
>>
>> Visit http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++
>> Yahoo! Groups Links
>>
>>
>>
>>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 11:15 AM, Michael Möhle wrote:

> Following Versions must be installed:
> 
> -  Excel 64 Bit:
> 
> -  Excel 32 Bit:
> 

Hey Michael, is there an easy way to see which version of Excel I'm running?

I think this may be the issue...

RE: [firebird-support] Calling Firebird from Excel?

[Michael Möhle]

Following Versions must be installed:

-  Excel 64 Bit:

o   Firebird Client 64 Bit (included in normal Firebird Server Install)

o   Firebird ODBC 64 Bit

-  Excel 32 Bit:

o   Firebird Client 32 Bit (included in normal Firebird Server Install)

o   Firebird ODBC 32 Bit

 

Michael

 

It looks you may have a different driver than me.
What does it say in the Driver box under System DSN, select the
Firebird/Interbase driver and then Confirgure?
I have there lscDbc and the file is ODBCJDNC.dll
Different driver may mean different connection string needed.

RBS

On 5/3/12, Maury Markowitz mailto:maury.markowitz%40gmail.com> > wrote:
> On 2012-05-03, at 9:41 AM, Maury Markowitz wrote:
>
>> On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
>>> What is your ODBC driver?
>>
>> I don't know, it came with the Fishbowl system. Is there a way to get
more
>> information?
>
> Ok, I found it:
>
> Firebird/Interbase® driver, 2.00.00.150, IBPhoenix Inc., ODBCFB.DLL,
> 11/4/2010
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>





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

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

It looks you may have a different driver than me.
What does it say in the Driver box under System DSN, select the
Firebird/Interbase driver and then Confirgure?
I have there lscDbc and the file is ODBCJDNC.dll
Different driver may mean different connection string needed.

RBS



On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-03, at 9:41 AM, Maury Markowitz wrote:
>
>> On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
>>> What is your ODBC driver?
>>
>> I don't know, it came with the Fishbowl system. Is there a way to get more
>> information?
>
> Ok, I found it:
>
> Firebird/Interbase® driver, 2.00.00.150, IBPhoenix Inc., ODBCFB.DLL,
> 11/4/2010
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

>  Is there a way to get more information?

Yes, in Windows: Start, Settings, Control panel, Administrative Tools,
Data Sources (ODBC), System DSN

Useful to know what ODBC driver we are dealing with here!


RBS



On 5/3/12, Maury Markowitz  wrote:
> On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
>> What is your ODBC driver?
>
> I don't know, it came with the Fishbowl system. Is there a way to get more
> information?
>
>>  strConn = "Driver={Firebird/InterBase(r) driver};" & _
>>"Dbname=c:\Program Files\Fishbowl\database\data\AS SOLAR.FDB;"
>>
>>  OpenDBConnection strConn, "XXX", "YYY"
>
> Ahhh, progress, but not for the reason you think!
>
> I used the string above and it did not work - same "Automation Error". So
> then I started suspecting that perhaps this was due to permissions. So I
> opened the same code under an admin account. It still doesn't work, but at
> least now I have more information!
>
> [ODBC Firebird Driver]Unable to connect to data source: library 'gds32.dll'
> failed to load.
>
> I have noticed one difference between the ODBC manager setup and this
> connection string: the ODBC setup includes a "client" line. However,
> adding…
>
> Client=c:\Program Files\Fishbowl\odbc\fbclient64.dll;
>
> to the connection string didn't improve matters.
>
> I *suspect* this has something to do with 32/64 bit issues, or a
> mis-registered DLL.
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++
> Yahoo! Groups Links
>
>
>
>




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-03, at 9:41 AM, Maury Markowitz wrote:

> On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
>> What is your ODBC driver?
> 
> I don't know, it came with the Fishbowl system. Is there a way to get more 
> information?

Ok, I found it:

Firebird/Interbase® driver, 2.00.00.150, IBPhoenix Inc., ODBCFB.DLL, 11/4/2010



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

By the way, I don't know if this is important or not, I can access the DB fine 
through MSQuery and the DSN I set up. From within the same Excel instance.

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-02, at 6:32 PM, Bart Smissaert wrote:
> What is your ODBC driver?

I don't know, it came with the Fishbowl system. Is there a way to get more 
information?

>  strConn = "Driver={Firebird/InterBase(r) driver};" & _
>"Dbname=c:\Program Files\Fishbowl\database\data\AS SOLAR.FDB;"
> 
>  OpenDBConnection strConn, "XXX", "YYY"

Ahhh, progress, but not for the reason you think!

I used the string above and it did not work - same "Automation Error". So then 
I started suspecting that perhaps this was due to permissions. So I opened the 
same code under an admin account. It still doesn't work, but at least now I 
have more information!

[ODBC Firebird Driver]Unable to connect to data source: library 'gds32.dll' 
failed to load.

I have noticed one difference between the ODBC manager setup and this 
connection string: the ODBC setup includes a "client" line. However, adding…

Client=c:\Program Files\Fishbowl\odbc\fbclient64.dll;

to the connection string didn't improve matters.

I *suspect* this has something to do with 32/64 bit issues, or a mis-registered 
DLL.



++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

What is your ODBC driver?

Try like this:

  strConn = "Driver={Firebird/InterBase(r) driver};" & _
"Dbname=c:\Program Files\Fishbowl\database\data\AS SOLAR.FDB;"

  OpenDBConnection strConn, "XXX", "YYY"


RBS


On Wed, May 2, 2012 at 11:20 PM, Maury Markowitz
wrote:

> **
>
>
> On 2012-05-02, at 5:59 PM, Bart Smissaert wrote:
>
> > Post your code and tell where you get the error.
> > Should be something simple
>
> Yeah, normally is simple. Just wish the error was more descriptive!
>
> Const cnn = "Driver={Firebird/InterBase(r) driver};Dbname=c:\Program
> Files\Fishbowl\database\data\AS SOLAR.FDB;UID=XXX;PWD=YYY;"
> Public conn As ADODB.Connection
>
> Sub OpenDBConnection()
> If conn Is Nothing Then
> Set conn = New ADODB.Connection
> End If
> If conn.State = 0 Then
> conn.Open cnn
> End If
> End Sub
>
> Happens right on the conn.open. I assume it's a problem in the cnn string,
> but what? I tried {}'s around the dbname, no luck.
>
> The connection to the DB works when I use ODBC. I have a DSN pointed to it
> as well, but when I try to use the DSN based cnn it doesn't work and gives
> me two errors :-)
>
> Const cnn = "ODBC;DSN=Fishbowl;UID=XXX;PWD=YYY;"
> 
>


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





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-02, at 5:59 PM, Bart Smissaert wrote:

> Post your code and tell where you get the error.
> Should be something simple

Yeah, normally is simple. Just wish the error was more descriptive!

Const cnn = "Driver={Firebird/InterBase(r) driver};Dbname=c:\Program 
Files\Fishbowl\database\data\AS SOLAR.FDB;UID=XXX;PWD=YYY;"
Public conn As ADODB.Connection

Sub OpenDBConnection()
 If conn Is Nothing Then
   Set conn = New ADODB.Connection
 End If
 If conn.State = 0 Then
   conn.Open cnn
 End If
End Sub


Happens right on the conn.open. I assume it's a problem in the cnn string, but 
what? I tried {}'s around the dbname, no luck. 

The connection to the DB works when I use ODBC. I have a DSN pointed to it as 
well, but when I try to use the DSN based cnn it doesn't work and gives me two 
errors :-)

Const cnn = "ODBC;DSN=Fishbowl;UID=XXX;PWD=YYY;"

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

Post your code and tell where you get the error.
Should be something simple.

RBS


On Wed, May 2, 2012 at 10:52 PM, Maury Markowitz
wrote:

> **
>
>
> On 2012-05-01, at 7:03 PM, Bart Smissaert wrote:
>
> > This is some simple VBA code that demonstrates how this can be done.
>
> Thanks Bart, that did help.
>
> Sadly, I'm getting the dreaded "Automation Error". Likely a missing
> semicolon...
> 
>


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





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Maury Markowitz]

On 2012-05-01, at 7:03 PM, Bart Smissaert wrote:

> This is some simple VBA code that demonstrates how this can be done.

Thanks Bart, that did help.

Sadly, I'm getting the dreaded "Automation Error". Likely a missing semicolon...

Re: [firebird-support] Calling Firebird from Excel?

[Bart Smissaert]

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 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=;" & _
> "UID=;"
>
>   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  > 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]





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[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=;" & _
"UID=;"

  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
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]





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] Calling Firebird from Excel?

[Michael Ludwig]

Maury Markowitz schrieb am 01.05.2012 um 09:33 (-0400):
> 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...

I've never done VBA, only VBS - but I think ADO via VBA would require
a COM component for the Firebird client. As far as I know, there is no
such beast. Wait, looks like there is an OLE DB provider based on COM,
and maybe it has what you want; not free, though; take a look:

http://www.ibprovider.com/eng/documentation/firebird_interbase_odbc_drivers.html

Michael

[firebird-support] Calling Firebird from Excel?

[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...