Alternative to db = CurrentDB()

[Mr. Smith]

Hi.
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).

Dim sql
Dim db As Database
Dim rs
Set db = CurrentDb

sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)

As I've not uptated myself lately, is there a new (or old) "killer"
approache which might work more smoothly?

Kind regards
Mr. Smith.

[Stefan Hoffmann]

hi,

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim db As Database
Set db = CurrentDb

This is commonly replaced with a proxy property in a normal stand-alone
module. You don't need to declare your db and need the set. Just use
CurrentDbC.OpenRecordset().


Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property


mfG
--> stefan <--

[Mr. Smith]

Thanks Stefan.
I'll try to convert my db handling to your suggestion.

Mr. Smith

Post by Stefan Hoffmann
hi,

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've
used the way shown below tocommunicate with the tables in the application
some 150 times through the VBA code of the different forms (changing the
SQL ofcourse).
Dim db As Database
Set db = CurrentDb

This is commonly replaced with a proxy property in a normal stand-alone
module. You don't need to declare your db and need the set. Just use
CurrentDbC.OpenRecordset().
Option Compare Database
Option Explicit
Private m_CurrentDb As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
mfG
--> stefan <--

[Stefan Hoffmann]

hi,

Post by Mr. Smith
I'll try to convert my db handling to your suggestion.

This will not only save you lines of code, it will also increase the
overall speed of your application.

When calling CurrentDb Access always creates a new object pointing to
your actual DB. Using the property, this is normaly done only once when
calling CurrentDbC the first time.


mfG
--> stefan <--

[Aaron Kempf]

CORRECTION

THIS IS COMMONLY REPLACED WITH ADO

Post by Stefan Hoffmann
hi,

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim db As Database
Set db = CurrentDb

This is commonly replaced with a proxy property in a normal stand-alone
module. You don't need to declare your db and need the set. Just use
CurrentDbC.OpenRecordset().
Option Compare Database
Option Explicit
Private m_CurrentDb As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
mfG
--> stefan <--

[Fifth Amendment]

this WAS common

until ADP came around

now DAO is completely obsolete

Post by Stefan Hoffmann
hi,

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim db As Database
Set db = CurrentDb

This is commonly replaced with a proxy property in a normal stand-alone
module. You don't need to declare your db and need the set. Just use
CurrentDbC.OpenRecordset().
Option Compare Database
Option Explicit
Private m_CurrentDb As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
mfG
--> stefan <--

[Tony Toews [MVP]]

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim sql
Dim db As Database
Dim rs
Set db = CurrentDb
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)

I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

Tony

[Stefan Hoffmann]

hi Tony,

Post by Tony Toews [MVP]
I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

In this simple case it is just milliseconds. But in the long run, and
especially when called in a loop, it sums up to a nice amount. Maybe
seconds only, but you know just 2 cents :)


mfG
--> stefan <--

[Mr. Smith]

Tanks for useful input (both).

My application is about 7 years, and with new users every now and then, and
more data every week, it's beginning to crack in some of the joints. It's
the classical "prototype put into production"....



Hopefully a new approach on the db communication will improve it.



I' might post new questions regarding my "application remake", appreciate
insightful responses like yours.



Kind regrds.

Mr. Smith.

Post by Stefan Hoffmann
hi Tony,

Post by Tony Toews [MVP]
I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

In this simple case it is just milliseconds. But in the long run, and
especially when called in a loop, it sums up to a nice amount. Maybe
seconds only, but you know just 2 cents :)
mfG
--> stefan <--

[Aaron Kempf]

yeah no crap
Access MDB doesn't work well for multiple users

move to SQL Server and Access Data Projects

Post by Mr. Smith
Tanks for useful input (both).
My application is about 7 years, and with new users every now and then, and
more data every week, it's beginning to crack in some of the joints. It's
the classical "prototype put into production"....
Hopefully a new approach on the db communication will improve it.
I' might post new questions regarding my "application remake", appreciate
insightful responses like yours.
Kind regrds.
Mr. Smith.

Post by Stefan Hoffmann
hi Tony,

