By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Access Custom property vs. Global Variable

P: n/a
I'm using Access 2K. I'm hoping someone can tell me which method
performs faster- (currently I'm using a mix of both methods)

a.) creating custom properties and then calling functions to set and
retrieve the values... (ie:
application.currentproject.properties(propName))

b.) creating global variables in the form of an array and using a
function to populate and retrieve them.

I set string, numeric and boolean values based on the user selected
from a sign-in form and call these values frequently throughout the
application.

Thanks,
lq

Nov 13 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Use a global variable if you just need a temporary memory assignment that's
available to all routines. It will be faster - particularly for assigning a
value).

Use a custom property if you want something that survives between sessions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
I'm using Access 2K. I'm hoping someone can tell me which method
performs faster- (currently I'm using a mix of both methods)

a.) creating custom properties and then calling functions to set and
retrieve the values... (ie:
application.currentproject.properties(propName))

b.) creating global variables in the form of an array and using a
function to populate and retrieve them.

I set string, numeric and boolean values based on the user selected
from a sign-in form and call these values frequently throughout the
application.

Thanks,
lq

Nov 13 '05 #2

P: n/a
Allen thanks.
What I am trying to determine if there is an overhead associated with
having say 20 custom properties in the application, and if so, is it
worse than having to load 20 global values in an array from a remote
server.
lq

Allen Browne wrote:
Use a global variable if you just need a temporary memory assignment that's available to all routines. It will be faster - particularly for assigning a value).

Use a custom property if you want something that survives between sessions.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
I'm using Access 2K. I'm hoping someone can tell me which method
performs faster- (currently I'm using a mix of both methods)

a.) creating custom properties and then calling functions to set and retrieve the values... (ie:
application.currentproject.properties(propName))

b.) creating global variables in the form of an array and using a
function to populate and retrieve them.

I set string, numeric and boolean values based on the user selected
from a sign-in form and call these values frequently throughout the
application.

Thanks,
lq


Nov 13 '05 #3

P: n/a
Assigning a value to a property will be slower than assigning a value to a
variable.

Will your code need to check whether the property already exists, and create
it if it does not? There's extra overhead in that also.

The difference may be academic for just 20 values, unless you are setting
them repeatedly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Allen thanks.
What I am trying to determine if there is an overhead associated with
having say 20 custom properties in the application, and if so, is it
worse than having to load 20 global values in an array from a remote
server.
lq

Allen Browne wrote:
Use a global variable if you just need a temporary memory assignment

that's
available to all routines. It will be faster - particularly for

assigning a
value).

Use a custom property if you want something that survives between

sessions.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
> I'm using Access 2K. I'm hoping someone can tell me which method
> performs faster- (currently I'm using a mix of both methods)
>
> a.) creating custom properties and then calling functions to set and > retrieve the values... (ie:
> application.currentproject.properties(propName))
>
> b.) creating global variables in the form of an array and using a
> function to populate and retrieve them.
>
> I set string, numeric and boolean values based on the user selected
> from a sign-in form and call these values frequently throughout the
> application.
>
> Thanks,
> lq
>

Nov 13 '05 #4

P: n/a
rkc
laurenq uantrell wrote:
Allen thanks.
What I am trying to determine if there is an overhead associated with
having say 20 custom properties in the application, and if so, is it
worse than having to load 20 global values in an array from a remote
server.
lq


Run some tests.

If the values are specific enough to each front end so that you can
store them as properties, why not use a local table.
Nov 13 '05 #5

P: n/a
Allen,
What I am doing is storing some user preferences specific to individual
forms, for example, if the user has selected certain checkboxes.
In the past I have sent this info to a SQL table on the server, but
many users are connected by VPN and this seems like a lot of traffic
for nothing, if I can store it in the Access application as custom
properties and make it transfer between sessions.
As always, thanks for your input.
lq

Allen Browne wrote:
Assigning a value to a property will be slower than assigning a value to a variable.

Will your code need to check whether the property already exists, and create it if it does not? There's extra overhead in that also.

The difference may be academic for just 20 values, unless you are setting them repeatedly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Allen thanks.
What I am trying to determine if there is an overhead associated with having say 20 custom properties in the application, and if so, is it worse than having to load 20 global values in an array from a remote server.
lq

Allen Browne wrote:
Use a global variable if you just need a temporary memory assignment
that's
available to all routines. It will be faster - particularly for

assigning a
value).

Use a custom property if you want something that survives between

sessions.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
> I'm using Access 2K. I'm hoping someone can tell me which method
> performs faster- (currently I'm using a mix of both methods)
>
> a.) creating custom properties and then calling functions to set

