Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Maryland, USA

    Unanswered: Best Way to Update DB Using a Transaction file

    ENV: WIN2003 Server, WSH, JScript, ADO, Access 2003
    Hello Folks, I am wondering what would be the best pratice and most effecient way of
    reading an external text file of transactions and either updating the DB record if the primary key exists
    or creating a new DB record if the primary key does not exist.
    This file contains up to 13,000 records and would be processed daily.
    Example text file of transactions (primary key is Custnum):
    Columns are:Custnum,firstname, lastname
    Example data:

    So this is what I was planning but I'm not so sure if it is the best way as it would
    require a recordset open and close for every transaction. This method does work and was tested OK.
    Any suggestions or comments are welcomed!

    //transupd3.js - demo load transaction file and do updates
    var thisRec = [];
    var myDB = "customers.mdb";
    var SQL;
    var dbDate = "2010-04-20 16:20:10";
    var fsoReading = 1;
    var adOpenKeyset = 1;	
    var adLockOptimistic = 3;
    var objFSO = new ActiveXObject("Scripting.FileSystemObject");
    var FH = objFSO.OpenTextFile("trans3.txt", fsoReading);	
    var objDB = new ActiveXObject("ADODB.Connection");
    objDB.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myDB + ";");	// open Access DB
    var objRs = new ActiveXObject("ADODB.Recordset");		// create recordset object for db
    while (!FH.AtEndOfStream) {
    	thisRec = FH.ReadLine().split(",");		//csv file split
    	SQL = "SELECT * FROM tblcustomers WHERE cid = '" + thisRec[0] + "';";
    	objRs.Open(SQL, objDB, adOpenKeyset, adLockOptimistic);
    	if (objRs.EOF) {
    		WScript.Echo("CID not found - do AddNew: "+ thisRec[0]);
    		objRs.Fields("CID") = thisRec[0];
    	else {
    		WScript.Echo("CID found - do UPDATE: " + thisRec[0]);
    	}		//end-if-else
    	objRs.Fields("FirstName") = thisRec[1];
    	objRs.Fields("LastName") = thisRec[2];
    	objRs.Fields("upddate") = dbDate;
    }		//end-while
    // Cleanup!
    objRs = null;
    objDB = null;

  2. #2
    Join Date
    Jan 2002
    Bay Area
    I have a quick solution:
    1) Import the transactions file
    2) run an update query
    3) run an append query to add new records.

    In the Append query, your keyed table will reject adding records that exist in the table. Both queries update a date field in the table.

    I would use a macro to run the whole process and turn off Warnings to suppress the warning messages.

    For testing, the attachment contains Trans3.txt, which has name changes to 3 existing records, and it has 3 new names. It has already been imported. The db includes the two queries. Table1 has 3 records, and the first names will be changed when you run the Update query. 3 new records will be added when you run the Append query.

    I do not know if this solution qualifies for "best practice".
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts