Problems with refresing data in a multi-user environment

[HockeyFan]

Here is the issue: My database has linked tables to our mainframe computer. Records are being update throughout the day on the mainframe. In my database, I have a table which is an exact copy of the linked table, but not linked.
Currently in my database, when you enter it, I delete all the records out of the copy table, and then do an append query from the linked table to the copy table, to populate the copy table with all of the most current records.

The problem is that if a user is already in the database, and then another user enters the database, it will then do a delete and append routine. This is a problem for the person already in the database. One last item to mention. My database uses pretty much 1 form, so I can't expect the user to move back and forth between forms to do a refresh. They leave they database open all day, on the same form.

How can I display the most current records for the new person entering the database, without interfering with the people already in the database?

 

[HockeyFan]

anyone have any ideas?

 

[FancyPrairie]

I'm not sure why you are not linking the tables, but...If your users are not updating the data in the "copy table", then don't copy the table to the BE database, but rather to the user's front end. Then, basically, each user has their own copy of the "copy table".

 

[HockeyFan]

the tables are linked to the mainframe. the records are updated throughout the day on the mainframe computer. when a person enters my db, i delete all the records from the copy table, and then append all the records from the linked table to the copy table. that way the user will see all the records that are current up to the moment they opened my database.
are you suggesting that each user has their own copy of the 'copy table' on their FE? And at the time they enter the db, to clear the recs in their table, and then append recs from the linked table to their own copy.
If this is what you're suggesting, then i wonder how i would manage this feat?

 

[RandyMyers]

That is exactly what I would do. You would pretty much do it the same as you do now, except that each users front-end would contain the "copy" table and the link would be back to the mainframe table. When they open their front end it would delete and refresh their local table. All procedures should be the same as you are currently doing, just running against their local table instead of the lniked "copy" table.

The only other thing I would probably concider doing is to have the application compact upon exiting because deleting and recreating tables on each open will cause the application to get very large, very quickly.

 

[jsteph]

How many records on average in the mainframe table? How current do the users need the data to be? A day old ok? Or do they need last hour's updates? Or more current? What is the db and the box it's on?

What kind of work do they do on your form? I assume no updates or inserts. If it's just searching based on criteria, there is a better way.

I had a similar issue. We had Informix db on an IBM p670.

Users would search on, say, PO num, cust num, or a few other known fields. If there is an oledb provider for the mainframe mainframe db then you could use ADO and create a simple stored procedure on the mainframe (depending of if it supports that) and pass the params based on the criteria. The sp's sql is already compiled and I'd swear this returned results faster than a local JET table would have.

If not OLEDB, but an ODBC driver exists, a pass-thru query can be created dynamically against the live data for each search. This is a read-only query (you can set the odbc connection to be read only as well for better security/peace of mind). It's also very, very fast, even if the criteria isn't indexed (depending on the mainframe speed).

Basically the form opens empty--either with no query source or a dummy crit sure to return nothing--you don't want to base the form on whaterver crit was in the last fetch--and definitely not on the linked table. Then the user enters one or more items of criteria, hit's a 'search' button, you build the sql, set the form's RecordSource to the pt, and bang--it's there. Live, up to the second data. Reports can be based on the same pass-thru query.

If this is not what the basic intention is, let me know, but if the freshness of the data is important, you want to stay away from schlepping all the records over the network for every single user that so much as opens the .mdb
--Jim

 

[HockeyFan]

I like randymyers idea of each having their own 'copy table' on their fe, but the part i can't figure out, is how would I maintain a separate table on each users fe? I just don't understand how each user would have their own table. An upon opening the db, how would i tell it to update that persons db and not someone elses?
p.s. thanks jsteph for the reply. we have an iseries as/400 890. However the information that the mainframe stores is updated continuously throughout the day. That is why when a user enters the db, they need to see the most current info as of when they open the db.

 

[PHV]

Anyway why are the users seeing only a copy and not the original linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HockeyFan]

I have many queries that run on the data and it seems to run a lot faster to have the entire table already built and sitting in access as opposed to each query going out to the mainframe. I tried doing it that way and it takes forever for the results.

 

[PHV]

Even with a PassThru query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HockeyFan]

i've never tried a passthru query. the queries i'm using are select queires. i have no experience with a passthru.

 

[jsteph]

HockeyFan,
IBM Client/Access has decent ODBC drivers, I've worked extensively on different projects with AS/400 backends, and you can get very, very fast results with a pass-thru. Also, as I explained in thread181-1089232 JET queries can operate identical to pass-thru, depending on the sql and the server capabilities.

I'd go this route. The simplest path is to do a passthru query, and create the sql dynamically for each search/fetch. One thing to watch out for is that Access stores permanently each sql that you dynamically change (it's hidden), so the db can grow in size if the select is a huge statement.
--Jim

 

[HockeyFan]

to try this, can i just take my exact query and change it from a select query to a passthru?

here is an example of one of my select queries.


SELECT CopyOfLinkedTable.DATANM, CopyOfLinkedTable.ABBRV, CopyOfLinkedTable.SRCDSC, CopyOfLinkedTable.PRODTE, CopyOfLinkedTable.DSTTRM
FROM TerminalsAndRegions LEFT JOIN CopyOfLinkedTable ON TerminalsAndRegions.Terminal = CopyOfLinkedTable.DSTTRM
WHERE (((CopyOfLinkedTable.SRCDSC)="USEALIAS") AND ((CopyOfLinkedTable.PRODTE)=(SELECT Max (ProDTE) From CopyOfLinkedTable c Where c.dsttrm = CopyOfLinkedTable.dsttrm)));

 

