Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Solved
Determine current project path Ms Access
Posted on 2011-03-08
Hi,
I have been using the following code for some time to allow me to transfer data to an upgraded version of the database. See code snippet 1 below.
Basically it determines the existing Desktop file path.
I now want to change it to all me to transfer the data to a DB in the same folder as the open DB. I have the following code that seems to work, I just need to know if there are any issues I should consider that I have not in the code below.
Do I really need the following part.....
If Dir$(sUpgradePath, vbDirectory) = "" Then
sUpgradePath = Environ("UserProfile") & "\Desktop\" 'For use with Vista/Win7
End If
Is there any possibility that Access just would not be able to work out the directory path ?
Any advice would be greatly appreciated.
Kev
I have been using the following code for some time to allow me to transfer data to an upgraded version of the database. See code snippet 1 below.
Basically it determines the existing Desktop file path.
I now want to change it to all me to transfer the data to a DB in the same folder as the open DB. I have the following code that seems to work, I just need to know if there are any issues I should consider that I have not in the code below.
Do I really need the following part.....
If Dir$(sUpgradePath, vbDirectory) = "" Then
sUpgradePath = Environ("UserProfile") & "\Desktop\" 'For use with Vista/Win7
End If
Is there any possibility that Access just would not be able to work out the directory path ?
Any advice would be greatly appreciated.
Kev
'********************code snippet 1 ****************************
On Error GoTo Err_Handler
Dim RS As DAO.Recordset, sSql As String, strQuestion As String, sUpgradePath As String
DoCmd.SetWarnings False
'Determine user Desktop path
sUpgradePath = "c:\Desktop\" 'For use with WinXP on DRN
If Dir$(sUpgradePath, vbDirectory) = "" Then
sUpgradePath = Environ("UserProfile") & "\Desktop\" 'For use with Vista/Win7
End If
sUpgradePath = sUpgradePath & DLookup("SysUpgradeTitle", "systblVersion", "")
'************************* code snippet 2 **************************************
'Determine current path
sUpgradePath = CurrentProject.path
'Check for terminating backslash sUpgradePath filepath.
If Right(sUpgradePath, 1) <> "\" Then
sUpgradePath = sUpgradePath & "\"
End If
If Dir$(sUpgradePath, vbDirectory) = "" Then
sUpgradePath = Environ("UserProfile") & "\Desktop\" 'For use with Vista/Win7
End If
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2 Comments
Hi,
I just realized that I should reorder the sequence as follows. Any other suggestions ???
Kev
I just realized that I should reorder the sequence as follows. Any other suggestions ???
Kev
'Determine current path
sUpgradePath = CurrentProject.path
If Dir$(sUpgradePath, vbDirectory) = "" Then
sUpgradePath = Environ("UserProfile") & "\Desktop\"
End If
'Check for terminating backslash sUpgradePath filepath.
If Right(sUpgradePath, 1) <> "\" Then
sUpgradePath = sUpgradePath & "\"
End If
sUpgradePath = sUpgradePath & DLookup("SysUpgradeTitle", "systblVersion", "")
Using Environ() will work in some situations and not in others. It depends how the network / login scrips have been set up and also on the Office security settings.
My suggestion is to use the API that retunrs those special folders.
Using the attached code the desktop folder is returned by
GetSpecialFolder(&H10)
My suggestion is to use the API that retunrs those special folders.
Using the attached code the desktop folder is returned by
GetSpecialFolder(&H10)
'
' Declares for Shell Items
'
Private Type SHITEMID
cb As Long
abID As Byte
End Type
'
Private Type ITEMIDLIST
MKID As SHITEMID
End Type
'
' Enumerations of Folders - useful for providing access
' to the GetSpecialFolder Path routine
'
Public Enum fldrIDs
fldrPrograms = &H2
fldrControls = &H3
fldrPrinters = &H4
fldrPersonal = &H5
fldrFavorites = &H6
fldrStartup = &H7
fldrRecent = &H8
fldrSendTo = &H9
fldrBitBucket = &HA
fldrStartMenu = &HB
fldrDeskTopDir = &H10
fldrDrives = &H11
fldrNetWork = &H12
fldrNetHood = &H13
fldrFonts = &H14
fldrTemplates = &H15
fldrCommonStartMenu = &H16
fldrCommonPrograms = &H17
fldrCommonStartup = &H18
fldrCommonDeskTopDir = &H19
fldrCommonAppData = &H1A
End Enum
'
' API Declares
'
Private Declare Function SHGetSpecialFolderLocation _
Lib "shell32.dll" (ByVal hwndOwner As Long, _
ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
Public Function GetSpecialFolder(ByVal CsIdl As fldrIDs) As String
'
' This routine returns the fully qualified path to the
' required folder.
'
' Obviously this can change depending on the logged in
' user
'
Dim lRet As Long
Dim sPath As String
Dim sTemp As String
Dim IDL As ITEMIDLIST
'
On Error GoTo vbErrorHandler
'
' Get the location into our ITEMIDLIST
'
lRet = SHGetSpecialFolderLocation(0, CsIdl, IDL)
'
If lRet = 0 Then ' no error !
'
' Build a buffer string to handle the returned value
'
sPath = Space$(255)
lRet = SHGetPathFromIDList(ByVal IDL.MKID.cb, ByVal sPath)
If lRet <> 0 Then
'
' Make sure we append a '\' to the path
'
sTemp = TrimNull(sPath)
sTemp = sTemp & IIf(right$(sTemp, 1) = "\", "", "\")
GetSpecialFolder = sTemp
End If
End If
'
'
Exit Function
'
' Something strange happened - raise an error !
'
vbErrorHandler:
Err.Raise Err.Number, "cSpecialFolders::GetSpecialFolder", Err.Description
'
End Function
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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
Course of the Month7 days, 1 hour left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.