and
> retrieve the values... (ie:
> application.currentproject.properties(propName))
>
> b.) creating global variables in the form of an array and using

a > function to populate and retrieve them.
>
> I set string, numeric and boolean values based on the user selected > from a sign-in form and call these values frequently throughout the > application.
>
> Thanks,
> lq
>


Nov 13 '05 #6

P: n/a
It's an Access project (adp) with a SQL server backend, that's my I'm
not using a local table.

Nov 13 '05 #7

P: n/a
rkc
laurenq uantrell wrote:
It's an Access project (adp) with a SQL server backend, that's my I'm
not using a local table.


If which will load faster is really the deciding criteria, run some
tests. Personally, I'd keep the clutter of local preferences out of the
back end no matter which way loaded a milli-second faster.

A plain old text file on the local machine is another option.
Nov 13 '05 #8

P: n/a
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a
remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every
time a user changes a preference setting on a form...

lq

rkc wrote:
laurenq uantrell wrote:
It's an Access project (adp) with a SQL server backend, that's my I'm not using a local table.
If which will load faster is really the deciding criteria, run some
tests. Personally, I'd keep the clutter of local preferences out of

the back end no matter which way loaded a milli-second faster.

A plain old text file on the local machine is another option.


Nov 13 '05 #9

P: n/a
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a
remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every
time a user changes a preference setting on a form...

lq

rkc wrote:
laurenq uantrell wrote:
It's an Access project (adp) with a SQL server backend, that's my I'm not using a local table.
If which will load faster is really the deciding criteria, run some
tests. Personally, I'd keep the clutter of local preferences out of

the back end no matter which way loaded a milli-second faster.

A plain old text file on the local machine is another option.


Nov 13 '05 #10

P: n/a
If this is a workstation preference, I would use a local table.
If it's an all-user preference, I'd use a table in the back end.

It's not too difficult to run some timing tests to see what suits you best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Allen,
What I am doing is storing some user preferences specific to individual
forms, for example, if the user has selected certain checkboxes.
In the past I have sent this info to a SQL table on the server, but
many users are connected by VPN and this seems like a lot of traffic
for nothing, if I can store it in the Access application as custom
properties and make it transfer between sessions.
As always, thanks for your input.
lq

Allen Browne wrote:
Assigning a value to a property will be slower than assigning a value

to a
variable.

Will your code need to check whether the property already exists, and

create
it if it does not? There's extra overhead in that also.

The difference may be academic for just 20 values, unless you are

setting
them repeatedly.

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
> Allen thanks.
> What I am trying to determine if there is an overhead associated with > having say 20 custom properties in the application, and if so, is it > worse than having to load 20 global values in an array from a remote > server.
> lq
>
>
>
> Allen Browne wrote:
>> Use a global variable if you just need a temporary memory assignment > that's
>> available to all routines. It will be faster - particularly for
> assigning a
>> value).
>>
>> Use a custom property if you want something that survives between
> sessions.
>>
>> "laurenq uantrell" <la*************@hotmail.com> wrote in message
>> news:11**********************@c13g2000cwb.googlegr oups.com...
>> > I'm using Access 2K. I'm hoping someone can tell me which method
>> > performs faster- (currently I'm using a mix of both methods)
>> >
>> > a.) creating custom properties and then calling functions to set
> and
>> > retrieve the values... (ie:
>> > application.currentproject.properties(propName))
>> >
>> > b.) creating global variables in the form of an array and using a >> > function to populate and retrieve them.
>> >
>> > I set string, numeric and boolean values based on the user selected >> > from a sign-in form and call these values frequently throughout the >> > application.
>> >
>> > Thanks,
>> > lq
>> >
>

Nov 13 '05 #11

P: n/a
Local text file works just fine for me. Much less messy than 1 or 2. Never
tried 4, but, 3 was perfectly fine.
Darryl Kerkeslager
"laurenq uantrell" <la*************@hotmail.com> wrote
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a
remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every
time a user changes a preference setting on a form...

lq

rkc wrote:
laurenq uantrell wrote:
It's an Access project (adp) with a SQL server backend, that's my I'm not using a local table.


If which will load faster is really the deciding criteria, run some
tests. Personally, I'd keep the clutter of local preferences out of

the
back end no matter which way loaded a milli-second faster.

A plain old text file on the local machine is another option.

Nov 13 '05 #12

P: n/a
rkc
laurenq uantrell wrote:
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a
remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every
time a user changes a preference setting on a form...


I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value
pairs, one set per line. When the application started I would read
them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the file.

Nov 13 '05 #13

