Accessing external DB, and making query
 

Hi,

I'm new to VBA (Although I do have programming experience in other
languages).. I'm getting my feet wet, and trying to make a DB query to
a Firebird DB.

So far I have:

Dim dbMain As New ADODB.Connection
Dim rs As New ADODB.Recordset
dbMain.Open "DRIVER=Firebird/InterBase(r)
driver;UID=SYSDBA;PWD=masterkey;DBNAME=c:\temp.fdb "

rs.Open "SELECT sum(total_price) FROM headers", dbMain, adOpenKeyset,
adLockPessimistic

Dim tmp As Double
tmp = rs!Sum(total_price)
Sheets("Sheet1").Range(A1) = rs!Sum
-----

My problem is I'm not sure if rs.Open is retrieving the value, and
then the lines following where I attempt to assign that value have a
type mismatch.. I'm thinking it's becuase nothing is being returned in
the record set..

Can someone set me on the right path?

Thanks
David

Accessing external DB, and making query
 

I think you need to do:

Dim tmp As Double
tmp = rs.Fields(0)
Sheets("Sheet1").Range(A1) = tmp

Or just:

Sheets("Sheet1").Range(A1) = rs.Fields(0)

RBS

"David Wessell" wrote in message
ps.com...
Hi,

I'm new to VBA (Although I do have programming experience in other
languages).. I'm getting my feet wet, and trying to make a DB query to
a Firebird DB.

So far I have:

Dim dbMain As New ADODB.Connection
Dim rs As New ADODB.Recordset
dbMain.Open "DRIVER=Firebird/InterBase(r)
driver;UID=SYSDBA;PWD=masterkey;DBNAME=c:\temp.fdb "

rs.Open "SELECT sum(total_price) FROM headers", dbMain, adOpenKeyset,
adLockPessimistic

Dim tmp As Double
tmp = rs!Sum(total_price)
Sheets("Sheet1").Range(A1) = rs!Sum
-----

My problem is I'm not sure if rs.Open is retrieving the value, and
then the lines following where I attempt to assign that value have a
type mismatch.. I'm thinking it's becuase nothing is being returned in
the record set..

Can someone set me on the right path?

Thanks
David