How to get sqlcode from ODBC call to IBM host

[MrStar]

I'm sending a sql to IBM host DB2 from MS Access through ODBC.

If I made an error in this SQL, I only got an error message 3146 from ODBC telling that ODBC call has failed.

Is it possible to get the correct sqlcode from Host DB2 ?

I have tried to use API call GetLastError, but it allways gives me a zero.

This is part of the code I use for sending my query to Host DB2.

Set QueCV = dbs.CreateQueryDef("")
With QueCV
.Connect = "ODBC;DATABASE=" & f_DB2System & ";UID=" & _
f_UserId & ";PWD=" & _
f_Psword & ";DSN=" & f_DB2System
.SQL = strSql
.ODBCTimeout = 0
.ReturnsRecords = True

Set HostRs = .OpenRecordset()

If I have an error in my sql, then the code will fail in 'Set HostRs = .OpenRecordset()' with err.number = 3176.

The variable strSql containes my sql, and it could look like this:
Select CARD_PRODUCT_ID FROM KSP0.CARD_PRODUCT
WHERE CARD_PRODUCT_ID in ('LSMC')
AND CURRENT DATE
BETWEEN START_DATE AND VALUE (EXPIRATION_DATE, DATE('31.13.9999'))
group by CARD_PRODUCT_ID ;

As you can see, I have made an error in the date, and Host will respond -181, but I can't get this sqlcode into Access !

Can anybody tell me how to do this ?

 

[SkipVought]

You could LINK the DB2 table and then use ACCESS SQL syntax instead of DB2 without ANY VBA.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrStar]

I have tried to link the db2 tables directly in Access. The result was that Access started to download tabels into local tables. This is huge tables ( I stopped linking with 89 mb for the first of 25 tables).
But perhaps I have done it wrong. I'm not so familier with linking through ODBC.

 

[SkipVought]

Linking does not download anything. It is a...

LINK.
[tt]
In the Table View, right-click,

select Link Tables

in Files of Type dropdown select ODBC

Look in any Data Sources tab for your DB2 data source name.

Scroll for your Group/Table
[/tt]
now you have a link to which you can query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]