Modufy Modules with VBA Code

Question

I use VBA to read my tables, then capture all then field names, such as

Table Client

field ID              position (0)

field Name         position (1)

Field Address     Position (2)

and so on

I write a TEXT file with the following

Public Const  Client_ID = 0

Public Const  Client_Name = 1

Public Const  Client_Address = 2

     and so on

I then Copy/Past the  TEXT file to  a module called  "TableConstants"

This insures that my variables are all ways  Defined, consistent and correct.

If I reposition a filed in my tables, and regenerate my  TEXT file, my VBA Codes is not be affected.

How can I delete the "TableConstants" and insert the changed TEXT  in its place, using VBA Code.

Answer 1

Hi,

If you're trying to dynamically modify a module, check out the DeleteLines and InsertLines methods.

Hope it helps...

Answer 2

How can I delete the "TableConstants" and insert the changed TEXT  in its place, using VBA Code.

Hi Pasquale,

I have been puzzling quite a time on your question. I hope you don't intend to do this in the production version of your application. It would give some flickering of opening and closing modules, adds to bloating of the front end.

I would be more in favour of doing this in your development version, and then only from time to time. And in that case it would be easier with a copy/paste the replace the contents of the TableConstants module.

Next point is, where do you want it for? You want a translation between the Fieldname and the Fieldposition in the table? Working with tables is in my opinion working with sets, and assigning something like:   my_set("Client_ID") or my_set!Client_ID will automatically pick the right fieldposition, even after repositioning of the fields. So in my opinion this translation is just redundant, and a translation from Fieldname to Fieldposition or vice versa is not interesting.

Another point is when you want to use the data in forms, where the data-elements are displayed in Controls. In making dynamical forms it becomes important to make a relation between the Fieldname and the control. In standard Access you define this with the ControlSource. When you want to work dynamically, it is better to define a meta data table where you can make this translation form Control to Fieldname, which is much easier to maintain than code in modules.

I went quite a way in that direction, but perhaps I overlooked a few aspects of your requirements.

Imb.

Answer 3

Holy hypertext Batman! That's a awesome link DbGuy! I never knew you could do that to Modules. Its unfortunate my production files are all ACCDE. It could allow for some pretty dynamic runtime code generation.

Answer 4

Holy hypertext Batman! That's a awesome link DbGuy! I never knew you could do that to Modules. Its unfortunate my production files are all ACCDE. It could allow for some pretty dynamic runtime code generation.

Hi HTHP,

I am afraid you cannot use this technique in ACCDE's, because you must open the module in the Visual Basic Editor.

But in Development mode it is very handy. When constructing a new application, or extending an existing one, I can select from any application that i run, which fields form any table I can use in a new table (or existing one). After confirmation all the respective data relating to these fields in the metadata-table are copied to the metadata-table in the new application, including all routines coupled to these metadata. From then the new application is fully functional.

This has proven to be a very powerful tool.

Imb.