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

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
Who is Participating?
Antagony1960Connect With a Mentor Commented:
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

Line 14 doesn't need the final backslash. Just use this:
   GetDocumentFolder = sBuff
greenprgAuthor Commented:
I only understand that this code is WAY beyond my comprehension at this time.  Maybe I'll understand it next year?
greenprgAuthor Commented:
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.

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.