I have two RBase databases A & B.
They are separate data sets with the exception of one table, which is needed by both. This table is rather small, only 18,158 rows at present, but grows every day. Once a month, older data is archived off. So it does not build over 20,000 rows. Some time ago, after a few posts, I made the decision to keep the two databases separate and SAttach the one table via RBase standard ODBC driver to data base B. Both database applications insert records and modify records into this one table constantly throughout the day by several users. Any inserts or updates are for one record only. No "mass" data inserts or updates. No one r ecord would be accessed by multiple users at the same time, but could be accessed by different users at different times. Database A had run literally years without any corruption issues and I am now seeing a somewhat intermittent problem with the one shared table and it may be related to some type of access timing via the ODBC driver. To investigate I did the following from the native database A: Browse all from SkidData where Serial# in (19006,19007) - returns results immediately Browse all from SkidData where Serial# = 19006 - returns results immediately from the SAttached Table in database B: Browse all from SkidData where Serial# in (19006,19007) - returns results in 1 min. 40 sec. Browse all from SkidData where Serial# = 19006 - return results immediately (Serial# is an indexed column.) The above shows that the two databases certainly access the one table differently . Enough back ground, now for the problem... The table has an autonum column to generate a sequential number and then commands to immediately update the Serial# column to the autonumbered column. (I need to keep the same Serial# in case of a data reload) On occasion, the serial# gets updated incorrectly (duplicated) and on occasion I am getting corruption in the shared table. So my questions are: 1) When installing Rbase upgrades, does the ODBC driver automatically get updated as well? I am assuming it does. (All users are Terminal Service thin clients, so only one computer is actually involved. I do not have to worry that one PC did not get updated while others did, i.e. mismatched versions.) 2)When SConnecting , I am using a DSNless connection. I am assuming when updating, I do not have to modify the SConnect line in any manner? (Concerning same version, just new release installs) 3)When Sattaching I use : SAttach SkidData as SkidData using Serial#. Is there anything else I should consider? After a release update, does the table need dropped and reattached? 4)To update the Serial# column to the Autonum column, the native database uses : Update SkidData set Serial# = LabelNo WHERE COUNT = INSERT Since the Count = Insert does not work on ODBC tables, I use: Set var vSerialNo = (Max( LabelNo )) in SkidData where primaryMO = . vMO Update SkidData set Serial# = . vSerialNo where LabelNo = . vSerialNo (The above logic is correct in that vMO will always be a unique constraint) There is a date/time stamp column in the SkidData table. Whenever I see a problem, the time stam p is always the same or one second difference in the problem data rows, which indicates the problem is related to a concurrent insert or update operation. Unless someone can point out some other considerations, I am to the point I will have to merge the two databases to alleviate the ODBC connection as I have found no other commonality and it appears suspect. (N o hardware or network issues, lan cards etc. etc. in this case, as this all runs on one computer. ) Any thoughts appreciated . Thanks -Bob
