none
Converting Object Properties in VBA RRS feed

  • Question

  • When I make changes to the property sheets for my various Access 2016 objects (tables, forms, reports, queries), are those changes codified in VBA somewhere in the backend? I converted all of my macros to VBA and noticed that the properties for many (all?) of my objects did not get converted to VBA, which I guess should be expected since I was only converting macros. Does anyone know if there a way to convert everything on my front end UI to VBA, so that I can fully customize things using the VB editor?
    • Edited by le_kai Saturday, March 11, 2017 6:33 AM
    Saturday, March 11, 2017 6:33 AM

Answers

  • If I understand your question correctly, you're wanting to codify the values from the Properties window for each object in your database.

    Yes, that's exactly what I'd like to do. Thanks for sharing the Import/Export Utility, I'll check it out. Do you have any more information on the the SaveAsText and LoadFromText functions, or how I might be able to go about creating a custom function for this? 

    Thanks for responding!

    Hi,

    I think the link I gave you uses SaveAsText, so you will be able to see how it works once you get a chance to look at it. If you still have any questions after seeing it, just let us know. Good luck!

    • Marked as answer by le_kai Tuesday, March 14, 2017 2:44 AM
    Sunday, March 12, 2017 1:42 PM
  • One reason I began setting form/control properties in VBA is because I was tired of Access overriding properties set in the property sheet after saving the form in various states. I never have this problem with .NET desktop applications.

    I was also challenged with over a hundred users in one big application wanting updates constantly, I'm able to make changes fast and without needing to force users to log out now and restart the application to receive an update.

    Another reason was data binding to controls. When the database schema changes when normalizing and abstracting out new tables, it is very difficult to search property sheets when you have 200 to 300 forms in an Access application. Especially when there are over a hundred tables.

    I'll admit I still have ALOT to learn. I do love developing software, and hope and intend to stay malleable as I find new and better solutions.
    • Marked as answer by le_kai Tuesday, March 14, 2017 2:44 AM
    Sunday, March 12, 2017 8:52 PM

