ODBC export to iSeries

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