Reading data from a database without putting the value to any cell

[chamster]

I'd like to retrieve information from using the following syntax.

SELECT something, elsething FROM dbo.table WHERE something = 3

This will give me exactly two elements. What i'd like the computer to do is to only return that value as a function. So, i've got something like this.

[vba]Private Function GetSome (ByVal parameter As Long) As Long
Dim strSQL As String
strSQL = "SELECT something, elsething FROM dbo.table WHERE something = " & parameter
GetSome = ??? ' here, i need some help returning the valu of "elsething" ONLY
End Function[/vba]

Is this doable in Excel? How?

[Bob Phillips]

If you use ADO you can pull back a recordset which you can copy to an array amd pass that to GetSome.

[chamster]

Thanks. It sounds like what i'm aiming for. Now, HOW can i do the thing you just described?

[Bob Phillips]

Here is a simple example getting data from an Access database using late binding

[vba]

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub
[/vba]

[chamster]

I've been fighting with this for over a month and i just can't make it work. I changed the solution offered to fit the settings of my local system in the following way.

[VBA]
Public Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
sConnect = "ODBC;DSN=rTDBH;UID=rTDBRead;PWD=rTDBRead;APP=Microsoft Office 2003;WSID=GBG90;DATABASE=rTDBH"
sSQL = "SELECT * FROM dbo.RouteTravelTimeAggregate WHERE RouteID = 1101"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
[/VBA]

During opening the oRS object i get strange errors. SOmething about automation error and undefined error. The number of the error is something negative. I'm about to harakiri myself... Help?

[Bob Phillips]

Why are you using ODBC, why not OLE?

[chamster]

Because when i started with VBA i knew a bit less than jack and used whatever source code that popped out when i recorded a macro. Later on, i assumed that it's a valid way, since the results are coming to the screen and the program works to my employers satisfaction.

In short - i do that by pure coincidence.

Having said that, i'm fully open to suggestions. Next week, i'll be off working for Volvo with other stuff, so this part needs to get done by thursday. Everything goes, by other words.

After some paniced looking i started to use this connection string instead.
sConnect = "Provider=sqloledb;Data Source=gbg-its-db2;Initial Catalog=rTDBH;User Id=rTDBRead;Password=rTDBRead;"
Do you have issues with it?

[Bob Phillips]

So, is it an Access database, and what is the full path?

[chamster]

As far as i know:
1. MS SQL Server 2005
2. Path is unknown/unspecified

It needs pointing out that i get the data as of now, thanks to the help i got a month ago (only the connection string needed adapting). So, my wondering is not so much "how do i make it work?" but "can i make it work in a better way?".

ALso, i just realized one more thing. How can i operate oRS or ary in the code above so i get to know how many rows/columns there are contained in them?

[Charlize]

[VBA]oRs.RecordCount[/VBA]gives you the no of records.

[rory]

You can check the LBound and Ubound of the array. Generally you should try and avoid using 'SELECT *' statements unless you actually need all the data returned.

[chamster]

I get -1 as an answer, even though i get stuff from ary later...

Also - how do i know how many rows and how many columns there are in the record set? I i got you correctly, .RecordCounts will give me only the product of these two, won't it?

[rory]

And bear in mind that with ADO, you will often find that the RecordCount property simply returns -1, depending on various factors.

[Charlize]

[VBA]oRs.Fields.Count[/VBA]gives you number of fields.