× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. March 2022

Re: IBM i connection with MS access

For the file which is not journaled and even journaled, I tried all the
below Commit mode options in Advance server options still could not
generate this error :-

Commit mode:

Commit immediate(*None)
Read uncommited (*CHG)
Read commited(*CS)
Repeatable read (*ALL)
Serializable (*RR)

***************************
also tried by changing various Connection types available under IBM i
Access Windows ODBC Driver Connect:

1) Use ODBC access mode
2) Use ODBC access mode, Call allowed
3) Limit to Read/Call(Select and Call statements allowed)
4) Limit to Read-Only (Select statements only)

But could not generate that error(SQL7008).




Thanks.

On Fri, 25 Mar 2022 at 22:03, jerry ven <jerryven95@xxxxxxxxx> wrote:


Hi,


I tried commit mode (*CS) then opened the file in MS Access, which was
not journaled and then entered some value in this file's field and then
clicked on save button on MS Access but could not generate this error.

So How could I generate this error any example with proper steps.

and then in the same example by changing commit mode settings or using any
other settings how to resolve below error as mentioned earlier:-

"ODBC- update on a linked table Libraryname_ Filename' failed.
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008- Filename in
libraryname not valid operation.(#-7008) "


Thanks.

On Fri, 25 Mar 2022 at 19:30, Rob Berendt <rob@xxxxxxxxx> wrote:

<snip>
Commit Mode :- Read uncommitted(*CHG) -- > I think I was advised for the
same option (Commit immediate (*NONE) among different available options
under the drop down button here ?
</snip>
"Read uncommitted" and that *NONE I would think would both turn off the
requirements for journaling.

Now, let me say that I think journaling is a good thing. We journal all
of our files. It took me years of fighting and it wasn't until we got a
H/A system which required it that it happened. If you do use journaling I
strongly recommend getting
Resource
ID Option Feature Description
5770SS1 42 5117 HA Journal Performance
It will cost you a little but it's much cheaper, and much more effective,
than throwing a truckload of money into alternative hardware.
I suppose you could customize all your vendor software to better handle
commitment control (as was once suggested to me by someone on the list) but
that's very costly, hampers upgrades, etc. Probably only acceptable to
those shops which figure manpower is unlimited but paying 1 cent to a
vendor is prohibited. Or maybe a consultant milking business.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
jerry ven
Sent: Friday, March 25, 2022 9:32 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Hi,

Please find the settings from 'MS access to IBM i' as followings and I am
not sure how and from where to check settings for 'and/or SQL server to
your IBM i' :-

MS Access to IBM i :-


1) MS Access blank database -->New Data Source --> From Other
Sources-->ODBC Database--->Import the source data into a new table in the
current database.

2) Import the source data into a new table in the current database -->
Click on 'OK' --> DSN Name --> New --> iSeries Access ODBC Driver
(32-bit)(
Under Advanced File DSN Creation Settings by default it's written (under
Type driver - specific keywords and values: DRIVER = {Client Access ODBC
Driver (32 -bit)} and verify this connection(recommended) checkbox is tick
marked.

3) Then type the name of the file data source Which you want to save this
connection to. Or, find the location to save to by clicking Browse.---> I
typed some name here.


4) Then I Clicked on the Finish button.


5) Then On 'IBM i Access for Windows ODBC Driver Connect' Window below
are the settings for 'General' and 'Server' tabs:-


For General :-

Data Source name -- I think i can use the same Data source name which i
gave on step number 3) as mentioned above ?


Description:- it's coming automatoically as 'IBM i Access for Windows ODBC
data source'.

system:-- name of iseries server on which i want to make this connection.

Connection options:- I have let the default 'Use IBM i Navigator default'.

security :- 'Use same security as IBM i Navigator connection'.




For Server :-


Naming convention:

by default it's coming as 'SQL naming convention (*SQL)

Default SQL Schema or Library:- I have mentioned the library name in
which i am doing this testing for updating records to a specific file
which
is journaled and not journaled.

Connection Type:- Use ODBC access mode


undee Advanced server options Window below are the default settings:-

Commit Mode :- Read uncommitted(*CHG) -- > I think I was advised for the
same option (Commit immediate (*NONE) among different available options
under the drop down button here ?


Time :

format :

hh:mm:ss (*HMS)

Separator:

:(Colon)

Date :

Format:
yyyy-mm-dd (*ISO)

Separator
. (dash)

----
----





Thanks.









On Fri, 25 Mar 2022 at 18:11, Rob Berendt <rob@xxxxxxxxx> wrote:

I would check your setup in your connection from MS Access and/or SQL
server to your IBM i.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
jerry ven
Sent: Friday, March 25, 2022 8:15 AM
To: Midrange Systems Technical Discussion <
midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


1.) where can i check this connection options like
SET OPTION COMMIT = NONE ?

2.) also does this error appear when 1) trying to update any file
using
MS access which is not journaled or 2) does it appear when someone
runs
SQL query to update the same file which is not journaled using MS
access
?


Thanks.

On Fri, 25 Mar 2022 at 16:48, Rob Berendt <rob@xxxxxxxxx> wrote:

But he was concerned because the error was NOT appearing.

There are options in SQL, like WITH NC, which turns off commitment
control
and thus cares not a whit about journaling.
There also connection options like
SET OPTION COMMIT = NONE
which will turn off commitment control too. I believe this
capability is
also in your connection setup.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jack Woehr via MIDRANGE-L
Sent: Thursday, March 24, 2022 3:53 PM
To: Midrange Systems Technical Discussion <
midrange-l@xxxxxxxxxxxxxxxxxx

Cc: Jack Woehr <jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx>
Subject: Re: IBM i connection with MS access

CAUTION: This email originated from outside of the organization. Do
not
click links or open attachments unless you recognize the sender and
know
the content is safe.


"This error comes from the database itself : the SQL7008 message is
usually
a journaling issue indicating that either the updated file or
database is
not journaled, or that the current environment does not have
authority to
its journal."




https://www.ibm.com/support/pages/error-javasqlsqlexception-sql7008-during-rule-execution-server-diagnostic

Looks like you're trying to do something in SQL you can't do on a file
that's not jourmalled.

On Thu, Mar 24, 2022 at 11:22 AM jerry ven <jerryven95@xxxxxxxxx>
wrote:

I tried updating a file using MS access and the file was not
journaled
but
when i clicked on save button in MS access after making any data
entry
in
any of the field which was opened in MS Access for a particular
file
then
this error ' "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008"
was
not
appearing.



--
Jack Woehr, IBM Champion 2021-2022
<


https://www.youracclaim.com/badges/528d23d6-087f-4698-8d17-d59688106ac4/public_url

Absolute Performance, Inc.
12303 Airport Way, Suite 100
Broomfield, CO 80021

NON-DISCLOSURE NOTICE: This communication including any and all
attachments is for the intended recipient(s) only and may contain
confidential and privileged information. If you are not the intended
recipient of this communication, any disclosure, copying further
distribution or use of this communication is prohibited. If you
received
this communication in error, please contact the sender and
delete/destroy
all copies of this communication immediately.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.