P: n/a
Thanks.
Now to just find some code to read/write to a txt file...
lq
rkc wrote:
laurenq uantrell wrote:
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every time a user changes a preference setting on a form...


I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value
pairs, one set per line. When the application started I would read
them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the file.


Nov 13 '05 #14

P: n/a
Quick copy and paste:

GetUserLogin() - function to retrieve login/profile
LogError - function to log errors

Note that this code actually reads the file and copies it to a local table.
When time to save the preferences, it writes from the same table.
Darryl Kerkeslager

--------------------------------------------------------------

Public Function ReadPreferences()
On Error GoTo handle_error
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim s As String
cnxn.Execute "DELETE * FROM db_preference"
On Error GoTo write_file
Open "C:\Documents and Settings\" & GetUserLogin() & "\odis_user.ini" For
Input As #1
On Error GoTo handle_error
With rs
.Open "db_preference", cnxn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
.AddNew
.Fields(0) = 1
Input #1, s
.Fields(1) = Left(s, 3)
Input #1, s
.Fields(2) = Left(s, 3)
Input #1, s
.Fields(3) = Left(s, 7)
Input #1, s
.Fields(4) = Left(s, 50)
Input #1, s
.Fields(5) = Left(s, 15)
Input #1, s
.Fields(6) = Left(s, 25)
Input #1, s
.Fields(7) = Left(s, 50)
Input #1, s
.Fields(8) = Left(s, 100)
On Error GoTo handle_error
.Update
.Close
End With
exit_point:
Close #1
Exit Function
write_file: ' writes defaults
Err.Clear
With rs
.Open "db_preference", cnxn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
.AddNew
.Fields(0) = 1
.Fields("prf_armed") = "No"
.Fields("prf_state_vehicle") = "Yes"
.Fields("prf_cell_phone") = "0000000"
.Fields("prf_travel_radius") = "No overnight travel without
notifying PO."
.Fields("prf_photo_card") = "CF Card"
.Fields("prf_card_reader") = "E:\"
.Fields("prf_reporting") = "First week of every month."
.Fields("prf_ppc_folder") = "W:\Pocket_PC My Documents\"
.Update
.Close
End With
WritePreferences
GoTo exit_point
handle_error:
LogError Err.Number, "ReadPreferences", Err.Description
GoTo exit_point
End Function
----------------------------------------------------------
Public Function WritePreferences()
On Error GoTo handle_error
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Open "C:\Documents and Settings\" & GetUserLogin() & "\odis_user.ini" For
Output As #1
With rs
.Open "db_preference", cnxn, adOpenForwardOnly, adLockReadOnly,
adCmdTableDirect
Write #1, .Fields(1)
Write #1, .Fields(2)
Write #1, .Fields(3)
Write #1, .Fields(4)
Write #1, .Fields(5)
Write #1, .Fields(6)
Write #1, .Fields(7)
Write #1, .Fields(8)
.Close
End With
exit_point:
Close #1
Set rs = Nothing
Exit Function
handle_error:
LogError Err.Number, "WritePreferences", Err.Description
MsgBox "Your changes could not be saved. Try exiting and restarting.",
vbCritical, "File Error"
GoTo exit_point
End Function
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks.
Now to just find some code to read/write to a txt file...
lq
rkc wrote:
laurenq uantrell wrote:
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every time a user changes a preference setting on a form...


I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value
pairs, one set per line. When the application started I would read
them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the file.

Nov 13 '05 #15

P: n/a
The "standard" for this is an ini file

' ******* Code Start ************
Option Explicit

Private Declare Function GetPrivateProfileString _
Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long
Private Declare Function WritePrivateProfileString _
Lib "kernel32" Alias "WritePrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpString As String, _
ByVal lpFileName As String _
) As Long

Property Get IniValue(IniFile As String, Section As String, ValueName As
String) As String
Dim lngRet As Long
Dim lpReturnedString As String
Dim nSize As Long
Dim lngTermChar As Long
Dim lngCount As Long

nSize = 255

If Len(Section) < 1 Or Len(ValueName) < 1 Then
lngTermChar = 2
Else
lngTermChar = 1
End If

Do
lngCount = lngCount + 1
nSize = nSize * lngCount
lpReturnedString = Space(nSize)
lngRet = GetPrivateProfileString(Section, ValueName, _
"", lpReturnedString, _
nSize, IniFile _
)

Loop While lngRet = (nSize - lngTermChar)

IniValue = Left(lpReturnedString, lngRet)
End Property
Property Let IniValue(IniFile As String, Section As String, ValueName As
String, Value As String)
Dim lngRet As Long

