temporary BackEnd database for large resultset

[Tcs]

I want to create a temporary mdb just to hold the contents of one table. (It
will contain a LOT of records.) I'm assuming that creating a temporary db and
then deleting it when I'm done will be more expedient then merely deleting
records from a local table.

My problem is that I'm having difficulty with the proper syntax. Here's my
code:

Private Function fncOpenTempDatabase()
On Error GoTo Err_fncOpenTempDatabase

Set MyTempDatabase = "AS400_CIS_temp.mdb"

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("AS400_CIS_temp.mdb") Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAcctsRecAging_Details")
intNmbrOfRecs =
CurrentDb().TableDefs("tblAcctsRecAging_Details").RecordCount
rs.Close
Set db = Nothing

Forms!frmProgressBar.Caption = "Flushing table (1) of (4) - (" & _
intNmbrOfRecs & ") records to delete THIS table..."
DoEvents
Call fncDeleteTempDatabase(MyTempDatabase)
End If

Call fncCreateTempDatabase(MyTempDatabase)

Exit_fncOpenTempDatabase:

Exit Function

Err_fncOpenTempDatabase:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncOpenTempDatabase - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncOpenTempDatabase

End Function

With the following line in:

Set MyTempDatabase = "AS400_CIS_temp.mdb"

I get a compile error..."Type mismatch". I have to remove (or comment out) this
line to get the compiler to accept my code. BUT...how do I tell Access what the
name of my temp db is, so it'll deal with it, whether or not it has to delete it
before creating it?

ALSO...I want to create a table in the new db once I have created it. I have
this code:

Private Function fncCreateTable(MyTempDatabase As Database)
On Error GoTo Err_fncCreateTable

Dim MyTabledef As TableDef

oconn.open = & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=AS400_CIS_temp.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd="

Set dbODBC = OpenDatabase("", False, False, strConnect)

Set MyTabledef = MyTempDatabase.CreateTableDef("tblAcctsRecAging_Details")

MyTabledef.Connect = dbODBC.Connect
MyTabledef.SourceTableName = "tblAcctsRecAging_Details"

With MyTabledef
.Fields.Append .CreateField("CustID", dbDecimal)
.Fields.Append .CreateField("LocID", dbDecimal)
.Fields.Append .CreateField("CustClass", dbText, 2)
.Fields.Append .CreateField("Serv", dbText, 2)
.Fields.Append .CreateField("PeriodYY", dbLong)
.Fields.Append .CreateField("PeriodMM", dbLong)
.Fields.Append .CreateField("AgeCode", dbText, 4)
.Fields.Append .CreateField("ChgType", dbText, 2)
.Fields.Append .CreateField("ChgDesc", dbText, 20)
.Fields.Append .CreateField("CurrentCount", dbLong)
.Fields.Append .CreateField("CurrentAmtBilled", dbLong)
.Fields.Append .CreateField("CurrentUnPaid", dbLong)
.Fields.Append .CreateField("30dayCount", dbLong)
.Fields.Append .CreateField("30dayAmtBilled", dbLong)
.Fields.Append .CreateField("30dayUnPaid", dbLong)
.Fields.Append .CreateField("60dayCount", dbLong)
.Fields.Append .CreateField("60dayAmtBilled", dbLong)
.Fields.Append .CreateField("60dayUnPaid", dbLong)
.Fields.Append .CreateField("90dayCount", dbLong)
.Fields.Append .CreateField("90dayAmtBilled", dbLong)
.Fields.Append .CreateField("90dayUnPaid", dbLong)
.Fields.Append .CreateField("Over90Count", dbLong)
.Fields.Append .CreateField("Over90AmtBilled", dbLong)
.Fields.Append .CreateField("Over90UnPaid", dbLong)
.Fields.Append .CreateField("DateRetrieved", dbDate)
End With


' Add table to the collection.
MyTempDatabase.TableDefs.Append MyTabledef

MyTempDatabase.Close

Exit_fncCreateTable:

Exit Function

Err_fncCreateTable:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncCreateTable - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTable

End Function

The compiler doesn't like this part:

oconn.open = & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=AS400_CIS_temp.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd="

If I change "oconn" to "strConnect" I still get the same error. "Syntax error."
I'm thinking this is probably an ADO vs DAO issue, but I'm still learning the
differences, and don't really know.

