Searching for a way to get Access to retrieve and use data from a hard drive file not within Access 3

[TheresAlwaysAWay]

I have a 20+ user Access DB with SQL Server 2016 back end for use in a large insurance agency. We have what we call ShortApps, which are Word documents created with Mail Merge to be populated with info from the Access customer record and which are ultimately attached to an email and sent to the customer.

Which application is sent is determined by a concatenated field that consists of <Insurance Company Name> + <Vehicle Type> + <Policy Loss Settlement>, perhaps "Progressive Travel Trailer Total Loss Replacement", and that name, based on customer record data, along with a .docm suffix, is the file name that is ultimately identified, populated, and attached to the document.

The Word documents are infrequently updated, sometimes remaining untouched for months, and when a change is made a notice is sent to all users to update the entire ShortApps folder from the public drive to their local C: drive. The matching and attaching happens on the local drive. The public drive just holds the master copies for users to download when a change is made.

The issue is that we can send a notice to users to update, but human beings often intend to do something "later" and never do. I need a way to check to see if the local C: drive copy version matches the latest public drive version when the command to print and email a document is given.

I'm always open to better ideas, but I was thinking of a file with a name "Version", or something similar, being included in the same master folder with all the applications. It could be a Word or text or any other document format. I suppose theoretically it could also be an accdb. document as well. The ultimate goal is to get the print/send command to first look up the date info in the public Version document, and then compare it to the info in the C: drive Version document. If they're the same (in other words, user has the most current version), then command proceeds normally. If they don't match (meaning the user is not on the current version), a message pops requiring user to update ShortApps before continuing to print/send.

For clarification, my expectation is that the person who updates any of the master copies will manually change the version information, perhaps just to the current date. I don't think much more than that is needed.

I can get Access to seek out a given file on the C: drive, such as the actual application, but I don't know how to get it to read information contained within that file and use it for some sort of data comparison.

Thank you all in advance. I'm always appreciative of any offers of help, and will be happy to answer any questions if I haven't made my needs fully understandable.

 

[Andrzejek]

.docm suffix indicates it is a Word macro-enabled document, right?

What I do in my application is – I have Word Templates (.dotx) that are located on the Server available to my application and any time user is using my application and wants to create a Word document based on any Template using data from my DB, my app uses the Template from the server and creates ‘regular’ .docx Word document. Users do not know where the Templates are located, nor do they care. In same instances I do create a Word and a PDF versions of the same documents since users want to e-mail the PDFs to their customers. My app saves these docx and pdfs in the pre-determined location on the Server where user have access.

When I need to modify / update my Templates, I just replace them on the server, making sure my new version of my app is also modified / updated to use the new Word Templates. No need to copy, save, check if all is up-to-date, etc.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Given you have a folder with the correct master documents in it, I don't understand why you wouldn't just open the document from the master repository. Andy describes a suitable idea using templates in the master folder.

That being said, files have a Date Modified property - so you could compare the date modified of the local file against the date modified of the master file, and then copy down the master file if it is newer. No real need for any manual maintenance

The code could be as simple as:

[COLOR=blue][COLOR=green]' Keeps strLocalFile in synch with strMasterFile[/color]
Public Function MostRecent(strLocalFile As String, strMasterFile As String)
   
    With New FileSystemObject
        If .GetFile(strMasterFile).DateLastModified >= .GetFile(strLocalFile).DateLastModified Then
            .CopyFile strMasterFile, strLocalFile, True
        End If
    End With
    
End Function[/color]

 

[TheresAlwaysAWay]

Thanks, Andy. Yes, .docm are macro enabled documents.

Your approach sounds very similar to my earlier approach of directing everyone to the public drive copies. Of course, no update would be necessary then but problems arose when two people simultaneously tried to write to the same document. That's the reason for the local C: copy.

 

[TheresAlwaysAWay]

Strong, same reply. I tried it that way but just wound up with too many errors because of multiple users attempting to access the same file.

The C: solution has completely eliminated that issue. Each user is free to do whatever s/he wants to do without restriction.

Thanks for brainstorming with me, but that's the reason I've had to abandon using the master documents.

As I explained in my opening lines, my thought is that any time a master document is changed the version number is manually updated. Then there is an indication within the master file group of which version the entire group is labeled as.

Would it be possible to create an .accdb file as the current version holder, and then link a table from it to my main database? I'd have to link both C: and public drive tables, actually, but once done it seems it would be a simple matter to compare data from each within my main Access database.

 

[Andrzejek]

problems arose when two people simultaneously tried to write to the same document.

Hence the use of dotx Template. Anybody, at any time, many people at the same time, can use the same Template without issues since they don't "write to the same document". They have their own copy of the Template which is always up-to-date.

Yes, .docm are macro enabled documents.

I would be very suspicious/worry to access/open any ‘macro-enabled’ file. You never know what code will be run when you open it, especially if you have your system set to run the macros without any checks.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

> I tried it that way but just wound up with too many errors because of multiple users attempting to access the same file.

It is more that they are all trying to edit the same file, not access it. As Andy explains above, this is the reason to use templates, so each person gets their own copy of the file.

 

[TheresAlwaysAWay]

Oooooh, thanks! I was unfamiliar with dotx document format. So do all users pick the document from the same master file group then?

Can you explain a little more about dotx? I've never used that before.

 

[TheresAlwaysAWay]

I did some research on dotx and found this link,

https://fileinfo.com/extension/dotx,

which seems to indicate that the format is useful a a template for creating multiple similar copies. So far, so good.

I need some guidance, though. What happens when two people open the same dotx file at the same time?

 

[Andrzejek]

about dotx? I've never used that before.

Incorrect.
You are using .dotx file any time you open new, empty Word file. You access [tt]Normal.dotx[/tt] template file. If you change any default settings in Word: Font, size, color, background, etc. you actually modify your local Normal.dotx template file.

In short .dotx is just a Template. You access it and do whatever you want to do with it, without modifying the 'actual' template file. More about it here

What happens when two people open the same dotx file at the same time?

Nothing.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

The only limitation of dotx file I have found is – if I want to modify my template, I can open it in Word, make changes, but I cannot save it (as a template file) in the same location under the same file name. Word does not allow me to replace my template. :-(
So, I save it as a different file name, close Word, and rename .dotx files in Windows Explorer.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

> I can open it in Word, make changes, but I cannot save it

Er ... works fine here ... obviously if you open the template via 'New' and select the template to base your document on then document you clearly can't save over it (since it is a live, active template). But if you open it via 'Open' it works exactly as expected (at least, as I say, it does here)

 

[Andrzejek]

Thanks strongm,
Good to know. I used to open my template just by double-clicking it from Windows Explorer and had this issue with saving it. But [tt]File -- Open[/tt] works just fine when I need to save it.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Thank you so much, gentlemen.

I know you guys get nothing for your efforts except the satisfaction of having helped someone else. The stars I gave you both are nice, but they are not the reason you do this. Instead it's because you're both decent, caring people who just enjoy sharing what you know and making someone else's life a little easier by sharing your knowledge with them.

I know you don't have to do this and I want you to know I sincerely appreciate it.