VBA: Saving a semi-permanent variable without hard wiring it

T

Travis

What is generally considered to be the best way to store variables like
a user's default save directory?

I can hard code it into the VBA easily enough, but then I've got to
recode when I install on a new machine.

I can open a file dialog box to get the user to input this information,
but then I've got to do it every time.

The solution of course is to get the user to input this data ONCE and
then save it somehow.

For an Access app, do people generally set up a table to hold this or
is there a better way? And what do I do if I should ever decide to
write something in VBA which isn't in Access?

Travis
 
T

TC

In Access, it wouldn't be unreasonable to store it in a field in a
table. You could have a special table designed for that purpose. The
table could have exactly 1 row. Each field in the table could store a
different "permanent" value. If you needed to store "per user" values,
have a seperate row for each user.

Another way would be to store them in the registry. This is what I
currently do. The problem is, that registry access is getting tighter
and tighter with each successive version of Windows. For example, in
win XP, I think I am right in saying that a non-administrative user can
not access the HKLM branch of the registry.

Another way is to store them in custom database properties. See the
CreateProperty statement.

HTH,
TC
 
T

TC

When I said each field could store 'a different "permanent" value', I
meant, for example, that f1 could store a path for function #1, f2
could store a path for function #2, f3 could store a number for
function #6, and so on.

TC
 
P

(PeteCresswell)

Per Travis:
What is generally considered to be the best way to store variables like
a user's default save directory?

I can hard code it into the VBA easily enough, but then I've got to
recode when I install on a new machine.

I can open a file dialog box to get the user to input this information,
but then I've got to do it every time.

The solution of course is to get the user to input this data ONCE and
then save it somehow.
Click to expand...

Of the several ways (tblParms, Window's registry, Text files, .INI file,
hard-coding...), I prefer the .INI file for variables that the app can do
without.

Pros:
------------------------------------
1) Flexible.
If you save a lot of variables adding new ones doesn't require any changes to a
table. I tend to save each screen's entire state in Form_Close() and restore
same in Form_Open(). e.g. Form coordinates and size, RecordID of
currently-selected record, any user options in effect, and so-forth.

2) Easy to make user-specific.
Just save the variables under an option group that consists of the user's
Windows logon name.

3) Simple to code.
Once you have a few wrapper functions coded - like CurrentUser_Get(),
IniValue_Get() and IniValue_Put() to retrieve LAN user id, read/write parms;
saving/retrieving additional info is just a line of code on each end.

4) PC-independent.
Values can be shared to any PC on the LAN (as opposed to values stored in the
registry)
------------------------------------

Cons:
------------------------------------
1) It hinges on the user using the same .INI file each time the app is executed.

Moot in my scheme of things because I do not let users open up my apps directly.
I give them an icon that points to a .BAT file on the LAN that copies the latest
version of the app down to C:\TEMP (if it isn't there already) and then
executes it - specifying the .INI file at that time. My .INI files also
contain the address of each back-end database so that the app can reconnect
itself if/when necessary.


2) Now we're storing data somewhere besides the back end.

Again, not an issue for me because, except for the back end paths, I only store
trivial/throwaway data in the .INI file.
 
A

aaron.kempf

if you could use Access Data Projects; it is quite friggin easy to
email queries and that type of thing

xp_sendmail right?

yet another reason that MDB sucks ballz
 
M

Mike Labosh

What is generally considered to be the best way to store variables like
a user's default save directory?
Click to expand...

The replies in here are all good, and directly answer what you are asking.
Here is an alternative train of thought though. I like to have my default
save-as location specified in one of two ways:

1. The Working Directory property of your application's shortcut that the
user uses to launch your application
2. The user's "My Documents" folder.

If you like Option 2, here's how to get the user's path to *ANY* of the
system special folders:

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
' STDLIB.H
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''

Private Const MAX_PATH As Long = 260

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
' SHLOBJ.H
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''

' Retrieves the path of a special folder, identified by its CSIDL
Private Declare Function apiSHGetSpecialFolderPath _
Lib "Shell32.dll" Alias "SHGetSpecialFolderPathA" ( _
ByVal hwndOwner As Long, _
ByVal lpszPath As String, _
ByVal nFolder As Long, _
ByVal fCreate As Integer _
) As Integer