All replies

  • Hi,

    If I understand your question correctly, you're wanting to codify the values from the Properties window for each object in your database.

    If so, the closest method I could think of is by using the undocumented SaveAsText and LoadFromText functions. Otherwise, you may have to develop a custom function to do it for you.

    Just my 2 cents...

    PS. This Export/Import utility might be of interest to you as well. Hope it helps...

    • Edited by .theDBguy Saturday, March 11, 2017 5:17 PM added link
    Saturday, March 11, 2017 4:04 PM
  • When you use word, or Excel or Access - you are “using” that large and complex application object. I suspect the applcatation is more than 10 million lines of code.

    So be it using .net, c++ or any development system – the object model you use is really never exposed to you as the original code – this advice applies quite much to any development tool one would use for development with a computer. So it not quite clear what you are asking, but the “methods” in Access are not VBA code, but simply use of built in objects and methods.

    So you have a “forms” object. To use that object, you create an instance of that object. You can do that by:

    Launching/opening a form like this in VBA


    Docmd.OpenForm "frmInvoice"

    Use a macro to open the SAME form like this:

    OpenForm (frmInvoice, Form,,,,Normal)

    Write VBA code to create an instance of that form object


    Dim myform   as form

    Set myForm = new form_frmInvoice

    myForm.Visible = True


    more VBA code…


    So note in the last example, we don’t use the “built in OpenForm” method, but in code create an instance of the forms object, set the visible property = true (to display the form). However at the end of the day, all 3 approaches results in the SAME THING: that is an instance of the forms object.

    So there is no “exposed” code when you use the forms object, and in all cases you simply creating an instance of that built in object.

    So anytime you use a 3rd party development tool, you not going to see/use/view the actual code of the object, but simply interface with that object in question.

    Some older systems that were called 4th GL systems would in fact generate code for thing like forms. (FoxPro comes to mind). So today we more often see that in place of code, we store only the “values” and say x,y numbers of the location of a control on the screen – so code is not generated to create the form, but only a set of values that makes up the form and then is rendered by simply “reading” those values. And any of these “values” are exposed if you look at say the properties for a given text box. You can use the property sheet to set the width of a text box, or you can see/view/modify that value in VBA code.

    However, in all of the above 3 cases, you simply using a built in object. There is no “code” generation going on behind the scenes. This is really quite much how all development systems work, unless you going to dump Access, and say hand code layout and design of a form by hand. We “used” to do that about 30 years ago before all these wonderful tools like Access or anything else that includes a form designer that allows building of a form without having to write code.

    To be fair, Access for “layout” of the forms does in fact use a meta-data markup language (similar to HTML) behind the scenes. So some systems simple store x,y “values” for the position of a control on the screen, and some use markup languages like HTML (so lots of systems use either approach – but they are NON procedural in near all cases).

     You can “see” or look at that Meta data code by using the “little” documented saveas command as point out by dbGuy in this thread.

    However, as such this information is of little use. Any of the useful values for a given control are exposed to the property sheet, and ALSO available in VBA – so drilling down to the original HTML or internal “custom” Meta code in this case is of little or no value.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, March 11, 2017 5:52 PM
  • I believe le_kai is referring to something like how Visual Studio generates Form1.Designer.cs file and calls from the Form1.cs constructor the InitializeComponents() method.

    When you change properties in a VS Properties Sheet, it updates Form1.Deseigner.cs file.

    I wish Access did this too! To compensate, I create my own kind of InitializeComponents() method in my Access forms. But keep in mind, you can't set all properties in the Properties Sheet in Acccess from code during Open/Load events. And I've also had some issues when trying to position columns in a Datasheet via the GUI and then save them in that position if I have Open/Load event code setting particular values like the Datasheet Caption.
    Saturday, March 11, 2017 8:58 PM
  • If I understand your question correctly, you're wanting to codify the values from the Properties window for each object in your database.

    Yes, that's exactly what I'd like to do. Thanks for sharing the Import/Export Utility, I'll check it out. Do you have any more information on the the SaveAsText and LoadFromText functions, or how I might be able to go about creating a custom function for this? 

    Thanks for responding!

    Sunday, March 12, 2017 6:55 AM
  • Yes exactly, I'm interested viewing/editing all of my object properties in the VB editor, rather than switching back and forth between the VB editor and my object property sheet. I wasn't looking to view the entire source code behind the MS Access application object (as Albert Kallal was suggesting) or the program itself. 

    @HTHP, would you be able to share a bit more about the process/method you're using? 

    Thanks for responding!

    Sunday, March 12, 2017 7:02 AM
  • If I understand your question correctly, you're wanting to codify the values from the Properties window for each object in your database.

    Yes, that's exactly what I'd like to do. Thanks for sharing the Import/Export Utility, I'll check it out. Do you have any more information on the the SaveAsText and LoadFromText functions, or how I might be able to go about creating a custom function for this? 

    Thanks for responding!

    Hi,

    I think the link I gave you uses SaveAsText, so you will be able to see how it works once you get a chance to look at it. If you still have any questions after seeing it, just let us know. Good luck!

    • Marked as answer by le_kai Tuesday, March 14, 2017 2:44 AM
    Sunday, March 12, 2017 1:42 PM
  • My approach is simple really. I just avoid using the property sheet all together. Except for when I'm forced to because you can't set all properties via VBA on the Open/Load events. I create two methods in every form: ConfigureForm and ConfigureControls. I call these in the OpenEvent. This is where I set my properties.

    The added benefit for me is that I also have finer control over Enabled/Visible/Locked properties using a standard Users/Roles based design. Users login to databases with a User Name and Password, this is custom code that checks cloud server tables and caches connection strings and populates a UDC Users class with their Roles and credentials. I can set Grant/Deny permissions on remote server table, and each time a Form loads, it can also set the properties by looping over the controls and checking the server for it's values.

    Sorry for the tangent. Avoiding the property sheet is not always the most productive way to design in Access, but if I believe the project is going to need to scale up, Its much easier to just keep everything in code altogether.
    Sunday, March 12, 2017 5:40 PM
  • But I fail to see how this results in any benefits?

    When building say WinForms (desktop) applications in say .net, you never edit or bother with the actual Meta data that makes up that form. You can certainly “see” or “view” that Meta data that has things like the control def and position, but you are STRONGLY advised against editing and playing with that code – and you NEVER modify that Meta DESIGNER code anyway!

    I fail to see the advantage in looking for some type of imaginary development process that not existed in desktop developer for that last 30 years?

    The idea you “save” some developer time by having a property sheet on your screen while you coding and you looking to now somehow drill down into the Meta data that creates the form makes no sense. Is your monitor so small you can find a spot on the screen for the property sheet?

    We don’t do this in .net, or any other system that creates software for the desktop with the exception of some older 4th GL systems – and EVEN in those systems we NEVER modified the resulting form definition code since if you re-generate the form, then any changes you made are overwritten.

    However, if you have time to spare, then then while in Access, hit ctrl-g to jump to the debug window, and try exporting the forms underlying Meta code with this:

    saveastext acForm,"form6","c:\test\form6.txt

    Now go take a look at the text file.

    I fail to see anything here beyond wasting time and efforts with this approach. But do try the above, view the code and post back what use you found of this development approach.

    Unless you building modern software (usually web) based on some kind of markup language, then you find how VB6, .net, and MOST desktop development systems work – you find the same in Access.

    We simply don’t use this kind of development paradigm with this kind of software and have not for the vast majority and history of desktop development systems since the introduction of the original PC in 1981.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Sunday, March 12, 2017 7:17 PM
  • Does anyone know if there a way to convert everything on my front end UI to VBA, so that I can fully customize things using the VB editor?

    Hi le_kai,

    The values of the properties of the Access objects are mostly default or initial values. These can easily be changed in memory using VBA, to customize them the way you want.

    I use this technique to a far extent: my 90+ applications just use a few generalized forms tuned by metadata tables.

    Imb.

    Sunday, March 12, 2017 7:45 PM
  • One reason I began setting form/control properties in VBA is because I was tired of Access overriding properties set in the property sheet after saving the form in various states. I never have this problem with .NET desktop applications.

    I was also challenged with over a hundred users in one big application wanting updates constantly, I'm able to make changes fast and without needing to force users to log out now and restart the application to receive an update.

    Another reason was data binding to controls. When the database schema changes when normalizing and abstracting out new tables, it is very difficult to search property sheets when you have 200 to 300 forms in an Access application. Especially when there are over a hundred tables.

    I'll admit I still have ALOT to learn. I do love developing software, and hope and intend to stay malleable as I find new and better solutions.
    • Marked as answer by le_kai Tuesday, March 14, 2017 2:44 AM
    Sunday, March 12, 2017 8:52 PM
  • >without needing to force users to log out now and restart the application to receive an update.
    That approach will not work in .net, or just about any other development system.

    You have to work on a copy of the application. You develop, make changes, and test your changes. When you are happy with the results, then you deploy a new executable of your application.

    This assumes your application is split into an application part, and a data part. So just like word has the “application” part, and then the data/document part. The developers of word are thus working on a copy of the code for the next great version.

    The above same applies to Access. To issue a new executable to your users, then you simply deploy a NEW copy of your application to your users. So users (typically) should receive a whole new updated executable of your application. And it should be a compiled version of that application.

    So you in general practice will work on a copy of the front end (the code and forms part). You can (perhaps should) point that front end to a copy of the back end data also. You thus develop, test, and make changes.

    When you are ready to deploy, you re-link your front end to the production back end, then compile the accDB into an access executable (an accDE), and then deploy that updated compiled application to your users. Again I point out that this approach is really no different than say .net or any other desktop development approach.

    So it not clear why some “changes” are being attempted while users are running that application. As a general rule you work on a copy of the code while users work with a separate and compiled version of your code. You don’t need nor want to deploy the source code to your users.

    So it is assumed that for development the application is split into two parts:

    The application part (code, forms etc.)
    The data part (the back end database – it could be an accDB file, or SQL server or whatever).

    So the “concept” of the program part and the data part very much applies to Access development. It not practical (or even possible) to work on the production application while it is in use. So each user should receive their OWN copy of the application. This approach thus allows you to freely work on the source code, and when you are ready for deployment, then you provide and deploy an updated copy of that application to each workstation.

    So your resulting compiled Access executable needs to be deployed to each workstation. We don't have a click to run  system built into Access, but even in the case of .net click to run, each workstation ultimately receives a full new copy of that updated application. The same approach applies to Access.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Sunday, March 12, 2017 9:13 PM