Store value after code execution

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
318
Hi guys, I am executing code that converts my queries between postgres tables and access tables, that code works great. What I'm trying to do is store the mode type I'm using (postgres or access) somewhere in a safe spot so that I can read it when the database is opened and determine if the database is in Access mode or Postgres mode. I can then use it to automatically choose the right tables in some of my other code.
Basically after it finishes updating the qdf.sql I want it to store whatever mode was last selected. I'm hoping to display it on a user form when it opens and then I think I can take it from there. Whats the best way to handle this. Any ideas?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You could store that information in a custom property. Google EXCEL custom properties, you can access them with vba.
 
Upvote 0
You could store that information in a custom property. Google EXCEL custom properties, you can access them with vba.

Thanks I tried a few examples online and they threw errors. I guess I could handle this by passing the combobox value to an existing table but I'm not sure how to do this without using an update query. Does anyone know if its possible to store the combobox value directly to a table when either the database is closed or the form is closed?
 
Upvote 0
I guess I could handle this by passing the combobox value to an existing table
If you made the Combo Box bound to a table field, it will do this automatically.
I often create a Settings Form that is bound to a Settings Table. I set this up to not allow record additions or deletion, so my Settings Table is always exactly one record long. So it just tracks my latest settings.

Or, you could add VBA code to the AfterUpdate event on your Combo Box to write the record to a table.
Something like what is showed here: https://bytes.com/topic/access/answ...e-into-table-after-update-single-value-insert
 
Upvote 0
If you made the Combo Box bound to a table field, it will do this automatically.
I often create a Settings Form that is bound to a Settings Table. I set this up to not allow record additions or deletion, so my Settings Table is always exactly one record long. So it just tracks my latest settings.

Or, you could add VBA code to the AfterUpdate event on your Combo Box to write the record to a table.
Something like what is showed here: https://bytes.com/topic/access/answ...e-into-table-after-update-single-value-insert


Thanks, I couldn't get the binding to work correctly, so I used VBA instead. Working great now. Thanks
Code:
Dim qdf As QueryDef
Dim strsql As String
strsql = "UPDATE tbl_dbtype SET dbtype = 'Access';"
and vice versa for the PostgreSQL switch
 
Upvote 0
You're altering a stored querydef each time the parameter changes? I wouldn't do this as it raises the risk of db corruption IMHO, but you don't show enough of your code to determine that for sure. I got the impression from your first post. You can create a temporary qdef just by substituting "" for the name. In that case, nothing gets continually modified.
 
Upvote 0
You're altering a stored querydef each time the parameter changes? I wouldn't do this as it raises the risk of db corruption IMHO, but you don't show enough of your code to determine that for sure. I got the impression from your first post. You can create a temporary qdef just by substituting "" for the name. In that case, nothing gets continually modified.
In practicality, the code will only ever be executed once. Since the code exists in the front end and the table data backends will be in postgres, I'm not too worried about corruption as I can simply load a backup if that happens and I'm good to go. So far I've executed the code over a hundred times during testing and I haven't had any issues. I am curious, since I'm a novice at this compared to you guys, and I strive to do things the most correct way possible, how would the temporary qdef work in this example and what is the advantage?
 
Upvote 0
The advantages I know of:
- the qdef (and AFAIK, this is doable with a table def as well) resides only in memory for as long as circumstances allow. Setting qdf to Nothing, closing the db, etc. recovers the memory resources to the best of my knowledge.
- it can also be faster for some operations since an actual query object doesn't have to be handled
- reduces the chance of db corruption by not constantly modifying the sql property of a stored query

I thought you were modifying the qdf sql
Basically after it finishes updating the qdf.sql
which is what I was inferring raises the potential for corruption. I've never heard anyone be nonchalant about db corruption before, but if the potential for lost data or a suddenly broken db is of no concern to you, then who am I to disagree? All that is moot if you're not modifying a query def sql property over an over again, but it sure read like that was the case.

As for how it would work, hopefully I've answered that above (works the same way as far as the user or designer is concerned) except there is no query object to see in the Nav pane. You assign the sql statement to the qdf same as usual. The first part of the syntax is the name. As mentioned, you simply don't give it a name. But you cannot ignore the name property parameter. You just omit the name between the quotes; i.e. just use 2 double quotes - "".
Look here for info on this. It's called a temporary query def
 
Last edited:
Upvote 0
The advantages I know of:
- the qdef (and AFAIK, this is doable with a table def as well) resides only in memory for as long as circumstances allow. Setting qdf to Nothing, closing the db, etc. recovers the memory resources to the best of my knowledge.
- it can also be faster for some operations since an actual query object doesn't have to be handled
- reduces the chance of db corruption by not constantly modifying the sql property of a stored query

I thought you were modifying the qdf sql which is what I was inferring raises the potential for corruption. I've never heard anyone be nonchalant about db corruption before, but if the potential for lost data or a suddenly broken db is of no concern to you, then who am I to disagree? All that is moot if you're not modifying a query def sql property over an over again, but it sure read like that was the case.

As for how it would work, hopefully I've answered that above (works the same way as far as the user or designer is concerned) except there is no query object to see in the Nav pane. You assign the sql statement to the qdf same as usual. The first part of the syntax is the name. As mentioned, you simply don't give it a name. But you cannot ignore the name property parameter. You just omit the name between the quotes; i.e. just use 2 double quotes - "".
Look here for info on this. It's called a temporary query def

This is essentially the meat of the switch code, I simply reverse the tables for the switch back if needed.
Code:
 For Each qdf In CurrentDb.QueryDefs
        qdf.sql = Replace(qdf.sql, "public_table1", "table1")
    Next qdf
    
    For Each qdf In CurrentDb.QueryDefs
        qdf.sql = Replace(qdf.sql, "public_table2", "table2")
    Next qdf
If I misspoke in my first post its due to my ignorance of what the above is actually doing and its impact. All I know is it does exactly what I need it to do. If the above code could cause corruption, please let me know. As far as my nonchalantness regarding this corruption, for this particular database application (and others), all of my data is in back end tables, this switch is in a front end. I use front end / back ends to handle almost all of my data needs precisely to avoid corruption issues like we are talking about. Since the back-ends have nothing except tables of my data (where all our money is) I use the front ends as the interface to my data in order to query and manipulate that data as well as do all my tinkering, so if I corrupt a front end I simply delete it and reload a new front end and I'm back in business like nothing happened.
 
Upvote 0
You are continually modifying the sql property of 1 or more queries. This can cause corruption at worst (not that it will - it's one of several possible contributing factors), and db bloat in the least (more likely). So you appear to be correct in that it is your FE (front end) that will corrupt (if ever), which would still not be acceptable to me and may fail in the middle of a table update (the main thought behind my corruption concern), so it matters not that the db is split, but that's just the way I am. I mean, would I want to create something that I know can break at the worst time (Murphy's Law?)? Would I be comfortable in knowingly producing a vulnerable tool? Not ever. That doesn't mean you should look at things the same way - just giving you my perspective.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top