error with odbc function with no results returned

[edg126]

There have been similar posts like this before I feel, but it hasn't helped me with the problems I'm having.

In access 07 I'm automating some QA steps, and will be running a bunch queries against our oracle database. If there are results I want to print the text, however I figured it would be optimal to run the query, if there are results print them rather than running one query on a count(*) which will return results, and if the count is greater than 0 then run a second query to get the results.

The function I wrote is:

Public Function RunQuery2(ByVal Query As String)
Dim OdbcDSN
Dim connect, resultSet
Dim intPosition

OdbcDSN = "DSN=DBRAW;UID=uname;PWD=pword"
Set connect = CreateObject("ADODB.Connection")
connect.Open OdbcDSN
Set resultSet = connect.Execute(Query)

If InStr(UCase(Query), "SELECT") = 1 Then 'Only return results if it's select query. inserts and deletes skip this step. this can be buggy if doing a nested select query in a delete statement
If (Not resultSet.EOF) And (Not resultSet.BOF) Then 'begin of file blank and end of file blank
RunQuery2 = resultSet.GetRows
resultSet.Close
Else
Set RunQuery2 = Nothing
End If
Else
Set RunQuery2 = Nothing
End If
connect.Close
Set connect = Nothing

End Function

The call is:

Dim SQLReturnedResults As Variant

SQLReturnedResults = RunQuery2("SELECT owner, table_name FROM all_tables where 1=2;")
'SQLReturnedResults = RunQuery2("SELECT owner, table_name FROM all_tables;")
If IsNull(SQLReturnedResults) Then
MsgBox "no results found"
Else
MsgBox "results Found"
End If



If results are found (second query)it returns correctly.
No results (first query) is an error of:

Run-time error '91':
Object variable or with block variable not set.

Any ideas? Also, if that function the proper way to run a lot of queries (well, like 10-20) with having to open and close the connection each time?

Thanks for any advice!

[hansup]

If results are found (second query)it returns correctly.
No results (first query) is an error of:

Run-time error '91':
Object variable or with block variable not set.

Which line in the function is highlighted when you get that error?

[edg126]

That would have helped, sorry. The error shows up on the line:

SQLReturnedResults = RunQuery2("SELECT owner, table_name FROM all_tables where 1=2;")

[hansup]

Good. I don't believe your problem actually has anything to do with ODBC.

I changed the RunQuery2 function's connection assignment to this:

Set connect = CurrentProject.Connection

... then used a SELECT statement based on a local Jet table. I got the same error with a SELECT statement which returns no records.

I think the problem is due to the fact that RunQuery2 returns the Nothing object for no records vs. a Variant array if records found. See this copied from the Immediate Window:

? TypeName(RunQuery2("SELECT * FROM food WHERE 1=2;"))
Nothing

? TypeName(RunQuery2("SELECT * FROM food"))
Variant()

In the first case, no records, the calling code would have to do a Set assignment:

Set SQLReturnedResults = RunQuery2("SELECT * FROM food WHERE 1=2;")

In a case where records are returned, a regular assignment without Set:

SQLReturnedResults = RunQuery2("SELECT * FROM food")

I suspect your quickest route to joy would be to simply change the return values in the RunQuery2 function:

'Set RunQuery2 = Nothing
RunQuery2 = Null

Then use a simple assignment in the calling code:
SQLReturnedResults = RunQuery2("SELECT * FROM food WHERE 1=2;")

[edg126]

Interesting, that worked. I thought I had tried that before but I guess not. I appreciate your help -- I've been trying a bunch of different combinations to get it to work and just couldn't figure it out.

Thanks!

[hansup]

You're welcome.

I don't understand the details of your situation. However I'll show you a different approach, on the chance it may be useful.

Since you apparently can use ODBC to connect to your Oracle tables, I would link them to the Access database and use a DAO, instead of ADO, recordset.

Public Sub ToPrintOrNotToPrint()
Dim strSql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
'strSql = "SELECT * FROM food WHERE 1=2;"
strSql = "SELECT * FROM food"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)
With rs
If .BOF And .EOF Then
MsgBox "no results found"
Else
MsgBox "results Found"
'do your print thing here
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub

To me, this seems like a simpler direct approach, not requiring a separate RunQuery2 function.