lngRet = WritePrivateProfileString(Section, ValueName, _
Value, IniFile)
End Property
' ******* Code Start ************

Paste into a module (modINI) then you can do this sort of thing

' To put a value to the ini file
modINI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"

' To retrieve a value from the ini file
MsgBox modINI.IniValue("c:\inn.ini", "test", "test2")

Or
Paste into a class module (cINI) then you can do this sort of thing

' To put a value to the ini file
Dim cI As cIni
Set cI = New cIni

cI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"
Set cI = Nothing
' To retrieve a value from the ini file
Dim cI As cIni
Set cI = New cIni

MsgBox cI.IniValue("c:\inn.ini", "test", "test2")
Set cI = Nothing
--
Terry Kreft
MVP Microsoft Access
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks.
Now to just find some code to read/write to a txt file...
lq
rkc wrote:
laurenq uantrell wrote:
You do bring up another option with the local text file, so having
these three options (plus I add a fourth):

1.) Global Variable - written and read with a stored procedure from a remote SQL server, in many cases over a slow VPN.
2.) Custom Properties
3.) A local text file
4.) Registry settings

Which is the best option as far as performance, considering these
values have to be read each time certain forms open and written every time a user changes a preference setting on a form...


I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value
pairs, one set per line. When the application started I would read
them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the file.

Nov 13 '05 #16

P: n/a
Terry,
Thanks for the code.
After testing I determined that using your function
(modINI.IniValue(FilePath, "test", "test2") = "aaaa") to write to an
ini text file is about 20X faster than using
Application.CurrentProject.Properties(propName) = propValue to set a
custom property. This is an impressive difference!

But reading a custom property with getProperty =
Application.CurrentProject.Properties(propName) is between 2-5 x faster
than reading a value from an ini file using your stringvalue =
modINI.IniValue(FilePath, "test", "test2")

These tests are based on getting the average speed when executing the
code 1000 times.

So now to decide which to use...
lq

Terry Kreft wrote:
The "standard" for this is an ini file

' ******* Code Start ************
Option Explicit

Private Declare Function GetPrivateProfileString _
Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long
Private Declare Function WritePrivateProfileString _
Lib "kernel32" Alias "WritePrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpString As String, _
ByVal lpFileName As String _
) As Long

Property Get IniValue(IniFile As String, Section As String, ValueName As String) As String
Dim lngRet As Long
Dim lpReturnedString As String
Dim nSize As Long
Dim lngTermChar As Long
Dim lngCount As Long

nSize = 255

If Len(Section) < 1 Or Len(ValueName) < 1 Then
lngTermChar = 2
Else
lngTermChar = 1
End If

Do
lngCount = lngCount + 1
nSize = nSize * lngCount
lpReturnedString = Space(nSize)
lngRet = GetPrivateProfileString(Section, ValueName, _
"", lpReturnedString, _
nSize, IniFile _
)

Loop While lngRet = (nSize - lngTermChar)

IniValue = Left(lpReturnedString, lngRet)
End Property
Property Let IniValue(IniFile As String, Section As String, ValueName As String, Value As String)
Dim lngRet As Long

lngRet = WritePrivateProfileString(Section, ValueName, _
Value, IniFile)
End Property
' ******* Code Start ************

Paste into a module (modINI) then you can do this sort of thing

' To put a value to the ini file
modINI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"

' To retrieve a value from the ini file
MsgBox modINI.IniValue("c:\inn.ini", "test", "test2")

Or
Paste into a class module (cINI) then you can do this sort of thing

' To put a value to the ini file
Dim cI As cIni
Set cI = New cIni

cI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"
Set cI = Nothing
' To retrieve a value from the ini file
Dim cI As cIni
Set cI = New cIni

MsgBox cI.IniValue("c:\inn.ini", "test", "test2")
Set cI = Nothing
--
Terry Kreft
MVP Microsoft Access
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks.
Now to just find some code to read/write to a txt file...
lq
rkc wrote:
laurenq uantrell wrote:
> You do bring up another option with the local text file, so having > these three options (plus I add a fourth):
>
> 1.) Global Variable - written and read with a stored procedure from
a
> remote SQL server, in many cases over a slow VPN.
> 2.) Custom Properties
> 3.) A local text file
> 4.) Registry settings
>
> Which is the best option as far as performance, considering
these > values have to be read each time certain forms open and written

every
> time a user changes a preference setting on a form...

I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value pairs, one set per line. When the application started I would read them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the

file.


Nov 13 '05 #17

P: n/a
My approach:

Create a table called Settings_UserProperties with minimum of two
fields: "PropertyName" and "Value"

