Select Next Value from a DB2 System table

[Tiana Arylle]

Hi all -

I'm developing an Access 2007 front-end for a DB2 backend, and I need to
call an in-memory DB2 table to generate an ID for new records. I've been
told that the following SQL works, but I can't get Access to use it. (I'm
not a SQL guru, so I'm trusting the people who are...they just aren't Access
guru's).

SELECT NEXT VALUE FOR BOOK_ID
INTO :OUT_SEQ_OBJ
FROM SYSIBM.SYSDUMMY1
WITH UR;

Help?

Thanks!
~ Tia

 

[Albert D. Kallal]

that is a interesting syntax (it and not sql).

However, if they are telling you that syntax works, then
create a pass-through query, and type in the results.

Make sure you check the property sheet, and ensure you set that pass-though
to return values.

Save the query, and then run the query. It should produce a table like
result with one record that shows the next id....

if that works, then you can go:

dim rstNext as dao.recordset

rstNext = currentdb.OpenRecordSet("name of pass through")

msgbox "next record is " & rstNext(0)

rstNext.Close

 

[Tiana Arylle]

Thanks for answering Albert.

I've simplified the query to:

SELECT NEXT VALUE FOR BOOK_ID
FROM SYSIBM.SYSDUMMY1
WITH UR;

but it's still giving me the error:
"ODBC - call failed.

[IBM][CLI Driver][DB2] SQL0204N "UID.BOOK_ID" is an undefined name.
SQLSTATE-42704(#-204)"

Where "UID" is the ID I'm using in my ODBC connect string.

I decided to try creating a simple select query that I have working as a
pass-through and it threw the same error, until I created the select query in
Access' design view and converted it to a pass-through. That worked fine,
even though the SQL syntax was identical - but for some reason, when it's not
created as a pass-through, it doesn't pre-pend the UID to the field name.

Unfortunately, due to the type of query I need, I can't create the next
value query in Access' design view...or I don't know how to.

Thoughts? Thanks for your help!

~ Tia

 

[Tiana Arylle]

Fixed this one! Apparently I'm an idiot.

I figured out that Access requires the prefix for DB2 tables to be included
with the table name, but in direct DB2, the prefix isn't required...so my
DB2/SQL guys hadn't told me the prefix. Once I added the table prefix and an
underscore to the field name in the SQL (Book_ID) I was able to build a
pass-through query and pull the value in easily.

Thanks for trying to help! It did clear up some things

Thanks for answering Albert.

I've simplified the query to:

SELECT NEXT VALUE FOR BOOK_ID
FROM SYSIBM.SYSDUMMY1
WITH UR;

but it's still giving me the error:
"ODBC - call failed.

[IBM][CLI Driver][DB2] SQL0204N "UID.BOOK_ID" is an undefined name.
SQLSTATE-42704(#-204)"

Where "UID" is the ID I'm using in my ODBC connect string.

I decided to try creating a simple select query that I have working as a
pass-through and it threw the same error, until I created the select query in
Access' design view and converted it to a pass-through. That worked fine,
even though the SQL syntax was identical - but for some reason, when it's not
created as a pass-through, it doesn't pre-pend the UID to the field name.

Unfortunately, due to the type of query I need, I can't create the next
value query in Access' design view...or I don't know how to.

Thoughts? Thanks for your help!

~ Tia

that is a interesting syntax (it and not sql).

However, if they are telling you that syntax works, then
create a pass-through query, and type in the results.

Make sure you check the property sheet, and ensure you set that pass-though
to return values.

Save the query, and then run the query. It should produce a table like
result with one record that shows the next id....

if that works, then you can go:

dim rstNext as dao.recordset

rstNext = currentdb.OpenRecordSet("name of pass through")

msgbox "next record is " & rstNext(0)

rstNext.Close