Pass Data from MS Access to AS400 (1 Viewer)

Alhakeem1977 · Jun 2, 2019

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

GregoryWest · Jun 2, 2019

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.

theDBguy · Jun 2, 2019

Would copy and paste work?

The_Doc_Man · Jun 2, 2019

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

Alhakeem1977 · Jun 3, 2019

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

theDBguy · Jun 3, 2019

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.

Alhakeem1977 · Jun 3, 2019

theDBguy said:
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

theDBguy · Jun 3, 2019

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!

Alhakeem1977 · Jun 3, 2019

theDBguy said:
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!

,,[emoji4] 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

Gasman · Jun 4, 2019

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

Alhakeem1977 said:
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

apr pillai · Jun 8, 2019

Gasman said:
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

Gasman · Jun 8, 2019

apr pillai said:
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.?

The_Doc_Man · Jun 9, 2019

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?

apr pillai · Jun 9, 2019

The_Doc_Man said:
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

The_Doc_Man · Jun 9, 2019

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.

JHB · Jun 9, 2019

Can't you use a comma separated file instead?
Why should it be from MS-Access to AS400, why not from AS400 to MS-Access?

NauticalGent · Jun 9, 2019

The_Doc_Man said:
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.

Emphatically agree...force them to give you the tools you need to meet THEIR demands.

The_Doc_Man · Jun 9, 2019

JHB, I agree that some intermediary file needs to be the answer, but your response has the OP's desired data flow going the wrong way. For better or worse, the problem is to pass data into a non-Windows environment.

Alhakeem1977 · Jun 13, 2019

NauticalGent said:
Emphatically agree...force them to give you the tools you need to meet THEIR demands.

Thanks everyone for your responses, I will point that to my boss that the best solution is to connect throw the ODBC connectivity.

Now it's clear to me that the solution is to amend our organization's policy to accept connect the MS Access database with AS400 db through ODBC.

Thanks again!

Regards,
Alhakeem1977

Sent from my HUAWEI NXT-L29 using Tapatalk

The_Doc_Man · Jun 13, 2019

You are welcome and I wish you luck with the problem of convincing the "powers that be". Even if you have to go through an intermediate table of some sort and let the AS400 folks finish the process, in the long run you will be better off.

Pass Data from MS Access to AS400 (1 Viewer)

Registered User.

Registered User.

I’m here to help

Immoderate Moderator

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Enthusiastic Amateur

AWF VIP

Enthusiastic Amateur

Immoderate Moderator

AWF VIP

Immoderate Moderator

Have been here a while

Ignore List Poster Boy

Immoderate Moderator

Registered User.

Immoderate Moderator

Similar threads

Users who are viewing this thread