EnvVar (Get/Set)

[UnknownHost]

Hi there dear MS Community,

I've got a simple question or maybe yet advanced, setting EnvVar saves a value in the enviornmental location of a chosen variable,value, then you can retrieve that value from the same script or other scripts by getting the EnvVar which I'm sure you're all familiar with.

My question here is, closing my Macro Scheduler, or restarting my computer, completely wipes out all my previously saved EnvVars, as retrieving them from other scripts returns blank strings, which my script tells me there is no such EnvVar existing (I have it as a fail safe incase I'm retriving a wrong value).

I realized this issue when I closed my Msched completely, and all the stored EnvVar from previous script session was gone? Isn't it supposed to be stored, and if it is, is there a way to make the values I store permanent and not completely wiped out on application Exit/Restarting computer?

Thanks in advance

[Grovkillen]

I would use a simple access database (file) for this.

[UnknownHost]

I would use a simple access database (file) for this.

Thank you for your reply, how would I go on about storing my EntVar in a database (file) for this? I assume a .txt file would do, or dll? I've never done that before so would you kindly guide me? Thank you.

And how would one retrieve the values from the file using GetEnvVar after Set? Appreciate the quick reply!

[Grovkillen]

Let me get back to you tomorrow.

[UnknownHost]

Let me get back to you tomorrow.

Okay thank you again for your time, appreciate it

[UnknownHost]

Can't I just use Registry for this tbh? Like Writing/reading keys? It seems to be working that way values are stored, or is it anything against using registry for this purpose storing variables that contains values to be re-used at a later stage?

[Grovkillen]

I would not use the registry for this job, it would work but feels wrong in so many levels.

So, how I would do this:

Creating the database and setup the ODBC connection

• Win+R (start the run command)

• odbcad32

• Add new datasource

• Select the Microsoft Access Driver

• Create a good name for the connection

• Create the database by clicking that button, save in a logical place

• Ok and Ok

In Macro Scheduler, create/select the connection string

• In the code builder click on the DBConnect command

• For the Connection String click on the "..." button

• Go to the connection tab

• Under "1" select your database connection you created (dropdown list)

• Use the test connection button just to make sure the connection is working

• Ok and Insert

Code should look something like this:

Code: Select all

DBConnect>Provider=MSDASQL.1;Persist Security Info=False;Data Source=access,dbH

To create a table in this database you can use something like this:

Code: Select all

Let>ACCESS_CONNECTION_STRING=Provider=MSDASQL.1;Persist Security Info=False;Data Source=access
LabelToVar>create_table,DB_STRING_TO_EXECUTE
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBExec>dbH,DB_STRING_TO_EXECUTE,DB_r
DBClose>dbH

/*
create_table:
CREATE TABLE VariablesMemory (
    ID AUTOINCREMENT PRIMARY KEY,
    VariableName TEXT(255) UNIQUE,
    VariableValue TEXT(255),
    VariableType TEXT(50),
    LastUpdated DATETIME
);
*/

Now you have a table that you can talk to. The "LastUpdated" is just a column you may use to show what time you last changed. Useful in some cases, some not.

To read and write to this list (here's some example just for you to get going):

Code: Select all

Let>ACCESS_CONNECTION_STRING=Provider=MSDASQL.1;Persist Security Info=False;Data Source=access
Let>VARIABLE_NAME={"TestVariableName1"}
Let>VARIABLE_VALUE={"123456789"}
Let>TYPE_OF_VARIABLE={"StringValue"}

LabelToVar>get_all_variables,DB_QUERY_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBQuery>dbH,DB_QUERY_STRING,DB_r,DB_n,DB_f,1
DBClose>dbH

LabelToVar>get_variable_value,DB_QUERY_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBQuery>dbH,DB_QUERY_STRING,DB_r,DB_n,DB_f,1
DBClose>dbH
If>DB_n=0
//variable not found
Let>VAR_%VARIABLE_NAME%=##NOT FOUND##
Else>
//variable found "DB_r_1_VARIABLEVALUE"
Let>VAR_%VARIABLE_NAME%=DB_r_1_VARIABLEVALUE
Endif>

LabelToVar>set_variable_value_update,DB_EXEC_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBExec>dbH,DB_EXEC_STRING,DB_res
IfNot>DB_res=1
LabelToVar>set_variable_value_insert,DB_EXEC_STRING
DBExec>dbH,DB_EXEC_STRING,DB_res
Endif>
DBClose>dbH

/*
get_variable_value:
SELECT *
FROM VariablesMemory
WHERE VariableName = '%VARIABLE_NAME%';
*/

/*
set_variable_value_update:
UPDATE VariablesMemory 
SET VariableValue = '%VARIABLE_VALUE%', 
    LastUpdated = Now() 
WHERE VariableName = '%VARIABLE_NAME%';
*/

/*
set_variable_value_insert:
INSERT INTO VariablesMemory (VariableName, VariableValue, VariableType, LastUpdated)
SELECT '%VARIABLE_NAME%', '%VARIABLE_VALUE%', '%TYPE_OF_VARIABLE%', Now()
FROM (SELECT COUNT(*) AS Count FROM VariablesMemory WHERE VariableName = '%VARIABLE_NAME%') AS SubQuery
WHERE SubQuery.Count = 0;
*/

/*
get_all_variables:
SELECT *
FROM VariablesMemory;
*/

The best part of this approach is that you now have learnt how to use a simple database and you'll love having this knowledge from now on. Trust me

[UnknownHost]

I would not use the registry for this job, it would work but feels wrong in so many levels.

So, how I would do this:

Creating the database and setup the ODBC connection

• Win+R (start the run command)

• odbcad32

• Add new datasource

• Select the Microsoft Access Driver

• Create a good name for the connection

• Create the database by clicking that button, save in a logical place

• Ok and Ok

In Macro Scheduler, create/select the connection string

• In the code builder click on the DBConnect command

• For the Connection String click on the "..." button

• Go to the connection tab

• Under "1" select your database connection you created (dropdown list)

• Use the test connection button just to make sure the connection is working

• Ok and Insert

Code should look something like this:

Code: Select all

DBConnect>Provider=MSDASQL.1;Persist Security Info=False;Data Source=access,dbH

To create a table in this database you can use something like this:

Code: Select all

Let>ACCESS_CONNECTION_STRING=Provider=MSDASQL.1;Persist Security Info=False;Data Source=access
LabelToVar>create_table,DB_STRING_TO_EXECUTE
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBExec>dbH,DB_STRING_TO_EXECUTE,DB_r
DBClose>dbH

/*
create_table:
CREATE TABLE VariablesMemory (
    ID AUTOINCREMENT PRIMARY KEY,
    VariableName TEXT(255) UNIQUE,
    VariableValue TEXT(255),
    VariableType TEXT(50),
    LastUpdated DATETIME
);
*/

Now you have a table that you can talk to. The "LastUpdated" is just a column you may use to show what time you last changed. Useful in some cases, some not.

To read and write to this list (here's some example just for you to get going):

Code: Select all

Let>ACCESS_CONNECTION_STRING=Provider=MSDASQL.1;Persist Security Info=False;Data Source=access
Let>VARIABLE_NAME={"TestVariableName1"}
Let>VARIABLE_VALUE={"123456789"}
Let>TYPE_OF_VARIABLE={"StringValue"}

LabelToVar>get_all_variables,DB_QUERY_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBQuery>dbH,DB_QUERY_STRING,DB_r,DB_n,DB_f,1
DBClose>dbH

LabelToVar>get_variable_value,DB_QUERY_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBQuery>dbH,DB_QUERY_STRING,DB_r,DB_n,DB_f,1
DBClose>dbH
If>DB_n=0
//variable not found
Let>VAR_%VARIABLE_NAME%=##NOT FOUND##
Else>
//variable found "DB_r_1_VARIABLEVALUE"
Let>VAR_%VARIABLE_NAME%=DB_r_1_VARIABLEVALUE
Endif>

LabelToVar>set_variable_value_update,DB_EXEC_STRING
DBConnect>ACCESS_CONNECTION_STRING,dbH
DBExec>dbH,DB_EXEC_STRING,DB_res
IfNot>DB_res=1
LabelToVar>set_variable_value_insert,DB_EXEC_STRING
DBExec>dbH,DB_EXEC_STRING,DB_res
Endif>
DBClose>dbH

/*
get_variable_value:
SELECT *
FROM VariablesMemory
WHERE VariableName = '%VARIABLE_NAME%';
*/

/*
set_variable_value_update:
UPDATE VariablesMemory 
SET VariableValue = '%VARIABLE_VALUE%', 
    LastUpdated = Now() 
WHERE VariableName = '%VARIABLE_NAME%';
*/

/*
set_variable_value_insert:
INSERT INTO VariablesMemory (VariableName, VariableValue, VariableType, LastUpdated)
SELECT '%VARIABLE_NAME%', '%VARIABLE_VALUE%', '%TYPE_OF_VARIABLE%', Now()
FROM (SELECT COUNT(*) AS Count FROM VariablesMemory WHERE VariableName = '%VARIABLE_NAME%') AS SubQuery
WHERE SubQuery.Count = 0;
*/

/*
get_all_variables:
SELECT *
FROM VariablesMemory;
*/

The best part of this approach is that you now have learnt how to use a simple database and you'll love having this knowledge from now on. Trust me

First of all, I would like to thank you, and thank you again for all the time you took to type this and the thorough guidelines to help me understand this, this is completely new to me so I'll probably ask a few questions here and there, and show you example of my current script and the variables/values I'm working with and how I'm fetching them, but I really do have to thank you for this new knowledge and I know when I learn this as you said, it will be great having it from here on out, because I do script a lot and I do make very complex scripts with a lot of forth and back depending on what I am doing, and this is completely beautiful and new, thank you once again.

I will get back to you as soon as I try to set this up with the current thing I have going, and tell you how it goes, I appreciate your time Grovkillen!
- Tacker så mycket.

[Grovkillen]

Happy to help. I try to be that guy I wanted to find when I was starting out.