I'm trying to open the database, create my table, then close the database.
(I'll open a recordset later.)

If anyone has any fixes/thoughts/suggestions/advice, all is welcome.

Thanks in advance,

Tom

 

[Dirk Goldgar]

I want to create a temporary mdb just to hold the contents of one
table. (It will contain a LOT of records.) I'm assuming that
creating a temporary db and then deleting it when I'm done will be
more expedient then merely deleting records from a local table.

My problem is that I'm having difficulty with the proper syntax.
Here's my code:

Private Function fncOpenTempDatabase()
On Error GoTo Err_fncOpenTempDatabase

Set MyTempDatabase = "AS400_CIS_temp.mdb"

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("AS400_CIS_temp.mdb") Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAcctsRecAging_Details")
intNmbrOfRecs =
CurrentDb().TableDefs("tblAcctsRecAging_Details").RecordCount
rs.Close
Set db = Nothing

Forms!frmProgressBar.Caption = "Flushing table (1) of (4) - ("
& _ intNmbrOfRecs & ") records to delete THIS table..."
DoEvents
Call fncDeleteTempDatabase(MyTempDatabase)
End If

Call fncCreateTempDatabase(MyTempDatabase)

Exit_fncOpenTempDatabase:

Exit Function

Err_fncOpenTempDatabase:
MsgBox "Error # " & Err.Number & " was generated by " &
Err.Source & vbCrLf & Err.Description, , "RunJob -
fncOpenTempDatabase - " & Date & " - " & Time '
intRunJobLElecRpt4Andy = False Resume Exit_fncOpenTempDatabase

End Function

With the following line in:

Set MyTempDatabase = "AS400_CIS_temp.mdb"

I get a compile error..."Type mismatch". I have to remove (or
comment out) this line to get the compiler to accept my code.
BUT...how do I tell Access what the name of my temp db is, so it'll
deal with it, whether or not it has to delete it before creating it?

ALSO...I want to create a table in the new db once I have created it.
I have this code:

Private Function fncCreateTable(MyTempDatabase As Database)
On Error GoTo Err_fncCreateTable

Dim MyTabledef As TableDef

oconn.open = & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=AS400_CIS_temp.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd="

Set dbODBC = OpenDatabase("", False, False, strConnect)

Set MyTabledef =
MyTempDatabase.CreateTableDef("tblAcctsRecAging_Details")

MyTabledef.Connect = dbODBC.Connect
MyTabledef.SourceTableName = "tblAcctsRecAging_Details"

With MyTabledef
.Fields.Append .CreateField("CustID", dbDecimal)
.Fields.Append .CreateField("LocID", dbDecimal)
.Fields.Append .CreateField("CustClass", dbText, 2)
.Fields.Append .CreateField("Serv", dbText, 2)
.Fields.Append .CreateField("PeriodYY", dbLong)
.Fields.Append .CreateField("PeriodMM", dbLong)
.Fields.Append .CreateField("AgeCode", dbText, 4)
.Fields.Append .CreateField("ChgType", dbText, 2)
.Fields.Append .CreateField("ChgDesc", dbText, 20)
.Fields.Append .CreateField("CurrentCount", dbLong)
.Fields.Append .CreateField("CurrentAmtBilled", dbLong)
.Fields.Append .CreateField("CurrentUnPaid", dbLong)
.Fields.Append .CreateField("30dayCount", dbLong)
.Fields.Append .CreateField("30dayAmtBilled", dbLong)
.Fields.Append .CreateField("30dayUnPaid", dbLong)
.Fields.Append .CreateField("60dayCount", dbLong)
.Fields.Append .CreateField("60dayAmtBilled", dbLong)
.Fields.Append .CreateField("60dayUnPaid", dbLong)
.Fields.Append .CreateField("90dayCount", dbLong)
.Fields.Append .CreateField("90dayAmtBilled", dbLong)
.Fields.Append .CreateField("90dayUnPaid", dbLong)
.Fields.Append .CreateField("Over90Count", dbLong)
.Fields.Append .CreateField("Over90AmtBilled", dbLong)
.Fields.Append .CreateField("Over90UnPaid", dbLong)
.Fields.Append .CreateField("DateRetrieved", dbDate)
End With


' Add table to the collection.
MyTempDatabase.TableDefs.Append MyTabledef

MyTempDatabase.Close

Exit_fncCreateTable:

Exit Function

Err_fncCreateTable:
MsgBox "Error # " & Err.Number & " was generated by " &
Err.Source & vbCrLf & Err.Description, , "RunJob - fncCreateTable - "
& Date & " - " & Time ' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTable

End Function

The compiler doesn't like this part:

oconn.open = & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=AS400_CIS_temp.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd="

If I change "oconn" to "strConnect" I still get the same error.
"Syntax error." I'm thinking this is probably an ADO vs DAO issue,
but I'm still learning the differences, and don't really know.

I'm trying to open the database, create my table, then close the
database. (I'll open a recordset later.)

If anyone has any fixes/thoughts/suggestions/advice, all is welcome.

Thanks in advance,

Tom

Tom, there are all *kinds* of things wrong in there. First, this line

Set MyTempDatabase = "AS400_CIS_temp.mdb"

leaves me uncertain as to what type of variable MyTempDatabase is. You
show no declaration for it, so I don't know what it is. Presumable it's
either a DAO Database object, or it's a String variable. The Set
keyword implies that it's an object, but you can't set a Database object
equal to a string. If it's a String variable, on the other hand, the
Set keyword shouldn't be used.

But there are lots of other problems, made worse by the apparent fact
that you aren't declaring your variables. You should force yourself to
declare all variables in this and all existing modules by adding the
line ...

Option Explicit

.... at the top of each module, and make that happen automatically for
all new modules by checking the "Require Variable Declaration" option in
the VB Editor's Tools -> Options... dialog..

I've written a simple class object to wrap around this functionality;
maybe it would help you. Here's the code, but watch out for lines
wrapped by the newsreader:

'------ start of class module code ------
Option Compare Database
Option Explicit

' Class clsWorkDB creates a temporary work-database and allows the user
to create,
' link to, and manipulate temporary tables in the work database. A new
work database
' is created for each instance of this class. All tables are unlinked
and the work
' database is deleted when that instance is destroyed.

' Copyright 2002, Dirk Goldgar. All rights reserved.

' This class requires the services of module basTempFile, which was
written by Terry Kreft.

Private mdbWorkDB As DAO.Database ' This is the work database we'll be
using.
' We create the database file when
the class
' is instantiated and set this
reference to it.
' When the instance is destroyed, we
delete the
' file.

Public Property Get Name() As String

Name = mdbWorkDB.Name

End Property

Private Sub Class_Initialize()

Dim strWorkDBName As String
Dim strWorkDBFolder As String
Dim wrkDefault As Workspace

strWorkDBName = TempFile(False)

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

'Create a new temp database
Set mdbWorkDB = wrkDefault.CreateDatabase(strWorkDBName,
dbLangGeneral)

Set wrkDefault = Nothing

End Sub


Private Sub Class_Terminate()

Dim dbCurr As DAO.Database
Dim tdf As DAO.TableDef
Dim strWorkDBName As String
Dim strConnect As String
Dim lngT As Long

If mdbWorkDB Is Nothing Then
Exit Sub
End If

' Capture the name of the work database before closing it.
strWorkDBName = mdbWorkDB.Name

' Remove all tabledefs in the current database that are linked to
the
' work database.

Set dbCurr = DBEngine(0)(0)
dbCurr.TableDefs.Refresh
strConnect = ";DATABASE=" & strWorkDBName

With dbCurr.TableDefs
For lngT = (.Count - 1) To 0 Step -1
Set tdf = .Item(lngT)
If tdf.Connect = strConnect Then
.Delete tdf.Name
End If
Next lngT
End With
Set dbCurr = Nothing

RefreshDatabaseWindow

' Close and destroy the work database object.
mdbWorkDB.Close
Set mdbWorkDB = Nothing

' Erase the work database file.
Kill strWorkDBName

End Sub

Public Sub MakeWorkTable(TableName As String, TemplateName As String)

' Create a table in the work database and a table linked to it in
the current database,
' modeled on a specified, existing table.

Dim dbCurr As DAO.Database
Dim tdf As DAO.TableDef
Dim lngError As Long

' Check whether the table to be created exists in the current
database, and if so
' whether it is a linked table.
Set dbCurr = DBEngine(0)(0)
dbCurr.TableDefs.Refresh
On Error Resume Next
Set tdf = dbCurr(TableName)
lngError = Err.Number
On Error GoTo 0

Select Case lngError
Case 0
' The table exists. If it's a linked table, we are willing
to delete it.
If Len(tdf.Connect) > 0 Then
dbCurr.TableDefs.Delete TableName
Else
Set tdf = Nothing
Set dbCurr = Nothing
Err.Raise 5, "Can't create work table '" & TableName & _
"' -- this table already exists in the
current database."
End If
Case 3265
' The table doesn't exist. That's what we hoped.
Case Else
' Some other error occurred, which is going to mess us up.
Set tdf = Nothing
Set dbCurr = Nothing
Err.Raise 5, "Can't create work table '" & TableName & _
"' -- error " & lngError & " accessing
table in the current database."
End Select
Set tdf = Nothing

' Create the temp table in the work database
DoCmd.TransferDatabase acExport, "Microsoft Access", mdbWorkDB.Name,
acTable, TemplateName, TableName, True
Application.Echo True ' Force Access to clear "Verifying system
objects ..." status bar message

mdbWorkDB.TableDefs.Refresh

' Create a linked table in this database, linked to the table in the
work database.
Set tdf = dbCurr.CreateTableDef(TableName)
tdf.Connect = ";DATABASE=" & mdbWorkDB.Name
tdf.SourceTableName = TableName
dbCurr.TableDefs.Append tdf
Set tdf = Nothing
dbCurr.TableDefs.Refresh

RefreshDatabaseWindow


MakeWorkTable_Exit:
Set dbCurr = Nothing
Exit Sub

End Sub
'------ end of class module code ------

This class module requires the support of the TempFile code (as
separate, standard module) written by Terry Kreft and posted here:

http://www.mvps.org/access/api/api0014.htm

I found it easier to create my temporary table in the database by
copying the structure of an existing table that I use as a template. If
I don't have a real, working table in my database that I want to use as
a model, I create an empty one and save it just for the purpose of
serving as the template.

 

[Tcs]

At the begining of the module...

Public MyTempDatabase As Database

But there are lots of other problems, made worse by the apparent fact
that you aren't declaring your variables. You should force yourself to
declare all variables in this and all existing modules by adding the
line ...

Option Explicit

I did. There are more functions to this module:

Option Compare Database
Option Explicit

Public intPBOverallMax As Variant, intPBOverallValue As Variant,
intPBOverallCntr As Variant
Public intPBTaskMax As Variant, intPBTaskValue As Variant, intPBTaskCntr As
Variant
Public ctlProgBarOverall As String, ctlProgBarTask As String
Public intLoop1 As Variant, intNmbrOfLoops As Variant, intNmbrOfRecs As
Variant
Public intLoop2 As Variant
Public db As Database, rs As Recordset, tdfAccess As TableDef, qdf As
QueryDef
Public strSQLinsert1 As String, strSQLselect1 As String, strSQLfrom1 As
String
Public strSQLwhere1 As String, strSQLwhere1a As String, strSQLorderby1 As
String
Public strSQLstatement1 As String, strAgeGroup As String
Public int1stMM As Variant, int1stYY As Variant
Public int2ndMM As Variant, int2ndYY As Variant
Public strWhichAmtBilled As String, strWhichAmtUnpaid As String
Public strSQLinsert2 As String, strSQLselect2 As String, strSQLfrom2 As
String
Public strSQLon2 As String, strSQLwhere2 As String, strSQLin2 As String
Public strSQLorderby2 As String
Public strSQLstatement2 As String
Public StartTime As Variant, StopTime As Variant
Public ElapsedTime As Variant, ElapsedSeconds As Variant
Public strElapsedTime As String
Public DefaultWorkspace As Workspace, dbConn, fso
Public CurrentDatabase As Database, MyTempDatabase As Database

Public Function fncRunJobAcctsRecAging()
Private Function fncAcctsRecAgingPeriods()
Private Function fncSQLgetTbl420Trans()
Private Function fncSQLgetTbl420WOTrans()
Private Function fncSQLgetTbl400Trans()
Private Function fncSQLgetTbl400WOTrans()
Private Function fncSQLgetTbl460WOTrans()
Private Function fncGetMissingClassRates()
Private Function fncOpenTempDatabase()
Private Function fncCreateTempDatabase(MyTempDatabase As Database)
Private Function fncDeleteTempDatabase(MyTempDatabase As Database)
Private Function fncCreateTable(MyTempDatabase As Database)
Private Function fncLogRunHistory()
Private Function fncUpdateProgressBar()

... at the top of each module, and make that happen automatically for
all new modules by checking the "Require Variable Declaration" option in
the VB Editor's Tools -> Options... dialog..

Already checked.

This class module requires the support of the TempFile code (as
separate, standard module) written by Terry Kreft and posted here:

http://www.mvps.org/access/api/api0014.htm

Got it.

Thanks.

What is meant by "class"? Here are the current modules in my db:

mdlAcctsRecAging
mdlElecRpt4Andy
mdlExcelTest
mdlLocationsWithMeters
mdlLocationsWithServices
mdlMtrRdgSequenceRpt
mdlODBCTables
mdl_MainProcedure

According to what my books say, the term "class" was true in Access 97, and has
been replaced by "form and report". In any event, I THOUGHT that I was being
organized. No?

Thanks for the help.

 

[Dirk Goldgar]

(answers and comments inline)

At the begining of the module...

Public MyTempDatabase As Database

I did. There are more functions to this module:

Option Compare Database
Option Explicit

Public intPBOverallMax As Variant, intPBOverallValue As Variant,
intPBOverallCntr As Variant
Public intPBTaskMax As Variant, intPBTaskValue As Variant,
intPBTaskCntr As Variant
Public ctlProgBarOverall As String, ctlProgBarTask As String
Public intLoop1 As Variant, intNmbrOfLoops As Variant,
intNmbrOfRecs As Variant
Public intLoop2 As Variant
Public db As Database, rs As Recordset, tdfAccess As TableDef,
qdf As QueryDef
Public strSQLinsert1 As String, strSQLselect1 As String,
strSQLfrom1 As String
Public strSQLwhere1 As String, strSQLwhere1a As String,
strSQLorderby1 As String
Public strSQLstatement1 As String, strAgeGroup As String
Public int1stMM As Variant, int1stYY As Variant
Public int2ndMM As Variant, int2ndYY As Variant
Public strWhichAmtBilled As String, strWhichAmtUnpaid As String
Public strSQLinsert2 As String, strSQLselect2 As String,
strSQLfrom2 As String
Public strSQLon2 As String, strSQLwhere2 As String, strSQLin2 As
String Public strSQLorderby2 As String
Public strSQLstatement2 As String
Public StartTime As Variant, StopTime As Variant
Public ElapsedTime As Variant, ElapsedSeconds As Variant
Public strElapsedTime As String
Public DefaultWorkspace As Workspace, dbConn, fso
Public CurrentDatabase As Database, MyTempDatabase As Database

Yow! That's a lot of Public, module-level variables! I don't know your
reasons, but I can't help wondering whether you really need so many.
IMO, public variables should be used sparingly, and module-level Private
variables should be used judiciously. I'm especially concerned by the
number of module-level object variables. You'd better be very sure you
set those all to Nothing before you try to exit your application, or you
may find that Access won't really terminate.

Already checked.

I see I wronged you there. It didn't occur to me you'd have all those
variables declared at the module level.

Got it.

Thanks.

What is meant by "class"?

A class module defines a programming object of which one or more
instances can be created, used, and destroyed.

Here are the current modules in my db:

mdlAcctsRecAging
mdlElecRpt4Andy
mdlExcelTest
mdlLocationsWithMeters
mdlLocationsWithServices
mdlMtrRdgSequenceRpt
mdlODBCTables
mdl_MainProcedure

I can't tell anything about them from their names alone, but if you
didn't create them as class modules they are probably plain old
"standard" modules.

According to what my books say, the term "class" was true in Access
97, and has been replaced by "form and report".

I have no idea what it means to say "the term 'class' was true". Form
and report modules are special cases of class modules, but starting with
Access 97 we became able to define our own class modules, not associated
with a form or report. Class modules certainly haven't been "replaced"
by anything.

In any event, I
THOUGHT that I was being organized. No?

<g> It's too soon to tell.

 

[Tcs]

(answers and comments inline)

Yow! That's a lot of Public, module-level variables! I don't know your
reasons, but I can't help wondering whether you really need so many.

How do I get my job done withOUT them?

I would say that most of them are used in more than one function/sub. And it
was my understanding that under these conditions, I *had* to make them *public*.

IMO, public variables should be used sparingly, and module-level Private
variables should be used judiciously. I'm especially concerned by the
number of module-level object variables. You'd better be very sure you
set those all to Nothing before you try to exit your application, or you
may find that Access won't really terminate.

What's a *module-level* variable? One like mine? Declared at the top of the
module for use by any function/sub within that module? (As opposed to a
variable defined in just say, a function/sub?

I see I wronged you there. It didn't occur to me you'd have all those
variables declared at the module level.


A class module defines a programming object of which one or more
instances can be created, used, and destroyed.


I can't tell anything about them from their names alone, but if you
didn't create them as class modules they are probably plain old
"standard" modules.

Okay. I'll bite. Since I still don't really understand what you mean by
*class*, I have to ask, and what's a *standard* module?

I have no idea what it means to say "the term 'class' was true". Form
and report modules are special cases of class modules, but starting with
Access 97 we became able to define our own class modules, not associated
with a form or report. Class modules certainly haven't been "replaced"
by anything.

From what I read in the book, *I* understood it to mean that what was once
called *class*, is *now*...*properly*...called *form and report*. I stand
corrected.

Hey, I'm just going by what I read. I admit it, I'm still learning. (And at my
current rate, will NEVER make it to be an Access guru. Not before I die,
anyway. But I digress...) Almost every day it seems as if there is more that I
*don't* know, rather than that I'm actually increasing my level of knowledge.
And I'm still fuzzy on DAO vs ADO...

<g> It's too soon to tell.

Do you know of any book(s), website(s) or other that you would recommend which
will help me learn? Perhaps some book on *VB/VBA* as opposed to *Access*?
And/or *DAO*? Or just keep hacking away and using the newsgroups when/as
necessary?

Thanks a lot,

Tom

 

[Dirk Goldgar]

(answers and comments inline)

How do I get my job done withOUT them?

I would say that most of them are used in more than one function/sub.
And it was my understanding that under these conditions, I *had* to
make them *public*.

If they are used in more than one sub *in the same module*, but not
shared between modules, then you can declare them at the module level
but not make them Public -- use the Private keyword or don't use either
keyword, and the variables will default to Private. Private variables
declared at the module level are visible to all procedures in that
module, but not to procedures in other modules.

Just to clarify (and lecture a bit), we're really talking about
different attributes of variables: scope and lifetime. Scope
determines the visibility of the variables, and lifetime determines when
storage is actually allocated for the variable, and how long it persists
and holds its value.

---- begin lecture ----
There are three different possible scopes: procedure-level, private
module-level, and public module-level. Variables declared inside
procedures have procedure-level scope; that is, they are visible only
inside that procedure. Variables declared at the module level with the
Private keyword, or with no Public/Private keyword, have private
module-level scope -- they are visible to all procedures inside that
module but not to other modules. Variables declared at the module level
with the Public keyword have public module-level scope; that is, they
are visible to procedures in all modules in the project. Just to
confuse matters slightly, the defaults are reversed for the scope of
procedure names: a procedure defined in a standard module (as opposed
to a class module) is Public by default, and only Private if you use the
kwyword in its declaration.

Lifetime is a different attribute, though it normally relates to scope.
Unless you use the Static keyword, any variable declared inside a
procedure is created and initialized when that procedure is called,
holds its value while the procedure exits, and then is destroyed. The
Static keyword causes the variable to hold its value as long as the
project is loaded, so that it may be visible only inside the procedure
but retains its value between calls to the procedure. Variables
declared at the module level, regardless of visibility, are created when
the module is first loaded and retain their values so long as the module
is loaded and the project is not reset.

For standard modules, Access loads the module the first time any
procedure or variable within it is referred to, and doesn't unload the
module thereafter until you close the database or something happens to
rest the project. Thus, module-level variables declared in standard
modules have a lifetime that begins when the module is first needed and
lasts (normally) until you close the database. Module-level variables
declared in class modules are created for each instance of the class --
they are created when the instance is created, and destroyed when the
instance is destroyed.
---- end lecture ----

Generally speaking, module-level variables are more fragile than
procedure-level variables, because an unhandled error in any procedure
can cause your project to be reset and the variables to lose their
values. They also expose the inner working of procedures to the outside
world (well, to other procedures in the same module, in the case of
Private variables, and to other procedures in other modules, in the case
of Public variables). That's fine when that is the whole purpose of the
variable, but it's not fine if all you're trying to do is define a work
variable to be used in multiple places. Doing that is asking for
trouble, as some day you can bet that one procedure is going to step on
the value of a module-level variable that is currently being used by
another procedure.

So if you are using module-level variables to preserve values that are
going to be used by multiple procedures, or if you're using them to
communicate between procedures in a way that couldn't be handled well by
passing arguments, then all is well. You just want to limit your
exposure by not making things Public that could just as well be Private.
But if you're using module-level variables just to save yourself the
trouble of declaring work variables inside the procedures where they'll
be used, then I think you're making a mistake. "Keep local things
local" is the rule.

What's a *module-level* variable? One like mine? Declared at the
top of the module for use by any function/sub within that module?
(As opposed to a variable defined in just say, a function/sub?
Yes.


Okay. I'll bite. Since I still don't really understand what you
mean by *class*, I have to ask, and what's a *standard* module?

A class module is a template for a self-contained module that may define
its own private and public variables, properties, and methods
(procedures). Before you can use a class module, you have to use code
to create an "instance" of it, like this:

' Declare an instance object of my class
Dim objMySpecialClass As clsMySpecialClass

' Create the instance and set a reference to it
Set objMySpecialClass = New clsMySpecialClass

' Do things with this instance
objMySpecialClass.SomeProperty = "foo"
objMySpecialClass.DoSomething ' call a method

' Destroy it when I'm done
Set objMySpecialClass = Nothing

Although this sort of syntax may seem more cumbersome than working with
standard modules, it provides some advantages: (1) encapsulation -- all
data and code that belongs to a conceptual "object" is defined and
stored with it, and not visible to the outside world except where the
class designer intends it to be; and (2) reusability -- you can create
multiple instances of a class and have them all active at once without
getting in each others' way.

By contrast, there can only ever be one instance of a standard module.
Because of that, you don't need any special code to create that
instance -- it's loaded into memory the first time you refer to anything
defined in it -- and you don't need to use a reference to the module to
qualify references to the variables or procedures it contains. Both
standard modules and class modules may be shown on the Modules tab of
the database window. When you create modules in the database window by
clicking the "New" button on the container window, the modules you
create are standard modules. However, if you use the "New Object"
button on the toolbar or the menu item "Insert", among the objects you
can create are both Modules (standard modules) and Class Modules.

From what I read in the book, *I* understood it to mean that what was
once called *class*, is *now*...*properly*...called *form and
report*. I stand corrected.

In Access 95, form and report modules were the only class modules that
Access supported. Possibly the book was trying to say that, with the
introduction of user-defined class modules in Access 97, statements
formerly made about class modules should be revised to specifically
refer to form and report modules, which are now a subset of the
available class modules.

Hey, I'm just going by what I read. I admit it, I'm still learning.
(And at my current rate, will NEVER make it to be an Access guru.
Not before I die, anyway. But I digress...) Almost every day it
seems as if there is more that I *don't* know, rather than that I'm
actually increasing my level of knowledge.

I know how you feel.

And I'm still fuzzy on DAO vs ADO...

That's a topic for a whole different thread. If you haven't tried
searching Google Groups (http://groups.google.com) on the subject,
though, you might give it a go.

Do you know of any book(s), website(s) or other that you would
recommend which will help me learn? Perhaps some book on *VB/VBA* as
opposed to *Access*? And/or *DAO*? Or just keep hacking away and
using the newsgroups when/as necessary?

Well, of course there's the Access Web (www.mvps.org/access) and a host
of sites that are linked to from there. For books on VBA as it relates
to Access, I've heard good reports of _Beginning Access <version> VBA_
by Smith and Sussman (Wrox Press, I think). People have also mentioned
_Microsoft Access Power Programming_ by F. Scott Barker, but I don't
know where it fits on the spectrum. For a developer, the best Access
book I've ever seen is _Microsoft Access <version> Developer's Handbook_
by Getz et. al. (Sybex). That's on the technical side, though. There
are a number of other good Access books, but if you're looking
specifically for help with coding those are the ones that come to mind.
Use Google Groups to search for other suggestions.