Static database objects

[Bruce]

Hello,

I have a number of instances in which I need to grab a single value
from a table based on an index. Usually that table is an attached
table. I know Access provides the DLookup function to do this kind of
thing but I've been fooling around with functions like the abbreviated
one below to do this more efficiently:

Function GetVal(lngIndexID as long) as Variant

Static db as Database
Static rst as RecordSet

' GetRecordSet() returns a table-type recordset in rst and
' a database reference in db for the table named in the first
' argument regardless of whether it is attached or resides
' in the current database.
If rst Is Nothing Then
Call GetRecordSet("tblMyTable", db, rst)
rst.Index = "PrimaryKey"
End If

rst.Seek "=", lngIndexID
GetVal = rst.Fields("MyField")

End Function

My question is if anyone has used this kind of function (using static
db and recordset objects) in a production system and if they've
experienced any negative consequences. For example, I have no idea
how much it costs in terms of memory to maintain static references to
a number of database objects and/or recordset objects or if that
amount of memory changes depending on the characteristics of the
objects themselves, i.e., number of tables, number of fields, etc.
Are there instances where this kind of function ceased to work for
you? It seems to work well in my limited testing at least. Any
comments greatly appreciated. Thanks.

Bruce

[Lyle Fairfield]

br***@aristotle.net (Bruce) wrote in news:d3b3c84d.0311050956.127731d0
@posting.google.com:

Hello,

I have a number of instances in which I need to grab a single value
from a table based on an index. Usually that table is an attached
table. I know Access provides the DLookup function to do this kind of
thing but I've been fooling around with functions like the abbreviated
one below to do this more efficiently:

Function GetVal(lngIndexID as long) as Variant

Static db as Database
Static rst as RecordSet

' GetRecordSet() returns a table-type recordset in rst and
' a database reference in db for the table named in the first
' argument regardless of whether it is attached or resides
' in the current database.
If rst Is Nothing Then
Call GetRecordSet("tblMyTable", db, rst)
rst.Index = "PrimaryKey"
End If

rst.Seek "=", lngIndexID
GetVal = rst.Fields("MyField")

End Function

My question is if anyone has used this kind of function (using static
db and recordset objects) in a production system and if they've
experienced any negative consequences.

I have used something like this ... as below:

Dim rLogin As ADODB.Recordset

Public Property Get Login() As ADODB.Recordset
Set Login = rLogin
End Property

This code is part of my login form's module. Througout the application,
forms check this recordset with something like

With Form_frmLogin
User = .Login.Collect(0)
End With

and modify what they show and allow according to what they find. I've
strayed from Access security in this way because my app is part ADP and
part HTML-ASP.
It's not been a problem yet.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Pieter Linden]

Trevor Best did this kind of stuff a long time ago. TLookup etc... I
*think* it's on AccessWeb www.mvps.org/access

[MeadeR]

It's probably a better practice to put the info into a global variable
or object and not stay connected to any database/table. The best
designs I've seen revolve around transaction based interactions with a
database - this would allow for easy migration to a web front end and
the same db and quereies

br***@aristotle.net (Bruce) wrote in message news:<d3**************************@posting.google. com>...

Hello,

I have a number of instances in which I need to grab a single value
from a table based on an index. Usually that table is an attached
table. I know Access provides the DLookup function to do this kind of
thing but I've been fooling around with functions like the abbreviated
one below to do this more efficiently:

Function GetVal(lngIndexID as long) as Variant

Static db as Database
Static rst as RecordSet

' GetRecordSet() returns a table-type recordset in rst and
' a database reference in db for the table named in the first
' argument regardless of whether it is attached or resides
' in the current database.
If rst Is Nothing Then
Call GetRecordSet("tblMyTable", db, rst)
rst.Index = "PrimaryKey"
End If

rst.Seek "=", lngIndexID
GetVal = rst.Fields("MyField")

End Function

My question is if anyone has used this kind of function (using static
db and recordset objects) in a production system and if they've
experienced any negative consequences. For example, I have no idea
how much it costs in terms of memory to maintain static references to
a number of database objects and/or recordset objects or if that
amount of memory changes depending on the characteristics of the
objects themselves, i.e., number of tables, number of fields, etc.
Are there instances where this kind of function ceased to work for
you? It seems to work well in my limited testing at least. Any
comments greatly appreciated. Thanks.

Bruce