× 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. February 2022

RE: IBM i connection with MS access

You can create a linked table reference in MSAccess to a table(s) on the IBM I then build standard MSAccess queries over that tables.
MSAccess queries have an sql mode where you can write the query as an SQL statement, or use the MSAccess 'Design Mode' .

Do you use Visual Basic In msaccess? In visual basic you can build SQL strings and run them.

I have an example of a filtered form where I create the filter using an sql string. (Note: this is an ODBC connection to MySQL not the IBM I, but the source SQL database should not make a difference.)

Private Sub filtertheform()
Dim searchit As String
Dim thedatasource As String
Dim qdef As QueryDef
Dim sqlstmt As String
thedatasource = Me.RecordSource

Set qdef = CurrentDb.QueryDefs(thedatasource)

sqlstmt = "SELECT distinct tblDailyTasks.*, " + _
"case when tblDailyTasksNotes.Notes is null then false else true END as notesflag, " + _
"tblDailyTasks.taskid, " + _
"tblClientMaster.ClientName , tblProjectMaster.ProjectName, " + _
"tblProjectMaster.billingcategory " + _
"FROM tblDailyTasks INNER JOIN tblProjectMaster ON tblDailyTasks.ProjectId " + _
"= tblProjectMaster.ProjectId INNER JOIN tblClientMaster ON " + _
"tblClientMaster.ClientId = tblProjectMaster.ClientID AND " + _
"tblDailyTasks.ClientId = tblClientMaster.ClientId LEFT JOIN " + _
"tblDailyTasksNotes ON tblDailyTasks.DailyTaskId = tblDailyTasksNotes.DailyTaskId " + _
"left JOIN tblBillingCategory ON tblprojectMaster.ClientId = tblBillingCategory.ClientID " + _
"AND tblProjectMaster.Billingcategory = tblBillingCategory.BillingCategory " + _
"WHERE match (tblDailyTasksNotes.Notes, tblDailyTasks.TaskDescription, " + _
"tblBillingCategory.BCName, tblBillingCategory.BCDescription, " + _
"tblClientMaster.ClientName,tblClientMaster.ClientDesc, " + _
"tblProjectMaster.ProjectName, tblProjectMaster.ProjectDesc) " + _
"against (""" & Me.txtsearchfor & """ in boolean mode) " + _
"order by tbldailytasks.taskdate desc"
qdef.sql = sqlstmt
qdef.Close

Set qdef = Nothing
Me.Requery
Me.txtsearchfor.SetFocus

End Sub

Good luck.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of jerry ven
Sent: Saturday, February 26, 2022 3:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: IBM i connection with MS access

After carefully following steps and retrying I was able to see the respective file's data here using MS access, can I run SQL queries like we execute it in IBM i using STRSQL here in MS access when it's connected with IBM i? If yes, then How ?


Thanks

On Sun, 27 Feb 2022 at 01:19, jerry ven <jerryven95@xxxxxxxxx> wrote:

Yes, i installed ODBC driver and then trying again with my link and
trying step by step again at step no.4 unable to see that screen which says "
Once you have named your new database, you are shown the standard
Microsoft Access work environment."

But I can't see any such work environment.


Thanks


On Sun, 27 Feb 2022 at 00:26, Jim Oberholtzer
<midrangel@xxxxxxxxxxxxxxxxx>
wrote:

Did you install the ODBC driver and then configure the connection?

Those might be required steps. ;-)



Jim Oberholtzer
Agile Technology Architects



On Feb 26, 2022, at 12:35 PM, jerry ven <jerryven95@xxxxxxxxx> wrote:

?But after I just noticed it (
IBMiAccess_v1r1_WindowsAP_English.zip )
was
part of my original IBM i ACS tool, my question was to connect IBM
i through MS access following the link which I had shared in my
previous mail. so in the readmespacs.txt file which was there in
documentation folder when i had downloaded this IBM i ACS tool and again when I
downloaded this file IBMiAccess_v1r1_WindowsAP_English.zip in it's
documentation folder the same file( readmespacs.txt ) was there
but i could not find steps to connect to IBM i through this
documentation like the steps were mentioned in my previous post.





Thanks


On Sat, 26 Feb 2022 at 21:27, Jack Woehr via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Download IBMiAccess_v1r1_WindowsAP_English.zip (the modern ODBC
driver) from the same URL you downloaded IBM i Access Client
Solutions and
read the
docs.

On Sat, Feb 26, 2022 at 8:24 AM jerry ven <jerryven95@xxxxxxxxx>
wrote:

Hi ,

When trying to connect pub400.com with MS access while referring
to
the
IBM
document below, I am unable to connect to PUB400.com because I
can not proceed further beyond step 4 as I am unable to see the
screen as
mentioned
in this link on step number 4.

link:-*


https://www.ibm.com/support/pages/microsoft-access-connecting-odbc-da
ta-source
<


https://www.ibm.com/support/pages/microsoft-access-connecting-odbc-da
ta-source
*


I am trying with MS access 32 bit.




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



--
Jack Woehr, IBM Champion 2021-2022 <

https://www.youracclaim.com/badges/528d23d6-087f-4698-8d17-d59688106a
c4/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


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