Then create module with two VBA functions:
Public Sub SetProperty(strPropName as String, varValue as String)
Public Function GetProperty(strPropName) as Variant

The body of these routines use simple VBA DoCmd.RunSQL and DLookup()
functions to set/get the property values.

If you have multiple users in the database, add a third column called
"UserName" and modify the above-referenced VBA functions accordingly
i.e. SetProperty(strPropName, varValue, strUserName)

The advantage of this approach is that the table is always available -
you don't have to access it through a hidden form, for example.
Furthermore, the database is self contained - you don't have to read
from an external "configuration file"

The disadvantages are: the user(s) can manually view the properties
table and make changes (of course, you can avoid this by making the
table hidden in your production db). Also, the table data can get
"messed" up by bugs, requiring you to fix things manually.

Nov 13 '05 #18

P: n/a
Running pretty extensive tests with the GetTickCount function I have
determined that it's about 20 times slower to write to an Access custom
property than to write to a seperate text based .INI file used to store
user settings.
But it's about 5-10 times slower to read from the INI file than it is
to read from the custom property.
I'm still trying to figure out the moral of the story but I think that
the lesson learned is to write values to the INI file, then read them
once into some global variables on startup and then refer to the global
variables during use of the application.

Nov 13 '05 #19

P: n/a
Terry,
I'm using your code to extract user settings from an ini file using:

Dim valueIwant as string, currentProjectPath as string
currentProjectPath = c:\FullPath\
valueIwant = modINI.IniValue(currentProjectPath & "Application.ini",
"HeaderString", "ValueString")

Is there a way to modify this to grab multiple ValueStrings in the same
pass instead of calling each path individually.

For example:
myValuesIWantArray(1 to 3) =
HeaderString.ValueString1,HeaderString.ValueString 2,HeaderString.ValueSting3

Thanks!
lq

Terry Kreft wrote:
The "standard" for this is an ini file

' ******* Code Start ************
Option Explicit

Private Declare Function GetPrivateProfileString _
Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long
Private Declare Function WritePrivateProfileString _
Lib "kernel32" Alias "WritePrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpString As String, _
ByVal lpFileName As String _
) As Long

Property Get IniValue(IniFile As String, Section As String, ValueName As String) As String
Dim lngRet As Long
Dim lpReturnedString As String
Dim nSize As Long
Dim lngTermChar As Long
Dim lngCount As Long

nSize = 255

If Len(Section) < 1 Or Len(ValueName) < 1 Then
lngTermChar = 2
Else
lngTermChar = 1
End If

Do
lngCount = lngCount + 1
nSize = nSize * lngCount
lpReturnedString = Space(nSize)
lngRet = GetPrivateProfileString(Section, ValueName, _
"", lpReturnedString, _
nSize, IniFile _
)

Loop While lngRet = (nSize - lngTermChar)

IniValue = Left(lpReturnedString, lngRet)
End Property
Property Let IniValue(IniFile As String, Section As String, ValueName As String, Value As String)
Dim lngRet As Long

lngRet = WritePrivateProfileString(Section, ValueName, _
Value, IniFile)
End Property
' ******* Code Start ************

Paste into a module (modINI) then you can do this sort of thing

' To put a value to the ini file
modINI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"

' To retrieve a value from the ini file
MsgBox modINI.IniValue("c:\inn.ini", "test", "test2")

Or
Paste into a class module (cINI) then you can do this sort of thing

' To put a value to the ini file
Dim cI As cIni
Set cI = New cIni

cI.IniValue("c:\inn.ini", "test", "test2") = "aaaa"
Set cI = Nothing
' To retrieve a value from the ini file
Dim cI As cIni
Set cI = New cIni

MsgBox cI.IniValue("c:\inn.ini", "test", "test2")
Set cI = Nothing
--
Terry Kreft
MVP Microsoft Access
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks.
Now to just find some code to read/write to a txt file...
lq
rkc wrote:
laurenq uantrell wrote:
> You do bring up another option with the local text file, so having > these three options (plus I add a fourth):
>
> 1.) Global Variable - written and read with a stored procedure from
a
> remote SQL server, in many cases over a slow VPN.
> 2.) Custom Properties
> 3.) A local text file
> 4.) Registry settings
>
> Which is the best option as far as performance, considering
these > values have to be read each time certain forms open and written

every
> time a user changes a preference setting on a form...

I don't know...
Run some tests.

I would store the properties in a local text file as Property=Value pairs, one set per line. When the application started I would read them into memory. If they changed, I would change them in memory.
When they were no longer needed I would write them back to the

file.


Nov 13 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.