ODBC export to iSeries

I am very new to Access. I have ODBC connections from iSeries databases into
my Access 2007 database, and SQL Server connections to SQL databases. The
updates to my SQL databases work fine. My ODBC connection to my ACCESS 2007
database works fine for viewing the data. However, when I try to make any
updates to the iSeries files by using Access, I just keep getting the same
error message "ODBC - insert on a linked table table failed. (Error 3155)."

Here is the actual message that displays in the dialog box:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - GLACTREF1 in FMISLIB not
valid operation (#7008).

And this message pops up with any file I attempt to update from the iSeries.

I would be ever so grateful if some could point me in the right direction to
figure this out. I am not sure if it is a configuration setting on the
iSeries, or if it is just not possible to do updates using Access on iSeries,
or if it is something else I am overlooking.

Thanks

shebcogal wrote:
I am very new to Access. I have ODBC connections from iSeries
databases into my Access 2007 database, and SQL Server connections to
SQL databases. The updates to my SQL databases work fine. My ODBC
connection to my ACCESS 2007 database works fine for viewing the
data. However, when I try to make any updates to the iSeries files by
using Access, I just keep getting the same error message "ODBC -
insert on a linked table table failed. (Error 3155)."

Here is the actual message that displays in the dialog box:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - GLACTREF1 in
FMISLIB not valid operation (#7008).

And this message pops up with any file I attempt to update from the
iSeries.

I would be ever so grateful if some could point me in the right
direction to figure this out. I am not sure if it is a configuration
setting on the iSeries, or if it is just not possible to do updates
using Access on iSeries, or if it is something else I am overlooking.

Thanks

Unlike SQL Server where all edits are always logged, the AS400 lets you
decide if a table is journaled or not and the default is that they are not.
If you connect with an ODBC source, that source must then use an isolation
level of "Commit Immediate (None)". Any other setting with a non-journaled
table will produce the error you are seeing.

So, you either have to change your ODBC source or you have to turn on
Journaling for the table you are linking to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks alot Rick. You've most likely saved me a lot of time and headache.

"shebcogal" wrote:

I am very new to Access. I have ODBC connections from iSeries databases into
my Access 2007 database, and SQL Server connections to SQL databases. The
updates to my SQL databases work fine. My ODBC connection to my ACCESS 2007
database works fine for viewing the data. However, when I try to make any
updates to the iSeries files by using Access, I just keep getting the same
error message "ODBC - insert on a linked table table failed. (Error 3155)."

Here is the actual message that displays in the dialog box:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - GLACTREF1 in FMISLIB not
valid operation (#7008).

And this message pops up with any file I attempt to update from the iSeries.

I would be ever so grateful if some could point me in the right direction to
figure this out. I am not sure if it is a configuration setting on the
iSeries, or if it is just not possible to do updates using Access on iSeries,
or if it is something else I am overlooking.

Thanks

I am still unsure of what to do. I've looked at my ODBC connection, there
appears to be nothing in those config settings that would allow me to commit
the table. So do I need to do something to the table/file on the AS400? Or do
I need to write a vb script in my access database? Or can I add this line of
code "Commit Immediate (None)" to the SQL script prior to my Insert?
Thanks


"Rick Brandt" wrote:

shebcogal wrote:
I am very new to Access. I have ODBC connections from iSeries
databases into my Access 2007 database, and SQL Server connections to
SQL databases. The updates to my SQL databases work fine. My ODBC
connection to my ACCESS 2007 database works fine for viewing the
data. However, when I try to make any updates to the iSeries files by
using Access, I just keep getting the same error message "ODBC -
insert on a linked table table failed. (Error 3155)."

Here is the actual message that displays in the dialog box:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - GLACTREF1 in
FMISLIB not valid operation (#7008).

And this message pops up with any file I attempt to update from the
iSeries.

I would be ever so grateful if some could point me in the right
direction to figure this out. I am not sure if it is a configuration
setting on the iSeries, or if it is just not possible to do updates
using Access on iSeries, or if it is something else I am overlooking.

Thanks

Unlike SQL Server where all edits are always logged, the AS400 lets you
decide if a table is journaled or not and the default is that they are not.
If you connect with an ODBC source, that source must then use an isolation
level of "Commit Immediate (None)". Any other setting with a non-journaled
table will produce the error you are seeing.

So, you either have to change your ODBC source or you have to turn on
Journaling for the table you are linking to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

shebcogal wrote:
I am still unsure of what to do. I've looked at my ODBC connection,
there appears to be nothing in those config settings that would allow
me to commit the table. So do I need to do something to the
table/file on the AS400? Or do I need to write a vb script in my
access database? Or can I add this line of code "Commit Immediate
(None)" to the SQL script prior to my Insert?
Thanks

Assuming you are looking at your DSN in the ODBC Manager applet within
Control Panel...

Select your DSN and press the [ Configure ] button.

On the "Server" tab press the [ Advanced ] button.

For the "Commit Mode" there will be a few choices. You want "Commit
immediate (*NONE)".

If you already have that selected or still see the problem after selecting
it close the configuration form for your DSN and switch to the "Drivers" tab
of the ODBC Manager applet. That will show you the version numbers for all
of your ODBC drivers. The IBM driver is actually listed twice under two
different names (one name is for backward compatibilty).

Client Access ODBC Driver (32 bit)
ISeries Access ODBC Driver

You should see the same version number next to both of these names. If the
version is...

10.00.00.00

....that is a version with a bug that causes the error you are seeing. You
need to update to a later driver. The version I am currently running is
11.00.05.00. I'm not sure if that is the very latest. but it's fairly new
at least.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I didn't even see that Commit Mode option on the Server Advanced tab, and
I've been looking thru all those tabs for hours trying different things! So
I've made the Commit Mode change that you've suggested to my file dsn, then
I've deleted the previous connection in my access database and re-linked my
file, and now it works. You are a genius! Thank you so much...

"Rick Brandt" wrote:

shebcogal wrote:
I am still unsure of what to do. I've looked at my ODBC connection,
there appears to be nothing in those config settings that would allow
me to commit the table. So do I need to do something to the
table/file on the AS400? Or do I need to write a vb script in my
access database? Or can I add this line of code "Commit Immediate
(None)" to the SQL script prior to my Insert?
Thanks

Assuming you are looking at your DSN in the ODBC Manager applet within
Control Panel...

Select your DSN and press the [ Configure ] button.

On the "Server" tab press the [ Advanced ] button.

For the "Commit Mode" there will be a few choices. You want "Commit
immediate (*NONE)".

If you already have that selected or still see the problem after selecting
it close the configuration form for your DSN and switch to the "Drivers" tab
of the ODBC Manager applet. That will show you the version numbers for all
of your ODBC drivers. The IBM driver is actually listed twice under two
different names (one name is for backward compatibilty).

Client Access ODBC Driver (32 bit)
ISeries Access ODBC Driver

You should see the same version number next to both of these names. If the
version is...

10.00.00.00

....that is a version with a bug that causes the error you are seeing. You
need to update to a later driver. The version I am currently running is
11.00.05.00. I'm not sure if that is the very latest. but it's fairly new
at least.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi,

Did you ever get an answer to this question?

If so - please help!

Brian


800-828-6595*111



shebcoga wrote:

ODBC export to iSeries
27-Aug-08

I am very new to Access. I have ODBC connections from iSeries databases into
my Access 2007 database, and SQL Server connections to SQL databases. The
updates to my SQL databases work fine. My ODBC connection to my ACCESS 2007
database works fine for viewing the data. However, when I try to make any
updates to the iSeries files by using Access, I just keep getting the same
error message "ODBC - insert on a linked table table failed. (Error 3155)."

Here is the actual message that displays in the dialog box:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - GLACTREF1 in FMISLIB not
valid operation (#7008).

And this message pops up with any file I attempt to update from the iSeries.

I would be ever so grateful if some could point me in the right direction to
figure this out. I am not sure if it is a configuration setting on the
iSeries, or if it is just not possible to do updates using Access on iSeries,
or if it is something else I am overlooking.

Thanks

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials...tom-pagin.aspx