Post by Tony Toews [MVP]
I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

In this simple case it is just milliseconds. But in the long run, and
especially when called in a loop, it sums up to a nice amount. Maybe
seconds only, but you know just 2 cents :)
mfG
--> stefan <--

[d***@epsomdotcomdotau]

If it's inside the loop, just move it outside the loop!

If it's inside the loop, use a SQL statement that does
the whole transaction, instead of a loop that calls
repeated SQL statements.

If you need to have code for every line, make a SQL
statement that calls code, rather than code that calls
SQL statements.

I avoid all global variables, so although there is nothing
wrong with:
set rs=GlobalVariable.OpenRecordset(strSQL)
I avoid it anyway. CurrentDB is a global object, so
I'm not avoiding the global object, I'm just avoiding
having a second global object.

Using CurrentDB forces a refresh of the application
databases collection. Since I only have one database
in my databases collection, this does not take long. It
is a measurable delay, but since my code logic minimises
the number of times I have to get a database object, it
makes no difference to my application.

Some people use tabledef objects and indexes to get
very fast 'find' actions. To do this, they need a direct
connection to the back-end database (not CurrentDB).
Creating and destroying that connection is much slower,
and there is no existing global database object, so they
create and maintain a global database object, and do
their own check and refresh each time the object is used.

I don't use tabledef objects, because the SQL Server
equivalent is quite different, and I want to be able to use
either a SQL Server BE or a Jet MDB BE interchangeably
with minimum work. So again, I don't need a special
global database object for my BE database.

The bottom line is, I have complex queries that take 20
seconds to run, and code that takes 15 minutes to run,
and reports that take 10 minutes to run, (instead of 20
minutes, 15 hours, 10 hours), all because I have heavily
optimised everything, and I still haven't optimised out
the "set db=codedb" in my DLookup replacement, -
because it adds less than a second to that 20, and is just
one more thing that might break.

(david)

Post by Stefan Hoffmann
hi Tony,

Post by Tony Toews [MVP]
I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

In this simple case it is just milliseconds. But in the long run, and
especially when called in a loop, it sums up to a nice amount. Maybe
seconds only, but you know just 2 cents :)
mfG
--> stefan <--

[Aaron Kempf]

Hey David';

if we want to talk to your MDB dorks; then we'd go to the
microsoft.public.MDBDORKS newsgroup

Post by d***@epsomdotcomdotau
If it's inside the loop, just move it outside the loop!
If it's inside the loop, use a SQL statement that does
the whole transaction, instead of a loop that calls
repeated SQL statements.
If you need to have code for every line, make a SQL
statement that calls code, rather than code that calls
SQL statements.
I avoid all global variables, so although there is nothing
set rs=GlobalVariable.OpenRecordset(strSQL)
I avoid it anyway. CurrentDB is a global object, so
I'm not avoiding the global object, I'm just avoiding
having a second global object.
Using CurrentDB forces a refresh of the application
databases collection. Since I only have one database
in my databases collection, this does not take long. It
is a measurable delay, but since my code logic minimises
the number of times I have to get a database object, it
makes no difference to my application.
Some people use tabledef objects and indexes to get
very fast 'find' actions. To do this, they need a direct
connection to the back-end database (not CurrentDB).
Creating and destroying that connection is much slower,
and there is no existing global database object, so they
create and maintain a global database object, and do
their own check and refresh each time the object is used.
I don't use tabledef objects, because the SQL Server
equivalent is quite different, and I want to be able to use
either a SQL Server BE or a Jet MDB BE interchangeably
with minimum work. So again, I don't need a special
global database object for my BE database.
The bottom line is, I have complex queries that take 20
seconds to run, and code that takes 15 minutes to run,
and reports that take 10 minutes to run, (instead of 20
minutes, 15 hours, 10 hours), all because I have heavily
optimised everything, and I still haven't optimised out
the "set db=codedb" in my DLookup replacement, -
because it adds less than a second to that 20, and is just
one more thing that might break.
(david)

Post by Stefan Hoffmann
hi Tony,

Post by Tony Toews [MVP]
I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.

