Can I search all VBA in the database with one call rather than searching each form and each module?

[bcmarshall]

I have a long established database and the users all log into their Access copy on their own C:\ drives. The company is moving and is going to a 100% remote access format. There will be no "office" per se, and the logins will be established through hosted servers on a private cloud. One of the servers will be a terminal server. This allows multiple people to log into the same computer.

When fully implemented, each user's login will be seen as a login to the U:\ drive rather than current C:\, but other than that nothing really changes. 99% of what Access does is by looking at SQL Server database and making internal references to other database objects. There are, however a few, that 1%,, which reference the main C:\drive, whether it's to temporarily hold a file for attachment to an email or other similar purposes.

Since we haven't made the transition to the new remote system yet, I found a very simple, and I think elegant solution to the problem of allowing current users to operate unimpeded, and yet prepare for the future at the same time.

I have a reference in VBA, for example to "C:\Data\BoatForm.pdf" I have changed that text in VBA code to read Left([Application].[CurrentProject].[Path], 3) & "Data\BoatForm.pdf"

Now I'm looking at the filepath that the user is using to get into Access, stripping the left three characters, and replacing the fixed reference of "C:\" with the lookup result. Now, the file path for the external reference will be dependent on the user's login and can change back and forth with impunity.

My problem is that, like most databases, I have form code and modules. I want to know if there's a way to search all code everywhere in the system at once, and hopefully replace "C:\" with Left([Application].[CurrentProject].[Path], 3) & "

Does anyone know a way this can be accomplished so the entire DB can be updated at once instead of onesy-twoseys?

As always thanks in advance for your help.

 

[Kha]

Open any Module and then open search dialog box and pick Current Project, this will let you search in all your project

 

[bcmarshall]

I just HATE it when the solution is so simple! I can do the replace from there and be done in two minutes!

Thank you sooooo much!

 

[Gasman]

You are more than welcome

 

[GPGeorge]

I just HATE it when the solution is so simple! I can do the replace from there and be done in two minutes!

Thank you sooooo much!

Make sure you have a solid backup before you change everything in a single operation, just in case....

 

[bcmarshall]

Thank you all for your help. I have a follow-up question that is related, but not identical to the first.

In my original post I stated this.

"I have a reference in VBA, for example to "C:\Data\BoatForm.pdf" I have changed that text in VBA code to read Left([Application].[CurrentProject].[Path], 3) & "Data\BoatForm.pdf""

I have the same issue with Saved Exports. I have many references to the C:\Data folder in those exports and I wanted to make the same edit...allowing the system to decide which folder to use, either the C:\ or the U:\. However, I can't seem to find a way to edit the filepath in the Export.

Does anyone know how to accomplish this without recreating every export routine?

 

[Gasman]

I believe @isladogs has code to amend specs.
Search here for it, or isladogs.co.uk

He will see this post as well, so might well chip in.

 

[isladogs]

I believe @isladogs has code to amend specs.
Search here for it, or isladogs.co.uk

He will see this post as well, so might well chip in.

Here it is:

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

www.isladogs.co.uk

 

[bcmarshall]

Well, that was an adventure!

I used the wonderful examples provided, but ultimately because of the expression I used in the filepath I found it unworkable. In the end I decided to just rewrite the 20 or so Saved Exports to VBA. I spent much more time trying to get it to work than if I had just rewritten it to begin with, but that's part of the game too, isn't it?

I truly appreciate all the thoughtful advice offered. Ultimately I just had to make my own path, but I do thank you all. It's a rare thing to find people willing to help others for nothing but a kind word.

 

[bcmarshall]

What is the "old method" that you're referring to?

 

[bcmarshall]

Damn, that's awesome!

Thanks so much!

 

[amorosik]

Regarding the function
DoCmd.TransferText acExportDelim
I would like to add information that I believe will be useful to many who use it
With TransferText there is the big problem that if the table/query you want to export doesn't have its own 'export specification', it won't work properly

I ran into this problem in the past and wanting to create a generalized procedure that could export data from any table/query, I followed this method:
1- I created a specific export calling it 'universal_specification'
2- I deleted from the MsImexColumns table the rows connected to my 'universal_specification'
3- I used the DoCmd.TransferText acExportDelim activating the HasFieldName flag

In this way the text file is correctly exported, it is visible using both a text editor and opening it with Excel
And above all, in the case of exporting data from complete tables, it can be re-imported to recreate exactly the state of the information contained in the table at the time of export
Personally I often use this method to save records that are deleted, so the operator has additional security (in addition to periodic backups) in case you want to restore the data, and above all the deletions are perfectly traceable and therefore it is easy to go back to who/what was done
Obviously we are talking about tables with a fairly simple structure, in the sense that any blob fields or fields containing special characters could create problems during re-import, these are things to check with the data you want to work with

 

[amorosik]

Yes, you read correctly
And it's not hard to believe, just think that to use the same DoCmd.TransferText acExportDelim command on Access versions of a few years ago, the explicit indication of an export specification was NOT necessary, the export was completed correctly using the default settings
And so the real question to ask would be: why is the export specification necessary now?

 

[amorosik]

Actually I didn't ask for anything, I provided code based advice for Access which I have experienced to work correctly and saves a lot of manual work, probably useless
Hoping that my experience can be useful to whoever asked the question
If you believe that defining an export specification is essential, then please let the original questioner know.
Since it's already working properly on my code, I don't care

 

[amorosik]

You opened a dormant thread to add a comment that indicates that you think you know more than Access

No
I simply described one thing on a specific topic, which in my experience has proven to work well
The user who thinks of using it will obviously have to test it on his own
For my part, I added something that maybe the user who started the thread didn't know
If you think that the system I have proposed is not good, ok continue your reasons why you write that it is not good
@bcmarshall will be able to read and will have more information with which he will make his choice

"..not guarantee to not change any of the MSys tables without notice..."
Even on the correct functioning of Access, I mean the complete environment of Access, Microsoft does not guarantee anything
Same thing for the Windows operating system
And then what do we do? Are we all standing still because mom Microsoft won't give us the guarantee?

"..The OP was very happy with my solution..."
I'm sure, with two solutions instead of one, he'll be even happier