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]

thanks so much jsteph for taking the time to put this together and help me out. However as opposed to linking directly to the 400 and doing a passthru, i would rather continue to make a copy of the linked table and have all my queries and reports point to my copy.
What i would like to do as psuedocode however is as i mentioned above. I was thinking about an 'on load' event on my form and then put the code into there. I was thinking i should make two text boxes. Have the one be a variable holder and the other one's source be now().
When someone logs into my db, it looks at the current date and compares it to a variable field. If the current date is greater than this field, then it does nothing. If current date field value 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.
The only problem is that i don't know how to write the code to do this.

 

[RandyMyers]

HockeyFan,

If you are referring to each user having their own "CopyTable", than their table is within their front end that is installed on their PC. Any operations that are performed on this table would have no way of effecting the "CopyTable" on the other user's PC. Thye are completely independant since they are running internal, inside their own front end on their own PC. The only thing they share are the linked tables back on the server.

 

[HockeyFan]

interesting..... I guess i didn't realize that if i didn't put the table in the be, and simply put it in the fe, than each user would automatically be working off of their own 'copy table'. That's pretty cool. let me try some things and i'll let you all know how it goes. thanks for everyone's help on this.

 

[HockeyFan]

Ok jsteph, i tried setting up my pass-through query but i have some questions. i put your code in as a start, but that just left me with a couple more questions. first of all, my file name on the 400 is vrpwf045. the questions i have are because the table called terminalsandregions, is in the access db. how am i going to be able to link up the 400 and the access table together? please help.

 

[jsteph]

HockeyFan,
I thought terminalsandregions was on the 400. If that is a table that doesn't change often--which it sound's like it doesn't, you could use a passthru to create it on the 400 and then populate it. You may want to ask one of the '400 guys there to do that, you may not have an ODBC username with rights to use CreateTable.

Otherwise, it's back to your original thought of schlepping the records over every day.

However, the terminalsandregions table, based on my assumptions due to it's name, is probably small and doesn't change much?

Also, you're not using any fields from that table in the SELECT nor in the WHERE clause, *and* it's a LEFT join.

Normally, this left join will serve to select all of the terminalsandregions records, and show blanks for those from vrpwf045 that don't exist in the terminals table. However, you then have criteria on the vrpwf045 table, this sort of negates the Left join behaviour.

Is this the join you wanted, or do you want to use the terminals table as a way of restricting records from the other table? If you just want to force it to select only terminals existing in the terminals table, then a standard join will do.
--Jim

 

[HockeyFan]

no, terminalsandregions is on the pc, not the 400. I suppose the passthrough won't work after all.

Just a question though... as far as the join type, i just chose the left join because it said all records from the terminalsAndRegions table and only the recs from the vrpwf045 table that are equal. The reason i chose this is because i wanted to make sure my list always contained the 300 records from the terminalandregions table. Would it make my query run faster if i did an inner join or some other type. I want to make sure all 300 of my termandregions table are represented.

 

[jsteph]

But you're selecting criteria on the other table--this means that only non-null (in this context, that means 'existing') records from the vrpwf045 table will be selected--ones that meet the criteria. Without the extra criteria, then yes, you'd get all records from the terminals table and for records in the terminals table with no match, you'd get basically a dummy record. But since the criteria is there on the vrpwf045 table, then those dummy records are not selected, because the criteria field would be Null. So it's really the same as a standard Join.

I'm not sure about the low-level workings of how the join is made--the query engine chooses different plans for different sql, so maybe down deep JET will just make a standard join and ignore the Left (outer) Join syntax in cases where any criteria is used on the 'Only records from...' side of the join.
--Jim

 

[HockeyFan]

ok. thanks for the explaination.