In this simple case it is just milliseconds. But in the long run, and
especially when called in a loop, it sums up to a nice amount. Maybe
seconds only, but you know just 2 cents :)
mfG
--> stefan <--

[Aaron Kempf]

Tony

you haven't noticed a performance problem?

a) it causes HANGS
b) you've got to write EXTRA CODE TO CLEAN UP
c) you're stuck with MDB if you use DAO

lose the training wheels you friggin retard

Post by Tony Toews [MVP]

Post by Mr. Smith
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim sql
Dim db As Database
Dim rs
Set db = CurrentDb
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)

I just use set rs = currentdb.openrecordset(sql). While Stefan's
approach is very likely to be more efficient I haven't noticed any
performance problems with the above.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Albert D. Kallal]

Post by Mr. Smith
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)

I use

dim rs as DAO.database

set rs = currentdb.OpenReocrdSet("SELECT * FROM tblprodouct")

As others stated, performance wise not much an issue, but how much you have
to type and code as a developer *is* an issue.

To be fair, most of the time, I will go in code:

dim rs as DAO.database
dim strSql as string

strSql = "SELECT * FROM tblprodouct"
set rs = currentdb.OpenReocrdSet(strSql)

A few things:

in you example, you left out the defining of some variables types. A
good habit is to always define the type:

eg:

dim rs
or
dim rs as DAO.database

The 2nd approach is preferred (and, that is dispute MORE typing!!!). You
want to type your variables so the compile can catch errors in your code.

set sql = currentdb.OpenRecordSet("select * from tblcustomers")

The above is wrong, and sql is a "string". However, if you don't define it
as such, then the above line of code will actually compile...and we don't
want that..

You also want to make use of option explicit..as that again will catch
type-o's

[Aaron Kempf]

I use

Dim rst as new ADODB.Recordset
rst.Open "Select name from sysobjects Where Xtype = 'u'",
CurrentProject.Connection

Post by Albert D. Kallal

Post by Mr. Smith
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)

I use
dim rs as DAO.database
set rs = currentdb.OpenReocrdSet("SELECT * FROM tblprodouct")
As others stated, performance wise not much an issue, but how much you have
to type and code as a developer *is* an issue.
dim rs as DAO.database
dim strSql as string
strSql = "SELECT * FROM tblprodouct"
set rs = currentdb.OpenReocrdSet(strSql)
in you example, you left out the defining of some variables types. A
dim rs
or
dim rs as DAO.database
The 2nd approach is preferred (and, that is dispute MORE typing!!!). You
want to type your variables so the compile can catch errors in your code.
set sql = currentdb.OpenRecordSet("select * from tblcustomers")
The above is wrong, and sql is a "string". However, if you don't define it
as such, then the above line of code will actually compile...and we don't
want that..
You also want to make use of option explicit..as that again will catch
type-o's
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

[Aaron Kempf]

DAO is obsolete

don't listen to these kids
if you're EVER trying to use a library and someone tells you that you've got
to do this-- then move to a different library

DAO is a huge memory leak; it shouldn't be used by anyone for any reason

Post by Mr. Smith
Hi.
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim sql
Dim db As Database
Dim rs
Set db = CurrentDb
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)
As I've not uptated myself lately, is there a new (or old) "killer"
approache which might work more smoothly?
Kind regards
Mr. Smith.

[Fifth Amendment]

the alternative is to use Access Data Projects

DAO is a buggy, buggy library

and any library that you've got to use .Close and Set rst = nothing??

DAO is just not a library that you should be using

Access Data Projects don't have these bugs

Post by Mr. Smith
Hi.
I'm trying to make my Access application more efficient. I think I've used
the way shown below tocommunicate with the tables in the application some
150 times through the VBA code of the different forms (changing the SQL
ofcourse).
Dim sql
Dim db As Database
Dim rs
Set db = CurrentDb
sql = "SELECT * FROM tblprodouct"
Set rs = db.OpenRecordset(sql)
As I've not uptated myself lately, is there a new (or old) "killer"
approache which might work more smoothly?
Kind regards
Mr. Smith.