-----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:[color=blue]
> 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
>[/color]