Public Enum CSIDL
Desktop = &H0 ' <Desktop>
Internet = &H1 ' Internet Explorer (icon on desktop)
Programs = &H2 ' Start Menu\Programs
Controls = &H3 ' My Computer\Control Panel
Printers = &H4 ' My Computer\Printers
Personal = &H5 ' My Documents
Favorites = &H6 ' <Username>\Favorites
Startup = &H7 ' Start Menu\Programs\Startup
Recent = &H8 ' <Username>\Recent
SendTo = &H9 ' <Username\SendTo
BitBucket = &HA ' <Desktop>\Recycle Bin
StartMenu = &HB ' <Username>\Start Menu
MyDocuments = &HC ' Logical "My Documents" desktop icon
MyMusic = &HD ' "My Music" folder
MyVideo = &HE ' "My Videos" folder
DesktopDirectory = &H10 ' <Username>\Desktop
Drives = &H11 ' My Computer
Network = &H12 ' Network Neighborhood (My Network
Places)
NetHood = &H13 ' <Username>\nethood
Fonts = &H14 ' Windows\Fonts
Templates = &H15 '
CommonStartMenu = &H16 ' All Users\Start Menu
CommonPrograms = &H17 ' All Users\Start Menu\Programs
CommonStartup = &H18 ' All Users\Startup
CommonDesktopDirectory = &H19 ' All Users\Desktop
AppData = &H1A ' <Username>\Application Data
PrintHood = &H1B ' <Username>\PrintHood
LocalAppData = &H1C ' <Username>\Local Settings\Applicaiton
Data (non roaming)
AltStartup = &H1D ' non localized startup
CommonAltStarup = &H1E ' non localized common startup
CommonFavorites = &H1F '
InternetCache = &H20 '
Cookies = &H21 '
History = &H22 '
CommonAppData = &H23 ' All Users\Application Data
Windows = &H24 ' GetWindowsDirectory()
System = &H25 ' GetSystemDirectory()
ProgramFiles = &H26 ' C:\Program Files
MyPictures = &H27 ' C:\Program Files\My Pictures
Profile = &H28 ' C:\Documents and Settings\<Username>
SystemX86 = &H29 ' x86 system directory on RISC
ProgramFilesX86 = &H2A ' x86 C:\Program Files on RISC
ProgramFilesCommon = &H2B ' C:\Program Files\Common
ProgramFilesCommonX86 = &H2C ' x86 Program Files\Common on RISC
CommonTemplates = &H2D ' All Users\Templates
CommonDocuments = &H2E ' All Users\Documents
CommonAdminTools = &H2F ' All Users\Start
Menu\Programs\Administrative Tools
AdminTools = &H30 ' <Username>\Start
Menu\Programs\Administrative Tools
Connections = &H31 ' Network and Dial-up Connections
CommonMusic = &H35 ' All Users\My Music
CommonPictures = &H36 ' All Users\My Pictures
CommonVideo = &H37 ' All Users\My Video
Resources = &H38 ' Resource Direcotry
ResourcesLocalized = &H39 ' Localized Resource Direcotry
CommonOEMLinks = &H3A ' Links to All Users OEM specific apps
CDBurnArea = &H3B ' <Username>\Local Settings\Application
Data\Microsoft\CD Burning
ComputersNearMe = &H3D ' Computers Near Me (computers from
Workgroup membership)
Create = &H8000 ' Combine with CSIDL_ value to force
folder creation
DontVerify = &H4000 ' Combine with CSIDL_ value to return an
unverified folder path
NoAlias = &H1000 ' Combine with CSIDL_ value to insure
non-alias versions of the pidl
PerUserInit = &H800 ' Combine with CSIDL_ value to indicate
per-user init (eg. upgrade)
Mask = &HFF00 ' Mask for all possible flag values
End Enum

Public Function SHGetSpecialFolderPath( _
ByRef ownerForm As Form, _
ByVal specialFolder As CSIDL, _
ByVal forceCreation As Boolean _
) As String

On Error GoTo SHGetSpecialFolderPathError

