Store value after code execution

[gdesreu]

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?

 

[offthelip]

You could store that information in a custom property. Google EXCEL custom properties, you can access them with vba.

 

[gdesreu]

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?

 

[Joe4]

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

 

[gdesreu]

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

Dim qdf As QueryDef
Dim strsql As String
strsql = "UPDATE tbl_dbtype SET dbtype = 'Access';"

and vice versa for the PostgreSQL switch

 

[Micron]

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.

 

[gdesreu]

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?

 

[Micron]

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

 

[gdesreu]

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.

 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.

 

[Micron]

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.