Hey guys, I have an ODBC problem that has me stumped. I wrote a VBA
script to run in Microsoft Excel that pulls data out of an application
using that application's ODBC driver and puts it into Excel. I am
trying to translate the same program over to Microsoft Access and I ran
into a problem. Access locks up if the data I am querying for is not
present in the database.
To retrieve data from the database of the application, my Excel
program uses code like this:
With Sheets("Data").QueryTables.Add(Connection:="ODBC;D SN=insite;",
Destination _
:=Range("A13"))
.Sql = Array( SELECT Blah Blah FROM Blah Blah Blah )
To get at the same data, my Access program uses something like this:
Dim rst(30) As Recordset
Dim wrkODBC As Workspace
Dim condbs As Connection
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
Set condbs = wrkODBC.OpenDatabase("ODBC;DSN=insite;")
On Error Resume Next
Set rst(3) = condbs.OpenRecordset( SELECT Blah Blah FROM Blah Blah
Blah )
Blah Blah Blah substituted for readability's sake, as the actual
strings are lengthy.
Everything works fine as long as the table exists in the database. If
the table does not exist, my CPU usage goes to 100% and memory fills up
slowly. There are no errors raised in visual basic. The log file of
the calls to the ODBC driver seems to show a problem when SQLCancel is
called. SQLCancel is never called by the Excel script, but it is
called by the Access script and it is the last thing logged before the
lockup occurs.
The Access ODBC call Log:
----------------------------------------------------------------------------------------------
COPY(2~1 330-2a0 ENTER SQLExecDirect
HSTMT 08E82890
UCHAR * 0x096B0828 [ 162] "SELECT Blah Blah FROM Blah
Blah Blah"
SDWORD 162
COPY(2~1 330-2a0 EXIT SQLExecDirect with return code -1
(SQL_ERROR)
HSTMT 08E82890
UCHAR * 0x096B0828 [ 162] "SELECT Blah Blah FROM Blah
Blah Blah"
SDWORD 162
DIAG [S1000] Unknown table name 'Blah' (3)
COPY(2~1 330-2ac ENTER SQLCancel
HSTMT 08E82890
---------------------------------------------------------------------------------------------
And then Access locks up and there are no further entries in the log.
The Excel ODBC call Log:
-------------------------------------------------------------------------------------------------
-AUTOFA~1 32c-398 ENTER SQLExecDirect
HSTMT 03072010
UCHAR * 0x02B21460 [ 162] "SELECT Blah Blah FROM Blah
Blah Blah"
SDWORD 162
AUTOFA~1 32c-398 EXIT SQLExecDirect with return code -1
(SQL_ERROR)
HSTMT 03072010
UCHAR * 0x02B21460 [ 162] "SELECT Blah Blah FROM Blah
Blah Blah"
SDWORD 162
DIAG [S1000] Unknown table name 'Blah' (3)
AUTOFA~1 32c-398 ENTER SQLErrorW
HENV 03071540
HDBC 030715E8
HSTMT 03072010
WCHAR * 0x0012CB28 (NYI)
SDWORD * 0x0012CCD8
WCHAR * 0x0012C728
SWORD 256
SWORD * 0x0012CCE2
AUTOFA~1 32c-398 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 03071540
HDBC 030715E8
HSTMT 03072010
WCHAR * 0x0012CB28 (NYI)
SDWORD * 0x0012CCD8 (3)
WCHAR * 0x0012C728 [ 48] "Unknown table name 'Blah'"
SWORD 256
SWORD * 0x0012CCE2 (48)
---------------------------------------------------------------------------------------------
And excel just marches on to the next query without problems.
I think that this might be a bug in the ODBC driver. Either way I need
to find a way around this problem. I was thinking there might be a way
to get visual basic to handle the SQL error but I don't know how to
get that error into VB. I really know nothing about SQL and ODBC and
next to nothing about VBA, so if I am missing something obvious forgive
me.
Thanks for any help
Dave