473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

ODBC problem.

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
4 3541
-----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
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
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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Sean Anderson | last post by:
ODBC under System DSN Setup Access Driver give it the DSN (Data Source Name) MSA Click on Select and point to the myfile.mdb (your database file)
3
by: Joe | last post by:
Python 2.4 Windows XP SP2 MS Access 2000 mx.ODBC 2.0.7 Problem data truncation occuring (here's the actual error message): mxODBC.Warning: ('01004', 5, ' String data, right truncated on...
11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
1
by: simianphile | last post by:
OK, I had a problem that I've now fixed but I can't really understand what was causing it in the first place. I have an intranet site that uses basic authentication to allow users to view and...
0
by: Kong Li | last post by:
Follow up to this thread, the latest Oracle 9i release 2 patchset (9.2.0.5) fixed the handle count leak problem. The problem is in Oracle client component. Thanks. Kong ----- From: Kong...
5
by: SerGioGio | last post by:
Hello, I am going nuts. I am trying to connect to my local ORACLE instance using ODBC. It used to work few weeks ago, but it fails now. Connection with: - SQL*plus: connection works! -...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
8
by: acb | last post by:
Hello, I am a beginner in ASP.NET and C# having programmed in VB (not the .NET flavour) in the past. I am looking for assistance in converting a functional VB.NET aspx page to C#. I am trying...
2
by: Frank Millman | last post by:
Hi all I have found a problem using MS Sql Server connecting via the odbc module from python-win32. I am liaising with Mark Hammond, and he is trying to help, but he is very busy, and I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.