Microsoft, Access, 2007, VBA programming - system TEMP variable

Posted on 2008-11-05
Last Modified: 2013-11-28
I'm looking for a way to program my Access 2007 module to use the TEMP or TMP system variable for a location to save an Access generated report.  I currently have it hard coded to use c:\temp but have found some users do not have a c:\temp or will not have access to c:\temp if they use a machine where they do not have local administrative priveleges.  The TEMP and TMP variables should always be their own temp folder under Documents and Settings?  Can I do this in Access?  Is so, what is the syntax for the variable?


Here's my code...

    strFileName = "c:\temp\ToolingRequest.rtf"
    tempNo = 1000 + DCount("RecordNum", "ToolingTable", "Revision=1")
    strDocName = "ToolingReport"
    strWhere = "RecordNum = " & tempNo
    DoCmd.OpenReport strDocName, acViewPreview, "", strWhere, , "entry"
    DoCmd.OutputTo acOutputReport, strDocName, acFormatRTF, strFileName, False
Question by:greenprg
    LVL 11

    Accepted Solution

    I've found the most reliable way to get special folders like 'My Documents' etc. is to use the SHGetSpecialFolderPath API function.

    Put the code snippet below at the top of your module and use it to set your string with: strFileName = GetDocumentFolder

    'Function to get windows special folder names, based on their CSIDL identifier'
    Private Declare Function SHGetSpecialFolderPath Lib "shell32.dll" _
        Alias "SHGetSpecialFolderPathA" (ByVal hWnd As Long, _
        ByVal lpszPath As String, ByVal nFolder As Integer, _
        ByVal fCreate As Boolean) As Boolean
    Function GetDocumentFolder()
    Dim bRet As Long
    Dim sBuff As String
       sBuff = Space(255)
       '&H5 = documents folder CSIDL'
       bRet = SHGetSpecialFolderPath(0, sBuff, &H5, False)
       sBuff = Left(sBuff, InStr(sBuff, Chr(0)) - 1)
       GetDocumentFolder = sBuff & "\" '
    End Function

    Open in new window

    LVL 11

    Expert Comment

    Line 14 doesn't need the final backslash. Just use this:
       GetDocumentFolder = sBuff

    Author Closing Comment

    I only understand that this code is WAY beyond my comprehension at this time.  Maybe I'll understand it next year?

    Author Comment

    WOW!  Thanks for the code.  It works even though I can only understand about 40% of the code.  Very cool!  Maybe after I've programmed for another year I'll be able to decipher the code.  Thanks for the quick response.

    LVL 11

    Expert Comment

    You're welcome - and good luck learning coding... it has its ups and downs. :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now