Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Clear MS-Access Memory with Javascript before next query

    I was having some issues with retrieving the SUM from a column in my access database and displaying the value in a html textbox.--This i was able to fix with the function below.

    Issue: I've found that the below function works fine to retrieve the sum. But for some reason, if a value changes in my database, the SUM does not get updated when the function is called again.

    Here is how i found it has something to do with the memory not getting refreshed/cleared in Access

    I made a "copy" of my access DB, renamed it to something different, changed the values in the column im summing, and updated the code with the new DB path. once i did this, my textbox populated the correct sum. Again, if i changed values in this DB copy, the new sum would not refresh..

    It appears that access is unable to clear the last query run and must be done before i run my SUM function. And this is something i'm clueless about writing in JS. hoping someone has encountered this before.

    Here is the working function that retrieves the SUM from a column in my DB:

    Code:
    var adoconn = new ActiveXobject("ADODB.Connection");
    adoconn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='database.mdb'");
    var cmd = new ActiveXObject("ADODB.Command");
    cmd.ActiveConnection = adoconn;
    var adoRS = new ActiveXobject("ADODB.Recordset");
    var strSQL = "Select SUM(database_column_name) As Total FROM tablename";
    cmd.CommandText = strSQL;
    adoRS = cmd.Execute();
    textbox1.value = adoRS.Fields(0);
    Thank you for reading this far. Desperately looking for some help

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Same thing happens with Access when connected to server-side code, such as ASP code.

    UPDATEs cannot be seen until the connection is closed and reopened. There are some ways around this restriction, but they are more trouble than they are worth.

    And your code is overly complex. You can make it a lot simpler so that closing and reopening the connection is easier.
    Code:
    // treat this as a CONSTANT:
    var connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='database.mdb'";
    
    var adoconn = new ActiveXobject("ADODB.Connection");
    adoconn.Open(connString);
    
    // there is no need at all for the Command object!!!!
    var strSQL = "Select SUM(database_column_name) As Total FROM tablename";
    // and doing this is the fastest and easiest way to execute a SELECT into a recordset:
    var adoRS = adoconn.Execute(strSQL);
    textbox1.value = adoRS.Fields(0);
    adoRS.Close(); // don't forget to do this
    ...
    ... now you have some code that updates the DB ...
    ...
    // so close and reopen the DB
    adoconn.Close();
    adoconn.Open(connString);
    // and re-execute the query
    adoRS = adoconn.Execute(strSQL);
    ...
    ...
    I will bet you it works!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    tequilasunsette (01-21-2013)

  • #3
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    not working :(

    Thank you for the quick reply mate.. unfortunately i had no luck with the new function.

    Here is what i did: re-wrote my function as described above, renamed my table and updated new data to sum. When i ran the function, i got this error:
    "The microsoft jet database could not find the input table or query.."
    Now obviously i checked and triple checked i have the correct table name in my code.

    My table name is called "Marvin" (when the SUM was retrieving incorrectly)
    I changed this table name to "xMarvin".
    Now here is the weird part.. With the DB having the new name, if i changed my code with table name "Marvin" the SUM is being populated LOL!! currently no tables are named "Marvin"!!

    Looks like Access has memory that cannot be erased!
    I'm getting really desperate suffering for over a week now

    Any other ideas??

    Here is the function cleaned up:

    Code:
    var connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='database.mdb'";
    var adoconn = new ActiveXobject("ADODB.Connection");
    adoconn.Open(connString);
    var adoRS = new ActiveXobject("ADODB.Recordset");
    
    var strSQL = "Select SUM(database_column_name) As Total FROM xMarvin";
    adoRS = adoconn.Execute(strSQL);
    textbox1.value = adoRS.Fields(0);
    adoRS.Close();
    adoconn.Close();
    
    adoconn.Open(connString);
    adoRS = adoconn.Execute(strSQL);
    textbox1.value = adoRS.Fields(0);
    adoRS.Close();
    adoconn.Close();

  • #4
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    Code:
    var adoconn = new ActiveXObject("ADODB.Connection");
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    ??? Is this *REALLY* your query:
    Code:
    var strSQL = "Select SUM(database_column_name) As Total FROM xMarvin";
    Surely you don't really have a field by that name in your table?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    No that is not the DB column name, my code on the real page has the correct name. that was used only to describe the situation.
    Also, I had the big "O" in the above code, was a typo..

    It appears to me that my queries that were previously run are stuck in limbo somewhere in MS Access' memory

    any other tricks i can try?
    Is there some function we can run to somehow clear the memory and then i can call the SUM function above??

    thank you for sticking with me this far. I really appreciate it.

  • #7
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Alright, i did some research and found that we need to set the recordset and connection to "nothing". this will clear it from the memory.

    only problem is i dont know how to write this in JS. the site had an example with ASP..

    does this give us a lead in solving the issue?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    NOTHING is the VBScript equivalent of null in JS.

    You can try it. Dunno if it will work.

    Code:
    var connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='database.mdb'";
    var adoconn = new ActiveXobject("ADODB.Connection");
    adoconn.Open(connString);
    var adoRS = new ActiveXobject("ADODB.Recordset");
    
    var strSQL = "Select SUM(database_column_name) As Total FROM xMarvin";
    adoRS = adoconn.Execute(strSQL);
    textbox1.value = adoRS.Fields(0);
    adoRS.Close();
    adoRS = null;
    
    ... so your update ...
    
    adoconn.Close();
    adoconn = null;
    
    var adoconn = new ActiveXobject("ADODB.Connection");
    adoconn.Open(connString);
    adoRS = adoconn.Execute(strSQL);
    textbox1.value = adoRS.Fields(0);
    adoRS.Close();
    adoconn.Close();
    
    // if needed:
    adoRS = null;
    adoconn = null;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    tequilasunsette (01-22-2013)

  • #9
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    The o keeps winking at me (twice):

    Code:
    var adoconn = new ActiveXObject("ADODB.Connection");
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Yep. That indeed must be O.

    Fun part is...after that, the upper/lower case of method and property names do not matter.

    Code:
    var fso = new ActiveXObject("Scripting.FileSystemObject")
    var fl = fso.OpEnTeXtFiLe("c:\\inetpub\\wwwroot\\junk0.html");
    Works just fine. As does fso.opentextfile or fso.openTextFile or whatever.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I tried the above code to set the recordset and connection to nul.. doesnt work. I'm still getting a SUM that is historical (

    i'm about to pull out all my hair!! lol

    anything else i can try mate?

    kind regards,
    marvin.

  • #12
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    Browser cache, possibly.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Use a different database? Install and use MySQL. It doesn't have any of Access's stupid problems.

    And there's a freebie Access-to-MySQL database converter out there. Been years since I used it, but it works quite well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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