[jsteph]

HockeyFan,

just take my exact query and change it

Not necessarily.
Often, when you link, the table gets a prefix that isn't part of the real table--with AS/400, it'll be the Library, with SQL-server, it's the owner, usually dbo.

A passthru must contain the names of tables and fields (and functions, if needed) known by the server. So unless the AS/400 table is named 'CopyofLinkedTable', it won't work.

With a Jet query, you can use 'CopyofLinkedTable' if it's the JET name of the Link.
--Jim

 

[HockeyFan]

actually funny enough, the linked table is called linkedAs400Table, and the copy table I use is called CopyOfLinkedTable.

 

[jsteph]

You mean on the AS/400, the actual table name is 'linkedAs400Table'? Remember, a pass-thru is just that--it passes the sql through to the server.

You could write a pass-thru with the sql "Bite_Me". Access will save it with no error, and if you have a procedure called "Bite_Me" on the server, it will run. If not, you'll get an error that is passed-thru from the server back to Access that'll tell you that this doesn't exist, or whatever. Think of it as the sql commandline on the 400, you can only enter stuff that is known by the 400.

The querie's ODBC Connection string will have the IBM C/A DSN you've set up, and that setup has many pages where you set up the default library, the isolation level, etc, so if you reference a table that's not in the library list, you'll still get an error.
--Jim

 

[HockeyFan]

Oh, that's what i named it in my db. On the as/400, it has a different name, and is in some library. I had to point to it and set that stuff up when i initially linked the db to the 400 table.

 

[RandyMyers]

Well I usually develop applications where the data is stored on servers, mostly in SQL Server and the application links to the data through ODBC. This seems to work very well, however there have been occasions where tables need to be populated and destroyed when running a particular report for example. This can be accomplished on a temporary table on the SQL Server. On some occasions it was found that particular needs to populate and delete a table happened frequently and values in this table were used for a time frame after it was created. If another user performed the same function they could effectively over write the other users data. Basically, the solution was to have a local table (Jet Engine table) built into their front end. This table can be accessed like any other table, ODBC or local and delete, append, update queries work exactly the same. Therefore you could fire some code attached to the open event of the form that is loaded when your application launches (or many other methods) that would run a delete query on their local table and then an append query to re-populate the local table. The system really does not care where the table is stored, in their local mdb or linked through ODBC, etc.

The only concern with doing this method is to make sure to compact the database/application on exit so that it does not continually grow and grow.

 

[HockeyFan]

Thanks for the feedback. The only problem that I have with this method, is how to identify that the db should delete and replace this the table to the new person logging on. What would keep it from doing this to a user already logged in.
I thought of a different idea perhaps you could help me with. How about if when someone logs into my db, it looks at the current date and compares it to a field. If the current date is greater than this field, then it does nothing. If current date is less or equal to this field, then the db will delete and append the records as i do currently. After performing this function, the db will then move the value of current date into this other field. This will make the db do the delete and append only once per day, only to the first person that logs in. After that, when subsequent users logged in, their current date would match this other field. Are you able to help me figure out how to code this, or do it in a simple query? I'm not very good with vb at all. I typically only create queries using the design interface.

 

[jsteph]

HockyFan,
For grins, why not give this a try? This code would go in a button on the form that you'd click once criteria is entered in textboxes.

Remember, in the below code, anywhere that you see 'CopyOfLinkedTable' it should be replaced with the actual table name on the 400, same with TerminalsAndRegions. As I recall the 400 supported the LEFT JOIN syntax. If not, you can use OUTER JOIN with the +, or a WHERE join.

Dim sq As String, strWhere As String
Dim qd As DAO.QueryDef, strUseAlias As String

'Base sql, this could even be a constant.
sq = "      SELECT a.DATANM, a.ABBRV, a.SRCDSC, a.PRODTE, a.DSTTRM"
sq = sq & " FROM TerminalsAndRegions b "
sq = sq & " LEFT JOIN CopyOfLinkedTable a ON b.Terminal = a.DSTTRM"

'Get Where clause from criteria textboxes
strUseAlias = Trim("" & Me!txtCrit_UseAleas)
'(other crit. could follow and be added to WHERE clause)
strWhere = " WHERE a.SRCDSC='" & strUseAlias & "' AND a.PRODTE= "
strWhere = strWhere & "(SELECT Max (ProDTE) "
strWhere = strWhere & " FROM CopyOfLinkedTable c "
strWhere = strWhere & " WHERE c.dsttrm = a.dsttrm)"

'below is the form's RecordSource--a Passthru query
Set qd = CurrentDb.QueryDefs("qryPassThruSource")
qd.SQL = sq & " " & sqWhere
'This effectively requeries the form with the new criteria
Me.RecordSource = qd.Name

That's a very simplified example, you may need to escape any embedded quotes from the criteria for example, and other validation would be needed for other criteria, but I think it'd be worth giving this a try to see how it goes, you may be surprised.

I find that once you start getting into looking at dates and testing whether somthing should be deleted/appended, it gets real messy real fast. Also, the network guys will thank you for not clogging the network with constant refreshes from this table.
--Jim