Clear MS-Access Memory with Javascript before next query

[tequilasunsette]

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:

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

[Old Pedant]

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.

// 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!

[tequilasunsette]

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:

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();

[AndrewGSW]

var adoconn = new ActiveXObject("ADODB.Connection");

[Old Pedant]

??? Is this *REALLY* your query:

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?

[tequilasunsette]

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.

[tequilasunsette]

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?

[Old Pedant]

NOTHING is the VBScript equivalent of null in JS.

You can try it. Dunno if it will work.

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;

[AndrewGSW]

The o keeps winking at me (twice):

var adoconn = new ActiveXObject("ADODB.Connection");

[Old Pedant]

Yep. That indeed must be O.

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

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.

[tequilasunsette]

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.

[WolfShade]

Browser cache, possibly.

[Old Pedant]

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.