I know why this is happening and I know I need to modify my program to accomodate Windows 7/8.
Good, then I'll try not to beat a dead horse (although this is one I regularly like to give a good flogging because so many people misunderstand it). I'll only point out that the fact your program ever worked correctly, even on the older versions of Windows, was a fortunate consequence of a latent bug. The "Program Files" folder was never meant to be writeable. You're not just working around some silly "issue" of newer versions of Windows. This is something that should have been done right from the beginning.
I know appdata folder under "users" is the recommended place to store the databases/word files/backups. However, on my system that is hidden and I would like it to be easily accessible to the user (not hidden), so I was thinking MyDocuments folder?
I know this is complicated, so let me see if I can try to make it as simple as possible…
The "App Data" folders are for data intended for internal use by your application and not intended to be seen or manipulated directly by the user.
The "My Documents" folder is for everything that the user should be able to see and directly manipulate; in other words, user data instead of application data.
And then there also per-user and global variants of each of these two folder types. In the case of "App Data", the per-user variant is further subdivided into local (which stays on the local machine only) and roaming (which follows the user account to any machine on the network to which she logs on).
So if you want the files to be "easily accessible to the user", then you should definitely use the "My Documents" folder. That's what it's for—user documents.
The reason why all of these complicated rules exist is because misbehaved applications by such popular vendors as Microsoft and Adobe had (have?) a tendency to dump junk into the user's "My Documents" folder, stuff that the user was never intended to interact with directly. Users would then open their documents folder and see a bunch of stuff they never put there. "What are all these things? These are not mine. Where are my documents?" My mother just asked me this last week when I updated her PC.
it's not clear how to find MyDocuments. Can anyone help?
From VB 6? The easy way is the one suggested by Bob and Mark in the question you looked at. This involves creating a Shell object and querying it for the location of the folder(s) you're interested in.
The part that's missing is the constant that corresponds to the "My Documents" folder. You'll find a complete list of constants here. The one you want is named ssfPERSONAL (because in the old days when this API was created, Windows wasn't as friendly and the "My Documents" name hadn't been invented yet), which has the value &H05. So the code looks like:
Const ssfPERSONAL = &H05
Dim strMyDocsPath As String
strMyDocsPath = CreateObject("Shell.Application").NameSpace(ssfPERSONAL).Self.Path
If I wanted to use something like MyDocuments\CompanyName or even the Appdata folder mentioned in the link above, how do I tell the package/deployment wizard to install the mdb files and the folders mentioned above into this specific folder? Is there a macro for the Appdata or MyDocuments folders in the PDW that I can use?
There's not much that I can add to this that Bob didn't already cover in his answer, other than to recommend that while you're modernizing this VB application, you also consider ditching the antiquated PDW in favor of a better installer.
Personally, I'm a huge fan of Inno Setup, which is a free installer utility for Windows applications that offers more features than you can shake a stick at. It works very well with VB 6 applications. I've used it many times for that myself. I think it's fairly easy to use, at least for the basics. It supports all sorts of advanced customizations, and some of that can be tricky if you don't learn well by reading documentation, but it is all very doable. I've even stumbled my way through writing Delphi code to customize Inno Setup projects!
In Inno Setup, you access the paths to system folders using one of the pre-defined constants. For example, {userdocs} and {commondocs}. These work much like (if not exactly like, it's been a long time) macros in PDW.
Of course, this still doesn't fix the problem Bob rightly points out: the concept of a user's "My Documents" folder doesn't really make much sense to an installer. Certainly you can access the global (public) "My Documents" folder because it is shared among all users, but this is probably not the one you want. The installer should be able to be run by any user with sufficient privileges, and in fact this will often be an administrator account that differs from the regular user account used to run your application. You can't assume a particular user account during setup, and your design must account for this.
It sounds like to me that a "template" approach would be a good one for you. The complete set of files used to set up a new user account could be stored in the "Program Files" folder. This is actually a good place for it because it is well protected there from erroneous modifications, and since you're only going to be reading from it, you can be guaranteed to have the necessary permissions. When the application is launched for the first time under a new user account, it could detect this fact and offer to set up the new user account for use with the application. At this point, you know that you're running under the correct user account, so you can use the above code to query the location of that user's "My Documents" folder. With the desired folder structure all set up in a subdirectory of your application's folder in "Program Files", setting up a new user account could be as simple as copying over that folder. If you need to gather information from the user or offer customization options, you could write a little "First Run Wizard".
