Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-02-2019, 07:03 AM   #1
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 155
Thanks: 72
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Pass Data from MS Access to AS400

Hi everyone,

How can I pass data from Microsoft Access active form to another active program like AS400, by preesing a button in Access and it will pass the whole fields like Transaction reference, Transaction Amount, and Customer A/C.

The connection in our environment cannot be through the connection manager like ODBC or OLEDB tio connect the databases.

I cannot recalled wither it's probably by HTML or something else?

Thanks a lot in advance!

Sent from my HUAWEI NXT-L29 using Tapatalk

Alhakeem1977 is offline   Reply With Quote
Old 06-02-2019, 07:45 AM   #2
GregoryWest
Newly Registered User
 
Join Date: Apr 2014
Location: Winnipeg Manitoba Canada
Posts: 149
Thanks: 11
Thanked 0 Times in 0 Posts
GregoryWest is on a distinguished road
Re: Pass Data from MS Access to AS400

A two step process I have used in these types of situation is a simple Export / Import. There are ways of pooching this method, but less damaging than someone typing in data wrong.
GregoryWest is offline   Reply With Quote
Old 06-02-2019, 09:05 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Pass Data from MS Access to AS400

Would copy and paste work?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-02-2019, 09:27 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Pass Data from MS Access to AS400

@theDBguy - copy and paste between two WINDOWS systems would work because they would use file sharing protocols behind the scenes (if you did that from Windows Explorer). However, the online research I did on AS400 says it is a bit of hardware, probably running OS/400, as part of the IBM iSeries line of mid-range servers. Different O/S capabilities make me think that unless some 3rd party packages are in use, copy/paste between systems won't work so well.

I think GregoryWest is probably right. Alhakeem1977 says he cannot use ODBC or OLEDB so that means another compatibility issue. Therefore, all of the mechanically simple ways are going to fail miserably. This will be a "build message, separately send message" type of operation.

Alhakeem1977, we are going to need a bit more from you. Is there any software on the AS400 that can read files from MS Office utilities like Excel? Because the problem is that we have to find the common format between the machines first, and I'll be honest - I was never an IBM person. (All of my mini, midi, and server experience in college was Digital Equipment Corp machines.)

I'm sure we can come up with something but we need to know a bit more about the target. Ask the AS400 people what THEY want to see and maybe we can do that. However, this WILL have to be in the form of flat files, I think, because none of the dynamic file interchange methods are likely to work.

I'm also curious. If our solution was to build a file in the right format and then send it to the AS400 using FTP, is that worth pursuing? I am having trouble imagining an IBM server that doesn't run the normal suite of TCP/IP protocols, but that is important to check before we go too far.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-02-2019, 03:05 PM   #5
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 155
Thanks: 72
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Pass Data from MS Access to AS400

Thanks a lot for the all responses,

Sorry guys I was busy with my family.

Just I want to explain the situation, the AS400 can accept the copy - past to fill up the data entry form from any other active window like HTML if I am correct, that it's possible to get data from MS Access form fields and past it to the AS400 data entry form fields.

But the issue is that our organization cannot approve any integration from any other database with the core system (AS400), it's a clue in our policy as we are one of the branches in the world.

My aim is to automate and simplify the data entry that I have it in MS Access instead of dubble entry with the same data fields.
Means copy from Access field to AS400 fields.

Is there a simple way to handle this issue?

Sent from my HUAWEI NXT-L29 using Tapatalk
Alhakeem1977 is offline   Reply With Quote
Old 06-02-2019, 03:46 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Pass Data from MS Access to AS400

Hi. So, if youíre saying copy and paste work, then hereís a crude approach I have seen before when making Access work with an external application that donít accept OLE automation. You can use API calls to switch between windows back and forth and use Sendkeys to copy and paste the data between the two programs.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-02-2019, 04:20 PM   #7
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 155
Thanks: 72
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Pass Data from MS Access to AS400

Quote:
Originally Posted by theDBguy View Post
Hi. So, if youíre saying copy and paste work, then hereís a crude approach I have seen before when making Access work with an external application that donít accept OLE automation. You can use API calls to switch between windows back and forth and use Sendkeys to copy and paste the data between the two programs.
Thanks for your prompt response.

