Results 1 to 8 of 8
  1. #1
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15

    Path reference so can use my accdb on any computer and any drive name

    I searched all posts and could not find this... even though it must be a common issue.



    Developed Access accdb at home and need have someone else install it on a machine at work... and drive names are different. Plus in a couple of months we will put it on drive F: on our in-house network.
    SO I do not want to change the code every time this is put on a different machine.

    QUESTION: Is there a way to reference the folders for importing and exporting so they would work on any computer and any drive name?

    I have the Access accdb file in a folder with 2 subfolders: a) xlsx files to import and b) pdf files of reports created by the Access accdb

    Access accdb: ...\QB PURCHASING ACCDB\ file name
    Files to Import: ...\QB PURCHASING ACCDB\XLSX FROM QB\ file name
    PDF files of reports ...\QB PURCHASING ACCDB\PDF REPORTS\file name


    I am using VBA code to import the
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB ITEMS SOLD", "C:\QB PURCHASING ACCDB\XLSX FROM QB\EXPORT TO MS ACCESS Sales by Item.xlsx", True, "Sheet1!"



    I am using Access 2013's Macro drop down choices to save the pdf- I can change the "Output File". Following is what displays on screen when I edit the Macro:

    EXPORT WITH FORMATTING
    Object Type: Report
    Object Name: TEST REPORT
    Output Format: PDF Format (*.pdf)
    Output File: C:\QB PURCHASING ACCDB\PDF REPORTS\test.pdf
    Auto Start: YES
    Template:
    Encoding: 0
    Quality Output: PRINT


    Thanks,

    Jim

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I understand the problem, I think the CurrentProject.Path property would work since your associated folders are relative to the folder that contains the db. Therefore, it should not matter what the drive letter is - just populate a variable and append the subfolder paths to it as needed. You can make the variable project-wide in scope or call it from any module when needed. If you are faced with UNC paths some of the time and sometimes not, you'll have to use string functions to repace the \\CompanyServer (fake name) with a drive letter.
    Actually, to deal with this on a corporate basis, I removed the drive letter and replaced it with the UNC path reference because not everyone had the same native drive letter on the network.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    Micron: I am not clear on use of CurrentProject.Path . In my above examples: for the pdf file to be saved (Access 2013 built in macro commands) , I replaced "C:" with "CurrentProject.Path" (no ") and did not find the path.

    When and where do I use the CurrentProject.Path-- and does it only work in VBA or also in Access 2013 built in macro drop down?

    This is for a 15 user network, so not the problems you may have in Boise.

    Thanks

    JP

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Following is what displays on screen when I edit the Macro:
    I think you will have a difficult time if you are trying to do this using macros. Can't say for sure because I never use macros. They are too limiting. (there is a wizard to convert macros to VBA code)

    You have the output path and file name hard coded - using VBA it is easy to change paths and file names. But I don't know if a macro can edit/change a macro.

    When I have a file to import, I have code to pick the folder/file name. To export, I have a folder picker. But it is all in VBA code.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Path is a property of your db (CurrentProject). If it resides in D:\JoesDatabases\PurchasingDB, then that is the path property. Since your folders will always (I presume) be relative to the folder that contains the db, your Excel folder would be D:\JoesDatabases\PurchasingDB\XLSX FROM QB; the reports in D:\JoesDatabases\PurchasingDB\PDF REPORTS. The only part you'd have to add to the path for any file in that folder, or any sub folder, is the ending backslash and the rest of the path.

    Either assign the path property to a global variable on startup (appPath = CurrentProject.Path), assign it to a hidden form textbox, dump it in a parameters table (tblParams) or get it every time you need it. You can't use an environment variable like this in sql or control sources, but you can do a DLookup on a table or reference a form control (Forms!frmHidden.appPath).
    If you're using macros, I missed that. I thought you were referring to a combo box. Weird, I know, but the more you post here, the more strange ways you read that people have for labelling things.
    Last edited by Micron; 02-02-2016 at 07:23 PM. Reason: macros
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ALT+F11 will open the VB editor. Insert a new module, paste in
    Public Function getAppPath() as string
    getAppPath = CurrentProject.Path
    end function
    save as mdlDbFunctions. You should see Option Explicit at the top of the module. You can get this by calling the function from a form textbox (=getAppPath) or in a query. With a query, you can dump it in a table, or get it in code other ways.
    If you're a macro guy, vb has a learning curve but there's mega examples out there for anything you'd want to learn, and it's far more powerful and useful than macros.

  7. #7
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    Followed Steve's advice and converted the pdf macro to vba then used Micron's path suggestions... Works perfectly.

    Thanks to you both,

    JP

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad to see that! Good luck with the rest of your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select File from Computer and Save to Shared Drive
    By sgrimmer in forum Import/Export Data
    Replies: 4
    Last Post: 11-02-2015, 03:43 PM
  2. Shared drive path to save attachments
    By zaffar_mughal in forum Access
    Replies: 3
    Last Post: 08-27-2014, 10:43 PM
  3. Replies: 3
    Last Post: 05-24-2014, 02:41 AM
  4. Replies: 3
    Last Post: 11-24-2012, 08:56 AM
  5. Replies: 10
    Last Post: 11-09-2010, 03:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums