Local Table vs. Class for Public Variables with simple types

[jimfortune]

I have a question based somewhat on:

http://groups.google.com/group/comp....2bbc027bf00720

A local table works well as a poor-man's repository for public
variables with simple 'Types.' What are the relative merits of
creating a class or collection versus using a local table?

James A. Fortune

[Lyle Fairfield]

I'm not really answering your question (aplogies) but
I have used custom properties of the database or in access >=2000 of
the current project.
Advantages:
1. They persist and are available on opening the application (that is
we can save a user's preferences) ;
2. The belong to the front end so not worry about differentiatings
John's from Jean's.

[jimfortune]

A local table has the same advantages. Do the custom properties have
an advantage that is not shared by a local table?

James A. Fortune

[Terry Kreft]

The main thing is persistence.

If you are simply persisting static values then it doesn't matter what you
use.

I you are persisting values which can be altered by the user (i.e. user
settings) then you need to persist to something which can be written to.
Typical examples include:-
Data tables
Ini Files
Plain text files
Registry (especially using GetSetting/SaveSetting)
Database properties


--
Terry Kreft

<ji********@compumarc.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a question based somewhat on:

http://groups.google.com/group/comp....2bbc027bf00720

A local table works well as a poor-man's repository for public
variables with simple 'Types.' What are the relative merits of
creating a class or collection versus using a local table?

James A. Fortune

[Danny J. Lesandrini]

I've used custom properties also, though not recently. The advantage,
in my opinion, is that the syntax for retrieving them is simpler ...

strUser = GetProperty("username")

as opposed to
strUser = Dlookup("[UserName]","[tblMyProperties]")

Perhaps it's only slightly simpler, but I always felt it was easier to
remember the syntax for my property calls. Mind you, the GetProperty
call is not a native Access function, but something I encapsulated, so
it's not like you can cut and paste that call and have it work. I'm only
speaking to the ease with which it can be done. I suppose you could
do the same with the DLookup() call, or create a class to get the props
from a table.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
<ji********@compumarc.com> wrote ...

A local table has the same advantages. Do the custom properties have
an advantage that is not shared by a local table?

James A. Fortune

[jimfortune]

Terry Kreft wrote:

The main thing is persistence.

If you are simply persisting static values then it doesn't matter what you
use.

I you are persisting values which can be altered by the user (i.e. user
settings) then you need to persist to something which can be written to.
Typical examples include:-
Data tables
Ini Files
Plain text files
Registry (especially using GetSetting/SaveSetting)
Database properties

I think the Data tables and Database properties have an advantage over
the others since the changes are contained within the mdb file itself.
What do you think about storing information in an XML file for more
complicated objects?

James A. Fortune

I think in (american) football there's a mistaken idea that you can get
by with average offensive and defensive linemen if you have a star
quarterback, star receivers and a star running back. I have lots of
patience, rather, for any team that picks linemen high in the draft.
Those offensive linemen will turn average QB's, running backs and
receivers into stars. Those defensive linemen will turn the other
team's QB's, running backs and receivers into losers.

[jimfortune]

Danny J. Lesandrini wrote:

I've used custom properties also, though not recently. The advantage,
in my opinion, is that the syntax for retrieving them is simpler ...

strUser = GetProperty("username")

as opposed to
strUser = Dlookup("[UserName]","[tblMyProperties]")

Perhaps it's only slightly simpler, but I always felt it was easier to
remember the syntax for my property calls. Mind you, the GetProperty
call is not a native Access function, but something I encapsulated, so
it's not like you can cut and paste that call and have it work. I'm only
speaking to the ease with which it can be done. I suppose you could
do the same with the DLookup() call, or create a class to get the props
from a table.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast

Thanks. That gives me a better idea of the tradeoffs.

James A. Fortune

[Lyle Fairfield]

Let's see:
1. assuming be/fe split they won't require a third file;
2. regardless of fe/be split their existence will seldom make
compacting required;
3. they are accessible in one line of code without recordset connection
query etc;
4. one sees horror stories in cdma about local tables; one sees fewer
horror stories about custom properties;
5. they may be inherently more private (in the sense that it's unlkely
Sally will see them unless she knows where to look and what to look for
or is clever enough to walk the properties collection) even if she
opens Fred's file;
6. the IT department will never know they exist; hence the IT
department won't screw them up sometime just because they CAN!;
7. in access >= 2000 they can be assigned to access objects
(trivial examples below)

CurrentProject.AllForms(Me.Name).Properties.Add "SomeName", 23

MsgBox CurrentProject.AllForms(Me.Name).Properties("SomeN ame")

If CurrentProject.AllForms(Me.Name).Properties("SomeN ame") = 23 Then
'do something
End If

CurrentProject.AllForms(Me.Name).Properties.Remove "SomeName"

[Lyle Fairfield]

I also use the ADO recordset Save method. It's very simple and very
fast; The recordset can be reconstructed with the Open method. It
requires only a few lines of code.

[David W. Fenton]

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in
news:q-********************@giganews.com:

I've used custom properties also, though not recently. The
advantage, in my opinion, is that the syntax for retrieving them
is simpler ...

strUser = GetProperty("username")

as opposed to
strUser = Dlookup("[UserName]","[tblMyProperties]")

Perhaps it's only slightly simpler, but I always felt it was
easier to remember the syntax for my property calls. Mind you,
the GetProperty call is not a native Access function, but
something I encapsulated, so it's not like you can cut and paste
that call and have it work. I'm only speaking to the ease with
which it can be done. I suppose you could do the same with the
DLookup() call, or create a class to get the props from a table.

You could very easily write a GetProperty function that internally
did a DLookup, so there really is no issue here of GetProperty being
easier.

My feeling is that retrieving custom properties of the MDB is slower
than retrieving data from a table.

Either way, I'd wrap both in a self-healing class module and
initialize it at startup and then use the class module to get the
settings.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Terry Kreft]

I wasn't reccomending any particular method. I would agree that database
properties and tables are better because they are carried with the database.

If I was storing more complicated objects e.g. I wanted to persist the state
of a class module (or form) then XML may well be the way to go, personally
though I would look at tables or property bags.

The advantage of a property bag is that you can store it whereever you want
so you can persist it to a file, or the registry or a database field. They
are less easy to "crack" than XML as well.

You could use an ADO stream and persist that to a field in a table as well.
e.g.

(BTW this is a horrible cludgy piece of code which I've knocked up because
I've only just thought of this as a method, so no digs on the style please)

Function ADOStream()
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim stm As ADODB.Stream
Dim strTest As String

Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set stm = New ADODB.Stream

rs1.Open "SELECT * FROM Customers", "Provider=sqloledb;" & _
"Data Source=wmpsdeterry3;Initial Catalog=Northwind;" & _
"User Id=sa;Password=;""", adOpenStatic, adLockReadOnly, adCmdText

rs1.Save stm, adPersistXML

strTest = stm.ReadText

rs2.Open "SELECT * FROM tblPersist WHERE 1=0", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

With rs2
.AddNew
.Fields("persist").AppendChunk strTest
.Update
End With
End Function

Function GetItBack()
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim stm As ADODB.Stream
Dim strTest As String

Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set stm = New ADODB.Stream

rs2.Open "SELECT * FROM tblPersist WHERE [id]=1",
CurrentProject.Connection

With rs2.Fields("persist")
strTest = .GetChunk(.ActualSize)
End With
stm.Open
Call stm.WriteText(strTest)
stm.Position = 0
rs1.Open stm
End Function

--
Terry Kreft

<ji********@compumarc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Terry Kreft wrote:

The main thing is persistence.

If you are simply persisting static values then it doesn't matter what
you
use.

I you are persisting values which can be altered by the user (i.e. user
settings) then you need to persist to something which can be written to.
Typical examples include:-
Data tables
Ini Files
Plain text files
Registry (especially using GetSetting/SaveSetting)
Database properties

I think the Data tables and Database properties have an advantage over
the others since the changes are contained within the mdb file itself.
What do you think about storing information in an XML file for more
complicated objects?

James A. Fortune

I think in (american) football there's a mistaken idea that you can get
by with average offensive and defensive linemen if you have a star
quarterback, star receivers and a star running back. I have lots of
patience, rather, for any team that picks linemen high in the draft.
Those offensive linemen will turn average QB's, running backs and
receivers into stars. Those defensive linemen will turn the other
team's QB's, running backs and receivers into losers.

[jimfortune]

Terry Kreft wrote:

I wasn't reccomending any particular method. I would agree that database
properties and tables are better because they are carried with the database.

If I was storing more complicated objects e.g. I wanted to persist the state
of a class module (or form) then XML may well be the way to go, personally
though I would look at tables or property bags.

The advantage of a property bag is that you can store it whereever you want
so you can persist it to a file, or the registry or a database field. They
are less easy to "crack" than XML as well.

You could use an ADO stream and persist that to a field in a table as well.
e.g.

(BTW this is a horrible cludgy piece of code which I've knocked up because
I've only just thought of this as a method, so no digs on the style please)

Function ADOStream()
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim stm As ADODB.Stream
Dim strTest As String

Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set stm = New ADODB.Stream

rs1.Open "SELECT * FROM Customers", "Provider=sqloledb;" & _
"Data Source=wmpsdeterry3;Initial Catalog=Northwind;" & _
"User Id=sa;Password=;""", adOpenStatic, adLockReadOnly, adCmdText

rs1.Save stm, adPersistXML

strTest = stm.ReadText

rs2.Open "SELECT * FROM tblPersist WHERE 1=0", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

With rs2
.AddNew
.Fields("persist").AppendChunk strTest
.Update
End With
End Function

Function GetItBack()
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim stm As ADODB.Stream
Dim strTest As String

Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set stm = New ADODB.Stream

rs2.Open "SELECT * FROM tblPersist WHERE [id]=1",
CurrentProject.Connection

With rs2.Fields("persist")
strTest = .GetChunk(.ActualSize)
End With
stm.Open
Call stm.WriteText(strTest)
stm.Position = 0
rs1.Open stm
End Function

--
Terry Kreft

Terry,

I admit that I've never considered using an ADO stream saved in a table
for that purpose before. I will consider the idea carefully. The data
I persist is not usually sensitive but your point about the readability
of XML is an important one. Thanks for sharing your opinions on
persistence.

James A. Fortune

[jimfortune]

Lyle Fairfield wrote:

Let's see:
1. assuming be/fe split they won't require a third file;
2. regardless of fe/be split their existence will seldom make
compacting required;
3. they are accessible in one line of code without recordset connection
query etc;
4. one sees horror stories in cdma about local tables; one sees fewer
horror stories about custom properties;
5. they may be inherently more private (in the sense that it's unlkely
Sally will see them unless she knows where to look and what to look for
or is clever enough to walk the properties collection) even if she
opens Fred's file;
6. the IT department will never know they exist; hence the IT
department won't screw them up sometime just because they CAN!;
7. in access >= 2000 they can be assigned to access objects
(trivial examples below)

CurrentProject.AllForms(Me.Name).Properties.Add "SomeName", 23

MsgBox CurrentProject.AllForms(Me.Name).Properties("SomeN ame")

If CurrentProject.AllForms(Me.Name).Properties("SomeN ame") = 23 Then
'do something
End If

CurrentProject.AllForms(Me.Name).Properties.Remove "SomeName"

Thanks for the list Lyle. Although none of the items in this list
concern me at the moment, #7 will be particularly cogent once I no
longer have to support A97 (don't even go there :-)). It looks like
custom properties are a nice way to go.

James A. Fortune