But I do not have any idea how to implement this approach !?

Let's assume that
if I have got four fields in Access like:
1. TransRef
2. TransDate
3. TransAmount
4. BenefitaryDetails

How can insert them into my data entry form in AS400 with the same fields?


Thank you so much!

Sent from my HUAWEI NXT-L29 using Tapatalk

Alhakeem1977 is offline   Reply With Quote
Old 06-03-2019, 07:46 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Pass Data from MS Access to AS400

Hi. I wish I could create a demo for you, but I am not sure I can. If you search for Windows APIs, you should be able to find the pair that identifies the currently available windows and set a specific one as active. You should be able to switch back and forth between windows using these APIs. You can then either use Sendkeys or other APIs to copy and paste data as you move from one window to another. Hopefully, someone else would have a better idea for you. What I am recommending is very crude and nothing more than automating a manual process for you. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Alhakeem1977 (06-03-2019)
Old 06-03-2019, 12:54 PM   #9
Alhakeem1977
Newly Registered User
 
Join Date: Jun 2017
Posts: 155
Thanks: 72
Thanked 0 Times in 0 Posts
Alhakeem1977 is on a distinguished road
Re: Pass Data from MS Access to AS400

Quote:
Originally Posted by theDBguy View Post
Hi. I wish I could create a demo for you, but I am not sure I can. If you search for Windows APIs, you should be able to find the pair that identifies the currently available windows and set a specific one as active. You should be able to switch back and forth between windows using these APIs. You can then either use Sendkeys or other APIs to copy and paste data as you move from one window to another. Hopefully, someone else would have a better idea for you. What I am recommending is very crude and nothing more than automating a manual process for you. Good luck!
,, Thank you so much, if you could get chance to do it fine and I will search through the sites but unfortunately for me it will be a bit difficult to get it, if I get it work I will let you know.

Thanks again, I am waiting any other suggestions.

Sent from my HUAWEI NXT-L29 using Tapatalk
Alhakeem1977 is offline   Reply With Quote
Old 06-03-2019, 09:23 PM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Pass Data from MS Access to AS400

If I had to do that I would try it like this.

Select the control
Copy to clipboard
Select the window that has the AS400 data in it
Go to the first control with the Sendkeys function using Home key
Then Tab as many times as required again using SendKeys to get to the correct matching field.
Then paste from clipboard
Select the Access window

continue as above until all fields have been processed.

HTH
Quote:
Originally Posted by Alhakeem1977 View Post
Thanks for your prompt response.

But I do not have any idea how to implement this approach !?

Let's assume that
if I have got four fields in Access like:
1. TransRef
2. TransDate
3. TransAmount
4. BenefitaryDetails

How can insert them into my data entry form in AS400 with the same fields?


Thank you so much!

Sent from my HUAWEI NXT-L29 using Tapatalk
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 06-03-2019 at 09:34 PM.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Alhakeem1977 (06-13-2019)
Old 06-08-2019, 09:50 AM   #11
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 674
Thanks: 1
Thanked 107 Times in 95 Posts
apr pillai is on a distinguished road
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Pass Data from MS Access to AS400

Quote:
Originally Posted by Gasman View Post
If I had to do that I would try it like this.



Select the control

Copy to clipboard

Select the window that has the AS400 data in it

Go to the first control with the Sendkeys function using Home key

Then Tab as many times as required again using SendKeys to get to the correct matching field.

Then paste from clipboard

Select the Access window



continue as above until all fields have been processed.



HTH


If you have access rights to AS400 table then you can link it to Access through ODBC and work with the table.



Sent from my iPhone using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
The Following User Says Thank You to apr pillai For This Useful Post:
Alhakeem1977 (06-13-2019)
Old 06-08-2019, 11:18 AM   #12
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Pass Data from MS Access to AS400

Quote:
Originally Posted by apr pillai View Post
If you have access rights to AS400 table then you can link it to Access through ODBC and work with the table.



