Updating SQL Lite Offline

I can't see an elegant solution to this but thought I'd pose the question here in case someone has a better insight than me to this type of issue. Here's the business problem.... System Owner runs a web based system (CF8 and SQL Server 2005 on a shared hosting platform). Their Client orders up an assessment for an Applicant (via the web system) and the Applicant goes to the web based system to complete the assessment. The system them notifies the System Owner (by email) that an assessment has been done and they do some further work and report back to the Client. As part of internal processes the System Owner rekeys a subset of the data into their internal systems (mainly demographic data, not assessment data). That triggers some internal activities as part of the assessment and also triggers raising an invoice for the service. The internal system is not web facing and (they tell me) is MS Access interfacing to SQL Lite. I'm trying to identify ways to reduce the level of rekeying here when an assessment has been completed but I think without some major internal changes or moving their internal system out to the web or elsewhere then it will remain a manual operation. Thank you!!! ++++++++++ Kevin Parker m: 0418 815 527 ++++++++++

If I understand the problem correctly...and rekeying = updating integer primary keys so that they're internally consistent in "this" DB, but possibly no longer externally consistent in the master DB... Best solution might be to change some/all primary keys to GUID's / UUID's. This should guarantee that they're always internally *and* externally consistent with no updating ever. "one's annual risk of being hit by a meteorite is estimated to be one chance in 17 billion,[34] that means the probability is about 0.00000000006 (6 × 10-11), equivalent to the odds of creating a few tens of trillions of UUIDs in a year and having one duplicate. In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. The probability of one duplicate would be about 50% if every person on earth owns 600 million UUIDs." http://en.wikipedia.org/wiki/Globally_unique_identifier http://en.wikipedia.org/wiki/Universally_unique_identifier On Wed, Aug 8, 2012 at 6:09 AM, Kevin Parker <trash80@internode.on.net>wrote: ----- Excess quoted text cut - see Original Post for more -----

if the subset of data is already stored, why not simply provide a table with the data and allow him to simply choose with checkboxes which data he wants to re-use and then copy/insert it into the other database. On Wed, Aug 8, 2012 at 11:09 AM, Kevin Parker <trash80@internode.on.net>wrote: ----- Excess quoted text cut - see Original Post for more -----

You should be able to have the CF8 system create a properly formatted CSV export of the new records. Then write a script to take that and ".import" that into the sqlite. From my :30 of googling for "bulk load" this was the first thing I came across: http://stackoverflow.com/questions/697004/bulk-load-data-into-sqlite Read up on sqlite's .import and see where that and a batch file gets you. Cheers, .jonah On 8/8/12 3:09 AM, Kevin Parker wrote: ----- Excess quoted text cut - see Original Post for more -----

Export the data to a spreadsheet or a tab delimited file and write a macro in Access to import it and put it in the correct tables. ----- Excess quoted text cut - see Original Post for more -----

I had the same situation. I set up a web service on the public server which returns a query object with all of the data to the private server which then can update the private database. I have an archive  flag field  on the table on the public server which notes if the record needs to be transferred.