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:
3001,F3001,L3001
3002,F3002,L3002
3003,F3003,L3003
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!
Code:
//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.AddNew;
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;
objRs.Update();
objRs.Close();
} //end-while
// Cleanup!
objDB.Close();
objRs = null;
objDB = null;
dBforums
>
Best Way to Update DB Using a Transaction file
04-23-10, 09:59
Linear Mode
