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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.