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

[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.

 

[Pat Hartman]

Over the years, I have found that hard coding paths is always a "future" problem so I avoid it like the plague. And #2, the old style exports are ever so much easier to modify than the export you are using. Especially since they don't store the path, the path is developed in code. Either hardcoded (problem) or calculated (much more flexible). Depending on the way the path is used, I determine where to store it because i might need multiple paths. Frequently, it comes down to writing code that has the ability to create a folder in a "path" with the ID of a specific client. Or sometimes you want different paths for different types of documents.

 

[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.

 

[Pat Hartman]

If you had recreated the exports using the old method, you would have rid yourself of the problem permanently since the old method doesn't store the file name in the spec

 

[bcmarshall]

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

 

[Pat Hartman]

You have to do the import or export one more time manually to set it up. Here's pictures and the code to use the export or import spec. They are interchangeable BTW. If you import and export the same structure, you can use ONE spec for both. All the spec has is the record layout and the description of the delimiters and some formatting of dates.

Option Compare Database
Option Explicit

Private Sub cmdExport_Click()
Dim strFullName As String
Dim strPathName As String
Dim strFileName As String

    strPathName = DLookup("PathName", "tblPath")
    strFileName = "Invoice_" & Me.CustNum & "_" & Format(Date, "yyyymmdd") & ".csv"
    strFullName = strPathName & "\" & strFileName
    DoCmd.TransferText acExportDelim, "Subscriber_Spec", "qSelectInvoice", strFullName, True
End Sub

I just made up some sample code to format the file name. Use whatever makes sense to you. Usually, each import/export has some standard part like "invoice". Then the variables are custNum and date. The spec in this case is named "Subscriber_Spec". You can use the same spec for both import and export if you import/export the same file type. So, with this example, the only thing hardcoded is the unchanging parts of the file name "Invoice_" and ".csv" Everything else either comes from a lookup table, the form you are working with or a VBA function.

 

[Pat Hartman]

 

[bcmarshall]

Damn, that's awesome!

Thanks so much!

 

[Pat Hartman]

You're welcome. I think MS added the "new" method (which isn't even new anymore) because people couldn't figure out how to use the "Advanced" button.

If the record layout changes, you would go through the process again. Import or Export a file, using the advanced button, pick the spec you want to work with. Change the spec as needed. Save it with the same name or a different name.

 

[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

 

[Pat Hartman]

Am I reading this correctly? You are removing all the rows from the spec that define what the import file columns looks like? I'm surprised that works but if you say it does, OK then. It does seem to defeat the purpose of actually making a spec to begin with since the spec is used to insure that the data types of the flat file are properly defined and that is the information you are removing.

 

[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?

 

[Pat Hartman]

the explicit indication of an export specification was NOT necessary

How can I argue with that? You are so certain that you know more than Access. I'll leave it to you to figure out why one would want to use an actual spec