using iSeries file member with Access

[dino624]

My problem is that I would like to process a file member from the iSeries to generate an Access report for the specific user that requests it.
Each user will create a new file member on the iSeries, because more than one person could possibly run the Access report at the same time, and having a linked file would not work.


Is there any way to do this?
I do not think link file would work and I have tried the transfertext but could not see how I can specify a member.
If I have to code it to work that would be fine. If that is the case, a code example would be helpful.

[June7]

What is iSeries?

Why would linking not work?

[dino624]

iSeries is the IBM AS/400.
Linking will not work because I need to link the member of the file for each individual user the might request the report. Two users might request the report at the same time. Linking the file would cause possible overwriting of data.

[June7]

I don't understand how running a report causes overwrite of data.

I didn't think Access could link to AS/400 file anyway.

[dino624]

I can link the AS/400 file to Access but if 2 users want to run the Access report at the same time, the data from the second user would be used.
If I can somehow use the members from the AS/400 file, each user would receive their specific report. I query the information on the AS/400 based on user criteria. If only one user was to use the report at a time it would not be an issue. but I have to take into account 2 or more might run the report at the same time.
I hope that makes sense.

[June7]

This is not a split db? Users are all running the same file? I have never tested multiple users running the same dynamically filtered report at same time in an all-in-one db.

If it isn't already, should be a split db. If it is a split db and each user is running their own copy of frontend, then this is really confusing because should be no issue with multiple users in split database running report.

[dino624]

I realize this is might be hard to understand. This is not a split DB. I am only using Access for the report function.
All other processes are going to be done on the AS/400. On the AS/400, the user will extract information based on their selection criteria and place it into a separate file member.
I will then run a vbscript that will run the report. The script below does work if I link the AS400 file to the Access data base.
But because the data in the linked file is dynamic this would only work if only one user ran the script at a time.
I have an ODBC connection to the AS400 which allows me to link the file. but it only allows me to link the first member of the file and not a specific member.

this is the script:
Option Explicit
Dim appAccess
Dim WshNetwork
Dim strName
dim strComputer
Dim oWMI
Dim colPrinters
Dim oPrinter
Dim acImportFixed
Const cstrFolder = "\\eureka\group\prospex\"
Const cstrMdb = "prospex.accdb"
'Discontinued list
Const cstrReport = "rptglass_new"
'Determine default printer
Const ATTR_DEFAULT = 4
strComputer = "."
Set oWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colPrinters = oWMI.ExecQuery("SELECT * FROM Win32_Printer where Default= TRUE")
For Each oPrinter in colPrinters
strname = oPrinter.ShareName
Next
'Set new default printer
Set WshNetwork = WScript.CreateObject("WScript.Network")
WshNetwork.SetDefaultPrinter "CutePDF Writer" ' Set default printer
'Print report
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase cstrFolder & cstrMdb, False
'Set iSeries Member name for User
'Not working comment out : appAccess.DoCmd.TransferText acImportFixed,,"qspexrpt","\\eureka\group\prospex\donl.txt",True
appAccess.DoCmd.OpenReport cstrReport,0,,,3 'report mode=5, dialog=3
appAccess.DoCmd.Close 3, cstrReport 'acReport = 3
appAccess.Quit
Set appAccess = Nothing
'Reset Default printer
on error resume next
WshNetwork.SetDefaultPrinter strname
WshNetwork.SetDefaultPrinter "\\Eureka\" & strname

[June7]

This is confusing.

Where is this script? It is a .vbs executable file and not a module within the Access file? Why is the code not within Access? Then each user could have their own copy of the Access file and therefore run independent instances of the code and the Access report.

I don't understand 'separate file member', 'first file member', 'specific member'? What is a 'member'?

[orange]

Have you tried using Google/Bing to find info on Access and Iseries interaction?
Have you tried to find any ISeries user groups/forums that may interact with Access?

If you are using dynamic data for reporting, you always run the risk that 2 users would never get the same results -since the data could change at any instant.

Where did the script come from? As June7 says it could be put into a module in vba and made available to each user.
But if you are reporting from an Online database, there is NO GUARANTEE that 2 reports will be the same.

I worked in corporate data base where we had several online systems --Finance - Assistance Programs - Loans. New users would try to run reports against dynamic/real time data and be upset when reports didn't match. This was solved by creating MonthEnd Financial/Programs/Loans database -- values all current up to the close of business last day of the month. Reporting was consistent.

Dynamic reports were available and timestamped - they were accurate at that point in time - but could change in the next instant of time.

Good luck.

[dino624]

Here is what I am trying to accomplish.
The user enters their selection criteria into the AS/400 screen and it in turns creates a query file(filename) with a member especially for that user(usermember)
I have the query file(filename) linked to an Access db.
After the file is built I run a vbscript. See at end of reply.
In that script I set run access pointing to a start function which will set up an override to the link filename with the individual member for that user.
I need to do this because there might be multiple users attempting to process the report at the same time.
From within the vbscript I receive a message that: Microsoft Access cannot find the procedure 'Report_rptglass_new.me_start.'
I can run this procedure from within Access vba.
I know this might sound a bit convoluted but it should work.
Any help would be appreciated.
vbscript:
Version:0.9
StartHTML:00000107
EndHTML:00007420
EndFragment:00007380
EndFragment:00000000


Option Explicit
dim args
dim Userid
Set args = WScript.Arguments
Userid = "#DONL"  'args.Item(0)
dim func1
func1 = "Report_rptglass_new.me_start"

Dim appAccess
Dim WshNetwork
Dim strName
dim strComputer
Dim oWMI
Dim colPrinters
Dim oPrinter
Dim acImportFixed
Const cstrFolder = "\\eureka\group\prospex\"
Const cstrMdb = "prospextest.accdb"
'Discontinued list
Const cstrReport = "rptglass_new"

'Determine default printer
Const ATTR_DEFAULT = 4
strComputer = "."
Set oWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colPrinters = oWMI.ExecQuery("SELECT * FROM Win32_Printer where Default= TR UE")
For Each oPrinter in colPrinters
   strname = oPrinter.ShareName
Next

'Set new default printer
Set WshNetwork = WScript.CreateObject("WScript.Network")
WshNetwork.SetDefaultPrinter "CutePDF Writer" ' Set default printer

'Print report
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase cstrFolder & cstrMdb, False
appAccess.run func1,userid
'appAccess.DoCmd.OpenReport cstrReport,0,,,3 'repo rt mode=5, dialog=3, 2=Print preview
appAccess.DoCmd.Close 3, cstrReport          'acReport = 3
appAccess.Quit
Set appAccess = Nothing

'Reset Default printer
on error resume next
WshNetwork.SetDefaultPrinter strname

WshNetwork.SetDefaultPrinter "\\Eureka\" & strname

[June7]

Process still not clear to me. I don't know AS400 so the statement "enters their selection criteria into the AS/400 screen and it in turns creates a query file" means nothing to me. But if the issue is that this 'query file' gets overwritten by multiple users, why is there not multiple query files?

This is a VBScript? Why is it not a VBA procedure?

Is this a split database?

If split, does each user run their own copy of frontend?