edg126
05-19-2011, 06:43 AM
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!
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!