By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

ODBC problem.

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need ODBC to connect to an Access (JET) table. You can set up
a DSN to the Access table & just use that DSN w/o the ODBC indicator:

With Sheets("Data").QueryTables.Add(Connection:="DSN=in site;", etc.

If you still get the hanging phenomenon try establishing the existence
of the table before attempting to read it. Here is a query that will
find the table you're looking for w/o trying to open it:

SELECT Count(*)
FROM msysobjects
WHERE Name="Orders"
AND Flags=0
AND Type=1

Here's how you'd run it (using ADO):

const SQL = "SELECT Count(*) FROM msysobjects WHERE " & _
"Flags=0 AND Type=1 AND Name="

dim cn as new adodb.connection
dim rs as new adodb.recordset

' If the Access version is before Access 2000, use 3.5 instead of 4.0
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDB.mdb"

' Substitute your table name for Orders.
' Keep the single quote delimiters around the table name.
set rs = cn.Execute (SQL & "'Orders'",, adCmdText)

if not rs.eof then
msgbox "table exists"
else
msgbox "table doesn't exist"
endif

rs.close
cn.close

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHAtYechKqOuFEgEQK0cACgjKqHOJgkkVw5jPfH9rvGq3/DvbMAn19N
sTrBtdUIqcmrVol+qi1xkJjH
=Ache
-----END PGP SIGNATURE-----
Dave wrote:
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

Nov 13 '05 #2

P: n/a
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need ODBC to connect to an Access (JET) table. You can set up a DSN to the Access table & just use that DSN w/o the ODBC indicator:

With Sheets("Data").QueryTables.Add(Connection:="DSN=in site;", etc.
If you still get the hanging phenomenon try establishing the existence of the table before attempting to read it. Here is a query that will
find the table you're looking for w/o trying to open it:

SELECT Count(*)
FROM msysobjects
WHERE Name="Orders"
AND Flags=0
AND Type=1


Hi, thanks for the response. Actually I don't think I can get at the
data in the database other than through ODBC. The application that
holds the database is not a an Access or other Microsoft application.
It is fairly sophisticated software for gathering large amounts of data
from industrial sensors. The reason I think the ODBC driver may have a
bug is because I have had difficulty running anything other than a
basic "SELECT" SQL statements, and also no one in my company has really
had to use the ODBC driver for anything that I know of. It was just
sort of provided as an incidental I think. Anyhow, I will try to run a
query like the one you suggest though.

Dave

Nov 13 '05 #3

P: n/a
Dave wrote:
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need ODBC to connect to an Access (JET) table. You can set


up
a DSN to the Access table & just use that DSN w/o the ODBC indicator:

With Sheets("Data").QueryTables.Add(Connection:="DSN=in site;",


etc.
If you still get the hanging phenomenon try establishing the


existence
of the table before attempting to read it. Here is a query that will
find the table you're looking for w/o trying to open it:

SELECT Count(*)
FROM msysobjects
WHERE Name="Orders"
AND Flags=0
AND Type=1

Hi, thanks for the response. Actually I don't think I can get at the
data in the database other than through ODBC. The application that
holds the database is not a an Access or other Microsoft application.
It is fairly sophisticated software for gathering large amounts of data
from industrial sensors. The reason I think the ODBC driver may have a
bug is because I have had difficulty running anything other than a
basic "SELECT" SQL statements, and also no one in my company has really
had to use the ODBC driver for anything that I know of. It was just
sort of provided as an incidental I think. Anyhow, I will try to run a
query like the one you suggest though.

Dave


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In that case, you will need the ODBC driver. I thought you were
connecting to an Access db. Also, since you are not connecting to an
Access db the query I gave you will not work. If the db you are
connecting to complies w/ the SQL-92 standard the following query may
get the same results as the Access query.

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '<table name>'

Substitute your table name for <table name>.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkW4yYechKqOuFEgEQLcagCg9DYqOt758DhFSMnMrnJcis 7cvtQAn0PQ
zRgcHPndMF1Xzu822UCUFHzb
=6gOh
-----END PGP SIGNATURE-----
Nov 13 '05 #4

P: n/a

I will try to run that. I have been having problems with anything
other than just a basic select statement. I am not sure the driver
supports the Count function. I have tried doing count's and sum's but
it has just locked up on me. I have tried them from MS Query also and
had the same problem, so I don't think it is my syntax.

I was reading about SQL catalog functions and it seems like SQLTables
is just the function I need. I guess I would have to declare it as an
api function of the odbc driver before I could use it. I'm not too
experienced with that, but I will also give that a shot.

Dave

MGFoster wrote:

In that case, you will need the ODBC driver. I thought you were
connecting to an Access db. Also, since you are not connecting to an
Access db the query I gave you will not work. If the db you are
connecting to complies w/ the SQL-92 standard the following query may
get the same results as the Access query.

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '<table name>'

Substitute your table name for <table name>.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkW4yYechKqOuFEgEQLcagCg9DYqOt758DhFSMnMrnJcis 7cvtQAn0PQ
zRgcHPndMF1Xzu822UCUFHzb
=6gOh
-----END PGP SIGNATURE-----


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.