Dim lpszPath As String
Dim result As Integer
Dim fCreate As Integer

lpszPath = String$(MAX_PATH, 0)

If forceCreation Then fCreate = 1 Else fCreate = 0

result = apiSHGetSpecialFolderPath(ownerForm.hWnd, lpszPath,
specialFolder, fCreate)

If result = 1 Then
' Success
SHGetSpecialFolderPath = Trim$(lpszPath)
Else
SHGetSpecialFolderPath = ""
End If

SHGetSpecialFolderPathExit:
Exit Function

SHGetSpecialFolderPathError:
SHGetSpecialFolderPath = ""
Resume SHGetSpecialFolderPathExit

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
' Then, in your form, do this:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''

Dim myDocuments As String
myDocuments = SHGetSpecialFolderPath(Me, Personal, False)
 
A

aaron.kempf

you guys are just a bunch of fucking idiots

i mean-- YEAH LETS KEEP VARIABLES IN THE REGISTRY

LETS KEEP VARIABLES IN A FILE

i mean-- are you guys drunk?

keeep variables in a database

you can do everything from one place; i mean-- the complexity of the
app grows about 100x if you mix and match where you store DATA

if you use ACCESS DATA PROJECTS each connection has a constant named
SPID

then you have a table named SYSAPPSETTINGS
with columns like D_START and D_END and things along those lines..

when you need to update the D_START parameter for one user; you run
'UPDATE SYSAPPSETTINGS SET D_START = @D_START WHERE SPID = @@SPID'

it's pretty cut and dry; and it's about 1billion times more powerful
than playing with the registry or anything else

no parameters passing around; store everything in a database; and then
join to it when you need a variable

-aaron
 
P

(PeteCresswell)

Per [email protected]:
keeep variables in a database

you can do everything from one place; i mean-- the complexity of the
app grows about 100x if you mix and match where you store DATA

if you use ACCESS DATA PROJECTS each connection has a constant named
SPID

then you have a table named SYSAPPSETTINGS
with columns like D_START and D_END and things along those lines..

when you need to update the D_START parameter for one user; you run
'UPDATE SYSAPPSETTINGS SET D_START = @D_START WHERE SPID = @@SPID'

it's pretty cut and dry; and it's about 1billion times more powerful
than playing with the registry or anything else

no parameters passing around; store everything in a database; and then
join to it when you need a variable
Click to expand...

I'd agree with everything said except for something that was not said: speed.

I'm not saying that Getting/Putting from a .INI file is faster - only that I
don't know which is faster: .INI or Table lookups. Going to the .INI file is
*really* fast.
 
P

(PeteCresswell)

Per (PeteCresswell):
I'd agree
Click to expand...

Also, there could be a (maybe esoteric...) distinction between data that is part
of the "application" and data that is just to grease the UI.

Now that you've said it, I might have to try using an "stblParms" in my next app
- but it bothers me a little bit that now I'm stashing stuff like screen
locations and user option selections in the database that really have nothing to
do with the 'real' data that the users are interested in.
 
T

TC

I used to put application specific data under
HKEY_LOCAL_MACHINE\Software\MyCompany\MyProduct in the registry, but
non-administrative users can not write to that key in win XP and above
(unless you change its security level) :-(

TC
 
A

aaron.kempf

i disagree; speed is not a concern

i mean-- do you really want to spend all your life reading and writing
and reading and writing variables?

keep it all in a database; and you can JOIN to your variables i mean
jesus

if you use an ACCESS DATA PROJECT you can use @@SPID to keep a contact
# for a connection-- the best feature about ADP is that it keeps it's
connection open the whole session.. so you can do variables on the
database side effectively

then it's easy to view them all in one place; etc

consistent

even if INI is 20% faster than writing to a database-- is it worth all
the extra new shapes that your data has to live in??
 
P

(PeteCresswell)

Per [email protected]:
if you use an ACCESS DATA PROJECT you can use @@SPID to keep a contact
# for a connection-- the best feature about ADP is that it keeps it's
connection open the whole session.. so you can do variables on the
database side effectively

then it's easy to view them all in one place; etc

consistent
Click to expand...

I think you're winning me over....
 
Top