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

Static database objects

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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)
Nov 12 '05 #2

P: n/a
Trevor Best did this kind of stuff a long time ago. TLookup etc... I
*think* it's on AccessWeb www.mvps.org/access
Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.