Find and Replace in Macro and VB

[Baldeagle]

I have developed 2 databases for 2 charities and continue to be responsible for making changes to them from time to time. Included in a macro for handling Turn of Year activities are a large number of Transfer Database instructions which have a path to 'C:\' etc. Also in various Visual Basic procedures in the Databases there are links to documents and again the path is to 'C:\' etc.


I have just got a new PC and it is structured with the C drive being for Programme files and the D drive for Data files. My Databases are used on other PCs which still have the C drive as the place where data files are stored. Is there any simple way that I could create a button which I could click when transferring the Databases between my PC and the users' PCs which would change all references (both in my macros and in Visual Basic) from C to D and vice versa? If so could you give me a steer on how to go about this?

[ItsMe]

There are third party apps that will help you to analyze the structure of your app. Some of them have search and replace methods, etc. You could decompile your VBA and edit it that way, but pasting code into existing modules has its drawbacks.

Why not use "CurrentPath" within your code? This way you can place associated files in subfolders of the current path. There would not be a question where the files are. If this is a multiuser DB and the files are shared, then don't use mapped drives and use the Universal Naming Convention (UNC) for all of your paths.

[Baldeagle]

There are third party apps that will help you to analyze the structure of your app. Some of them have search and replace methods, etc. You could decompile your VBA and edit it that way, but pasting code into existing modules has its drawbacks.

Why not use "CurrentPath" within your code? This way you can place associated files in subfolders of the current path. There would not be a question where the files are. If this is a multiuser DB and the files are shared, then don't use mapped drives and use the Universal Naming Convention (UNC) for all of your paths.

Thanks for your response. Could you please throw a little more light on your comment about 'CurrentPath'. This is very new to me. How would I go about using your suggestion in my Macros & VB? Instead of making any changes in the Database would it simply be a question of putting my relevant data files into a folder on the C drive to match the references that are already in my Database?

[ItsMe]

Currentpath will return the path the current db resides in. Here is an example of it being used.

https://www.accessforums.net/program...tml#post144267

[Baldeagle]

Thanks. I think I'll stick with using my C drive and avoid any complications. I appreciate your input.