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

Posted on 2008-11-05
Medium Priority
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
  • 3
  • 2
LVL 11

Accepted Solution

Antagony1960 earned 2000 total points
ID: 22891812
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

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

Author Closing Comment

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

Author Comment

ID: 22891920
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

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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