Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014

    Unanswered: Prompt for user to update paths to linked (Excel) tables

    Hi, I am looking for a Macro/VBA code to implement in Access to accomplish a task in my database... I am hoping this is possible as it's a pretty simple task, but I'm not sure.

    I have a database that will be shared with many people. The tables are linked to Excel files, which are updated hourly via an automatic reporting procedure. The Excel files are saved in a similar location on each user's machine (same folder name and path, for the most part), but the path name will need to be changed for each user to reflect their Windows user name.

    So for example, when I look at my linked table manager, it shows all the tables have the following path: C:\Users\DataQueen\FolderName\FileName.xls

    I want to have a prompt come up with the database is opened, so that each user has to enter their user name (i.e. I would enter "DataQueen"), and then the code would update all the paths for the linked tables based on the input, since everything would be the same across machines except the name of the local user.

    Is this possible, and could someone help me out with the code for it? Thank you

  2. #2
    Join Date
    Apr 2004
    metro Detroit
    Do a google search for ms access vba link tables. There's quit a bit of code floating around for this. You may also want to look into Environ("UserName") rather than having the user type in their name and risk typos. Post back if you run into problems.

  3. #3
    Join Date
    Apr 2014
    Thanks for the feedback. I have been searching for days though, and can't seem to get anything to work-- I don't know if I'm implementing codes incorrectly, or they are just not quite what I need and I don't have the skill set to make small changes to VBA code without ruining the entire thing. I even tried to Environ("username") as you suggested, but I cannot get any sort of code to work around this either.

    The closest I have come to figuring this out has been just reading up on aspects of Windows paths in general, and found that if you place "%username%" in the path, that can be used as a substitute for "current user" essentially. So if, when I open the Linked Table manager, I don't use the point-and-click navigation to find the file but instead type in the path as:

    It works, but it stores the path with the username as "DataQueen," not as the placeholder "%username%", which I was hoping it would retain so that it would work across different users, so long as the folder and file was positioned in the same place on their home directory.

    If anyone has a suggestion for things to try, I am open to them. I have tried many of the VBA codes found via Google search results, but nothing has worked...

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you can get the current network logon using the api call (google dev asih api)
    but a pobelm I cna see is if your user computer has directories set up differently to the 'standard', which is more common that you might first think

    you should be able to get the file paths from the environ function. note you cna aso get a username from environ, but dont' be temted to use it as its all to easy to be spoofed. the user paths though should be less of an issue as opposed to trying to pickup a userid
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts