Saving VBA Module through code (1 Viewer)

[Insane_ai]

:banghead:

I have inherited an Access 2000 database that has a routine to change the ODBC connection from the live tables to development copies.

When triggering this, the user is prompted to save the changes to the module. I have not been able to find a solution to save this change through code with online searches, possibly because I don't know how to work the question.

Here's a sample of the code:

Access.VBE.ActiveVBProject.VBComponents("GlobalConstants").CodeModule.ReplaceLine 5, "Global Const dbConnectString = ""File Name=[I][my file path][/I]"""

The only change from one line to the other is the [my file path] part of the code to change the ODB connection string.

Is there a way to save this change without the user responding to a prompt?

 

[MarkK]

I would not modify the VBA. I would save the path as data somewhere, and expose that global as a variable that gets the path from wherever it is stored.

Consider code like...

private m_cstr as string

Global Property Get ConnectString as String
   if m_cstr = "" then m_cstr = DLookup("Value", "tSettings", "Name = 'ConnectString'")
   ConnectString = m_cstr
End Property

See how that looks up the connection string from a table where it is stored? This enables you to publish your VBA without it needing to be edited. This is the approach I recommend, rather than having to edit your code if file paths change.
Hope this helps,
Mark

 

[jleach]

I highly agree with Mark. Changing code from code should only really be done as part of development or build procedures, and not something for runtime/once deployed.

You can easily refactor it the way he suggested, which is a good pattern to follow in general anyway.

That said, if you're dead set on doing it by saving the code module, take a look here: https://www.everythingaccess.com/tutorials.asp?ID=Undocumented-SysCmd-Functions

 

[The_Doc_Man]

Further note that changing code from code CAN'T work from an .ACCDE (or .MDE, if you had one).

 

[Cronk]

My comment, other than agreeing with the consensus, is changing the connection path of linked tables does not require saving of code modules. Presumably the code changing the table source contains the prompt to save the database and could be disabled.

 

[Insane_ai]

To all that helped:

Thank you! Part of what I have been tasked with includes upgrading from V2000 to O365 and to use runtimes instead of native Access on the clients. I didn't think managing this with the current method would work in an ACCDE but was willing to be taught a new trick.


I will report back once I have results. I've got a week into this new position and an inherited to do list.



THANK YOU ALL!

 

[The_Doc_Man]

Oh, MAN, I don't envy you. I became the Access "guru" (in strictly relative terms) at my site back around 2003 when the only other guy with Access experience took a new job and moved several states away. For him it was great. He got the new, higher paying job. I got an increase in duties, but that was not so great for a while. The "sink or swim" learning curve was a real bear.

I also inherited a "to do" list and such lists are INEVITABLY mixed bags of both wildly variant priority and wildly variant complexity. A survival skill in this situation is to learn the self-discipline to perform triage on the list. See what can be done most quickly. If you were up on queuing theory, there is a proof that you can get the greatest NUMBER of things done if you do the shortest jobs first. If you can get several to-do items knocked out quickly, you can gain credibility when you tell the boss that the remaining tasks are a bit harder and regardless of priority, will take longer due to complexity.