Sent from my iPhone using Tapatalk
The o/p stated that was not allowed/available for some reason in the initial post, probably due to the access rights you mention.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Alhakeem1977 (06-13-2019)
Old 06-08-2019, 03:00 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Pass Data from MS Access to AS400

My usual advice in this case might be difficult to follow, but here goes...

The problem is to find a way to get data from point A to point B when the normal, direct methods are disallowed. The simplest solution is ODBC or some other networked method but that is claimed to be a non-starter.

My response is that the person asking for the data to be integrated can ask for it to be brought it without using certain methods. But if the goal is reliability then that is not going to fly. The correct response is to tell the boss that there is no reliable method other than the one that everyone else in the world uses. If he wants the data, he has the choice of unreliable, tedious, and time-wasting methods, or he can decide to override the corporate objections and allow some sort of limited direct import method. I.e. right now it is Alhakeem's problem. Make it his boss's problem and see if HE can break the logjam.

Having said that, I have a question for Alhakeem.

Can you ask for a small side database to be set up that would allow you access via ODBC ONLY to the small side database? Then after your import, your AS400 crew could open that DB for importing the new information. But other than at the time that this final import is under way, there would be no contact between it and your main database. Is that a viable solution?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Alhakeem1977 (06-13-2019)
Old 06-08-2019, 06:16 PM   #14
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 674
Thanks: 1
Thanked 107 Times in 95 Posts
apr pillai is on a distinguished road
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Pass Data from MS Access to AS400

Quote:
Originally Posted by The_Doc_Man View Post
My usual advice in this case might be difficult to follow, but here goes...

The problem is to find a way to get data from point A to point B when the normal, direct methods are disallowed. The simplest solution is ODBC or some other networked method but that is claimed to be a non-starter.

My response is that the person asking for the data to be integrated can ask for it to be brought it without using certain methods. But if the goal is reliability then that is not going to fly. The correct response is to tell the boss that there is no reliable method other than the one that everyone else in the world uses. If he wants the data, he has the choice of unreliable, tedious, and time-wasting methods, or he can decide to override the corporate objections and allow some sort of limited direct import method. I.e. right now it is Alhakeem's problem. Make it his boss's problem and see if HE can break the logjam.

Having said that, I have a question for Alhakeem.

Can you ask for a small side database to be set up that would allow you access via ODBC ONLY to the small side database? Then after your import, your AS400 crew could open that DB for importing the new information. But other than at the time that this final import is under way, there would be no contact between it and your main database. Is that a viable solution?


AS400 Tables can be dowloaded if allowed to do so, in .dbf format for local customized analysis.

We used to ask for the Monthly Report Printout in Spool file form from AS400 inventory system (hundreds of pages) parse the data lines/fields, discarding header/footer/summary lines with a combination of VBA and Queries processes and upload into Access for local customized Reports for Management.

As far as I can see the user doesnít have authority to modify the data in AS400 and attempting to modify data through other means from outside is not advisable.



Sent from my iPhone using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
The Following User Says Thank You to apr pillai For This Useful Post:
Alhakeem1977 (06-13-2019)
Old 06-08-2019, 06:49 PM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Pass Data from MS Access to AS400

I merely point out that the OP's manager has painted this process into a corner. The correct solution is to make it the manager's problem to solve the permissions issue and let Alhakeem solve the technical issues. This strategy (making it partly the boss's problem) even worked with the U.S. Navy in an environment involving SECRET clearance levels.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Alhakeem1977 (06-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create DSN for AS400(iSeries) in Access on the fly VegaLA Modules & VBA 2 01-26-2011 09:50 AM
Macro to transfer data to AS400 ALANBELL1 Excel 0 10-02-2007 07:34 AM
ODBC Access AS400 links Carolyn_dick General 0 03-03-2003 04:18 PM
AS400 Connection Causes Access to Crash Daslimmer General 1 06-27-2002 12:35 PM
[SOLVED] Access for AS400 files... poolban Reports 3 12-27-2001 07:26 AM




All times are GMT -8. The time now is 10:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World