Object variable or With block variable not set?

B

Bill

In the code segment below, I get an Run-time
error '91' the first time I make a reference to the
fields collection of the TableDefs object variable
tbfInstProp when the reference is not within the
current sub.

(Err91: Object variable or With block variable not set)

That is, if I move the code you see at the beginning
of sub InitVer7pt1 immediately after:

Set tdfInstProp = dbTMS.TableDefs("InstProperties")

Then that code runs as expected, looping through the
fields collection of the TableDef.

I guess the question is how come the scope of
tblInstProp doesn't extend to subs within the general
module?
===============================================
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO recordset
Dim fldName As DAO.Field 'Ojbect variable for members of the field
collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties table
definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example()

IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name TMS
backend DB
Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open backend DB
Set rsInstProp = dbTMS.OpenRecordset("InstProperties") 'Default is Dynaset
Set tdfInstProp = dbTMS.TableDefs("InstProperties") 'Need to look at
InstProp tbldefs

Call InitVer7pt1 '7.1 is the 1st version of field upgradable mdb's.

..
..
..
..
..
Set tdfInstProp = Nothing
'Close the properties table recordset.
rsInstProp.Close 'Close the recordset
Set rsInstProp = Nothing 'Free up its memory

dbTMS.Close 'Close TMS backend DB
Set dbTMS = Nothing 'Free up its memory

If IPDBVersion < TMSDBVersion Then Call Upgrade


End Sub
----------------------------------------------------------------
Private Sub InitVer7pt1()

booNotFound = True

For Each fldName In tdfInstProp.Fields 'For each field in the
fields collection
If fldName.Name = "InstDBVersion" Then 'This the field we're
looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done searching the
collection
End If
Next fldName 'NO, step to next field
..
..
..
..

===============================================
 
D

Dirk Goldgar

Bill said:
In the code segment below, I get an Run-time
error '91' the first time I make a reference to the
fields collection of the TableDefs object variable
tbfInstProp when the reference is not within the
current sub.

(Err91: Object variable or With block variable not set)

That is, if I move the code you see at the beginning
of sub InitVer7pt1 immediately after:

Set tdfInstProp = dbTMS.TableDefs("InstProperties")

Then that code runs as expected, looping through the
fields collection of the TableDef.

I guess the question is how come the scope of
tblInstProp doesn't extend to subs within the general
module?
===============================================
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO
recordset Dim fldName As DAO.Field 'Ojbect variable for
members of the field collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties
table definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example()

IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name
TMS backend DB
Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open
backend DB Set rsInstProp = dbTMS.OpenRecordset("InstProperties")
'Default is Dynaset Set tdfInstProp =
dbTMS.TableDefs("InstProperties") 'Need to look at InstProp
tbldefs
Call InitVer7pt1 '7.1 is the 1st version of field upgradable
mdb's.
.
.
.
.
.
Set tdfInstProp = Nothing
'Close the properties table recordset.
rsInstProp.Close 'Close the recordset
Set rsInstProp = Nothing 'Free up its memory

dbTMS.Close 'Close TMS backend DB
Set dbTMS = Nothing 'Free up its memory

If IPDBVersion < TMSDBVersion Then Call Upgrade


End Sub
----------------------------------------------------------------
Private Sub InitVer7pt1()

booNotFound = True

For Each fldName In tdfInstProp.Fields 'For each field in
the fields collection
If fldName.Name = "InstDBVersion" Then 'This the field
we're looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done
searching the collection
End If
Next fldName 'NO, step to next
field .
.
.
.

===============================================
Click to expand...

When I copy your code to my own database, modify it in a minor way to
use my own external DB and table, and run it, it works fine. Therefore,
I'm inclined to believe that there's something going on in the code
you've omitted that is destroying your database object.
 
B

Bill

Dirk,
Here's the whole module (Below). (I'm in the midst of
modifying and existing module, so I don't expect the
code as it stands to run to completion, only to the
point in sub InitVer7pt1 where I've put the stop.)

I PUT MY CURSOR on statement:

If booNotFound = True Then

in sub InitVer7pt1 and "run to cursor"

I get the Run-time error when I hit the statement:

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection

Debug shows fldName as Nothing and tdfInstProp!Fields
shows the error message as its current value.

===(NOTE: I have not yet finished modifying code beyond cursor stop
point)======
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO recordset
Dim fldName As DAO.Field 'Ojbect variable for members of the field
collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties table
definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example()
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here's an example of how to define and process a table using a DAO
Recordset.
' It is intended to mimic the LoadInstProperties logic, only using DAO
exclusively.
' That code logic attends to the setting of our set of Global variables and
checks
' to see if the backend DB is at a version level consistent with the current
version
' of TMS.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2 'Global TMS version
TMSDBVersion = 7.1 'Global (This ONLY changes when a TableDef
changes.)

IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name TMS
backend DB

Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open backend DB
Set rsInstProp = dbTMS.OpenRecordset("InstProperties") 'Default is Dynaset
Set tdfInstProp = dbTMS.TableDefs("InstProperties") 'Need to look at
InstProp tbldefs

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Call InitVer7pt1 '7.1 is the 1st version of field upgradable mdb's.

'==========================================================================
' This routine fetches the "Installation Properties" table and loads the
' corresponding global variables.
'==========================================================================

'Open the table containing the donation key.
' stSQL = "SELECT * FROM [InstProperties]"
' Set rsInstProp = New ADODB.Recordset
' rsInstProp.Open stSQL, con, adOpenKeyset, adLockOptimistic

If IsNull(rsInstProp!InstDBVersion) Then 'Null if we've just upgraded
rsInstProp!InstDBVersion = 7.1

If Len(IPAddress & "") > 0 Then
rsInstProp!InstAddress = IPAddress
Else
rsInstProp!InstAddress = "Please enter" 'User ignored prompt
End If

If Len(IPCityState & "") > 0 Then
rsInstProp!InstCityState = IPCityState
Else
rsInstProp!InstCityState = "Please enter" 'User ignored prompt
End If

rsInstProp.Update
End If

IPDBVersion = rsInstProp!InstDBVersion
IPName = rsInstProp!InstName
IPAcronym = rsInstProp!InstAcronym
IPPath = rsInstProp!InstPath
IPDatabase = rsInstProp!InstDatabase
IPImages = rsInstProp!InstImages
IPEmail = rsInstProp![InstE-mail]
IPAddress = rsInstProp!InstAddress
IPCityState = rsInstProp!InstCityState
IPPhone = rsInstProp!InstPhone
IPMDBRecip = rsInstProp![InstMDB-Recip]
IPRecipSubj = rsInstProp!InstRecipSubj
IPRecipMsg = rsInstProp!InstRecipMsg

'MsgBox IPName & " " & IPPhone

'Close the properties table recordset.
rsInstProp.Close 'Close the recordset
Set rsInstProp = Nothing 'Free up its memory

dbTMS.Close 'Close TMS backend DB
Set dbTMS = Nothing 'Free up its memory

If IPDBVersion < TMSDBVersion Then Call Upgrade


End Sub
Private Sub InitVer7pt1()

'==========================================================================
' TMS Version 7.1 is the first version of TMS where TableDef's are upgraded
' in the field. If the current DB is found to be without the InstDBVersion
' field within the Installation Properties, then DB needs to be upgraded to
' compatibility with at least version 7.1. THEN, after the Installation
' Properties are loaded in the main code (above), there will be checks to
' determine if further upgrades are required.
'==========================================================================

booNotFound = True

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection
If fldName.Name = "InstDBVersion" Then 'This the field we're
looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done searching the
collection
End If
Next fldName 'NO, step to next field


If booNotFound = True Then 'If version missing, so
are the others.
Set fldName = tdfInstProp.CreateField("InstDBVersion", dbSingle)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstAddress", dbText)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstCityState", dbText)
tdfInstProp.Fields.Append fldName

tdfInstProp.Fields.Refresh

IPAddress = InputBox("Your database has been updated to include two
new" & vbNewLine _
& "fields that are required for donation
statements" & vbNewLine _
& "suitable for submission to the IRS for tax
purposes." & vbNewLine & vbNewLine _
& "Please enter the mailing address of your
installation." & vbNewLine _
& "[We'll prompt for city, state and zip
momentarily.]")

IPCityState = InputBox("And now, the city, state zip of your
installation")
End If

Set fldName = Nothing
Set tdfInstProp = Nothing

'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache
'force the database to update what it knows about the tables:
dbTMS.TableDefs.Refresh

End Sub
Private Sub Upgrade()
MsgBox "Database at version " & IPDBVersion & "TMS requires it be at " &
TMSDBVersion
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' New code is required here to perform necessary upgrades from known
versions.
' That means when TMS code requires an update to any TableDefs that code be
' inserted here to accomplish the modifications. For example, say we update
' TMS to version 8.3 and that version requires TableDef changes. We set
' TMSDBVersion to 8.3 and write the necessary code to modify the table
definitions
' accordingly and update the IPDBVersion of the database to match.
'
' Everytime such a change is necessary we iterate the code blocks until
we've
' taken the current back-end database incrementally to the highest level,
where
' each block takes us from one version to the next and so on.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
End Sub

===================================================
 
B

Bill

Interesting! If I let the module run to completion (I put
the cursor at the "End Sub" of Sub DAO_Example) I don't
get any error messages and all 13 of the global variables you
see in DAO_Example have proper values and the object
variables have been freed.

It's only when I put the cursor at the statement:

If booNotFound = True Then

and run to cursor does the error message come up.

I thought I'd learned my lessons well enough to begin
using DAO, but now I'm not so sure???

Bill

Bill said:
Dirk,
Here's the whole module (Below). (I'm in the midst of
modifying and existing module, so I don't expect the
code as it stands to run to completion, only to the
point in sub InitVer7pt1 where I've put the stop.)

I PUT MY CURSOR on statement:

If booNotFound = True Then

in sub InitVer7pt1 and "run to cursor"

I get the Run-time error when I hit the statement:

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection

Debug shows fldName as Nothing and tdfInstProp!Fields
shows the error message as its current value.

===(NOTE: I have not yet finished modifying code beyond cursor stop
point)======
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO recordset
Dim fldName As DAO.Field 'Ojbect variable for members of the
field collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties
table definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example()
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here's an example of how to define and process a table using a DAO
Recordset.
' It is intended to mimic the LoadInstProperties logic, only using DAO
exclusively.
' That code logic attends to the setting of our set of Global variables
and checks
' to see if the backend DB is at a version level consistent with the
current version
' of TMS.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2 'Global TMS version
TMSDBVersion = 7.1 'Global (This ONLY changes when a TableDef
changes.)

IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name TMS
backend DB

Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open backend
DB
Set rsInstProp = dbTMS.OpenRecordset("InstProperties") 'Default is
Dynaset
Set tdfInstProp = dbTMS.TableDefs("InstProperties") 'Need to look at
InstProp tbldefs

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Call InitVer7pt1 '7.1 is the 1st version of field upgradable
mdb's.

'==========================================================================
' This routine fetches the "Installation Properties" table and loads the
' corresponding global variables.
'==========================================================================

'Open the table containing the donation key.
' stSQL = "SELECT * FROM [InstProperties]"
' Set rsInstProp = New ADODB.Recordset
' rsInstProp.Open stSQL, con, adOpenKeyset, adLockOptimistic

If IsNull(rsInstProp!InstDBVersion) Then 'Null if we've just upgraded
rsInstProp!InstDBVersion = 7.1

If Len(IPAddress & "") > 0 Then
rsInstProp!InstAddress = IPAddress
Else
rsInstProp!InstAddress = "Please enter" 'User ignored prompt
End If

If Len(IPCityState & "") > 0 Then
rsInstProp!InstCityState = IPCityState
Else
rsInstProp!InstCityState = "Please enter" 'User ignored prompt
End If

rsInstProp.Update
End If

IPDBVersion = rsInstProp!InstDBVersion
IPName = rsInstProp!InstName
IPAcronym = rsInstProp!InstAcronym
IPPath = rsInstProp!InstPath
IPDatabase = rsInstProp!InstDatabase
IPImages = rsInstProp!InstImages
IPEmail = rsInstProp![InstE-mail]
IPAddress = rsInstProp!InstAddress
IPCityState = rsInstProp!InstCityState
IPPhone = rsInstProp!InstPhone
IPMDBRecip = rsInstProp![InstMDB-Recip]
IPRecipSubj = rsInstProp!InstRecipSubj
IPRecipMsg = rsInstProp!InstRecipMsg

'MsgBox IPName & " " & IPPhone

'Close the properties table recordset.
rsInstProp.Close 'Close the recordset
Set rsInstProp = Nothing 'Free up its memory

dbTMS.Close 'Close TMS backend DB
Set dbTMS = Nothing 'Free up its memory

If IPDBVersion < TMSDBVersion Then Call Upgrade


End Sub
Private Sub InitVer7pt1()

'==========================================================================
' TMS Version 7.1 is the first version of TMS where TableDef's are
upgraded
' in the field. If the current DB is found to be without the InstDBVersion
' field within the Installation Properties, then DB needs to be upgraded
to
' compatibility with at least version 7.1. THEN, after the Installation
' Properties are loaded in the main code (above), there will be checks to
' determine if further upgrades are required.
'==========================================================================

booNotFound = True

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection
If fldName.Name = "InstDBVersion" Then 'This the field we're
looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done searching
the collection
End If
Next fldName 'NO, step to next field


If booNotFound = True Then 'If version missing, so
are the others.
Set fldName = tdfInstProp.CreateField("InstDBVersion", dbSingle)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstAddress", dbText)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstCityState", dbText)
tdfInstProp.Fields.Append fldName

tdfInstProp.Fields.Refresh

IPAddress = InputBox("Your database has been updated to include two
new" & vbNewLine _
& "fields that are required for donation
statements" & vbNewLine _
& "suitable for submission to the IRS for tax
purposes." & vbNewLine & vbNewLine _
& "Please enter the mailing address of your
installation." & vbNewLine _
& "[We'll prompt for city, state and zip
momentarily.]")

IPCityState = InputBox("And now, the city, state zip of your
installation")
End If

Set fldName = Nothing
Set tdfInstProp = Nothing

'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache
'force the database to update what it knows about the tables:
dbTMS.TableDefs.Refresh

End Sub
Private Sub Upgrade()
MsgBox "Database at version " & IPDBVersion & "TMS requires it be at " &
TMSDBVersion
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' New code is required here to perform necessary upgrades from known
versions.
' That means when TMS code requires an update to any TableDefs that code
be
' inserted here to accomplish the modifications. For example, say we
update
' TMS to version 8.3 and that version requires TableDef changes. We set
' TMSDBVersion to 8.3 and write the necessary code to modify the table
definitions
' accordingly and update the IPDBVersion of the database to match.
'
' Everytime such a change is necessary we iterate the code blocks until
we've
' taken the current back-end database incrementally to the highest level,
where
' each block takes us from one version to the next and so on.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
End Sub

===================================================






Dirk Goldgar said:
When I copy your code to my own database, modify it in a minor way to use
my own external DB and table, and run it, it works fine. Therefore, I'm
inclined to believe that there's something going on in the code you've
omitted that is destroying your database object.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
B

Bill

Continuing to experiment with DAO and the DAO_Example
module, I deleted the 3 fields that the InitVer7pt1 sub looks
for and the creates & appends the new fields if the key field
is missing (booNotFound=True).

I get a RT Error 3211 at the first "Append":

tdfInstProp.Fields.Append fldName

because the DB engine couldn't lock the table. I have no idea
why not?????

Bill

Bill said:
Dirk,
Here's the whole module (Below). (I'm in the midst of
modifying and existing module, so I don't expect the
code as it stands to run to completion, only to the
point in sub InitVer7pt1 where I've put the stop.)

I PUT MY CURSOR on statement:

If booNotFound = True Then

in sub InitVer7pt1 and "run to cursor"

I get the Run-time error when I hit the statement:

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection

Debug shows fldName as Nothing and tdfInstProp!Fields
shows the error message as its current value.

===(NOTE: I have not yet finished modifying code beyond cursor stop
point)======
Option Compare Database
Option Explicit
Dim dbTMS As DAO.Database 'Object variable for our database
Dim rsInstProp As DAO.Recordset 'Object variable for our DAO recordset
Dim fldName As DAO.Field 'Ojbect variable for members of the
field collection
Dim tdfInstProp As DAO.TableDef 'Object variable for InstProperties
table definition
Dim stSQL As String 'Working string for our SQL queries
Dim booNotFound As Boolean

Public Sub DAO_Example()
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here's an example of how to define and process a table using a DAO
Recordset.
' It is intended to mimic the LoadInstProperties logic, only using DAO
exclusively.
' That code logic attends to the setting of our set of Global variables
and checks
' to see if the backend DB is at a version level consistent with the
current version
' of TMS.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2 'Global TMS version
TMSDBVersion = 7.1 'Global (This ONLY changes when a TableDef
changes.)

IPDatabase = DLookup("InstDatabase", "InstProperties") 'Fetch name TMS
backend DB

Set dbTMS = OpenDatabase(IPDatabase) 'Ok. Open backend
DB
Set rsInstProp = dbTMS.OpenRecordset("InstProperties") 'Default is
Dynaset
Set tdfInstProp = dbTMS.TableDefs("InstProperties") 'Need to look at
InstProp tbldefs

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Call InitVer7pt1 '7.1 is the 1st version of field upgradable
mdb's.

'==========================================================================
' This routine fetches the "Installation Properties" table and loads the
' corresponding global variables.
'==========================================================================

'Open the table containing the donation key.
' stSQL = "SELECT * FROM [InstProperties]"
' Set rsInstProp = New ADODB.Recordset
' rsInstProp.Open stSQL, con, adOpenKeyset, adLockOptimistic

If IsNull(rsInstProp!InstDBVersion) Then 'Null if we've just upgraded
rsInstProp!InstDBVersion = 7.1

If Len(IPAddress & "") > 0 Then
rsInstProp!InstAddress = IPAddress
Else
rsInstProp!InstAddress = "Please enter" 'User ignored prompt
End If

If Len(IPCityState & "") > 0 Then
rsInstProp!InstCityState = IPCityState
Else
rsInstProp!InstCityState = "Please enter" 'User ignored prompt
End If

rsInstProp.Update
End If

IPDBVersion = rsInstProp!InstDBVersion
IPName = rsInstProp!InstName
IPAcronym = rsInstProp!InstAcronym
IPPath = rsInstProp!InstPath
IPDatabase = rsInstProp!InstDatabase
IPImages = rsInstProp!InstImages
IPEmail = rsInstProp![InstE-mail]
IPAddress = rsInstProp!InstAddress
IPCityState = rsInstProp!InstCityState
IPPhone = rsInstProp!InstPhone
IPMDBRecip = rsInstProp![InstMDB-Recip]
IPRecipSubj = rsInstProp!InstRecipSubj
IPRecipMsg = rsInstProp!InstRecipMsg

'MsgBox IPName & " " & IPPhone

'Close the properties table recordset.
rsInstProp.Close 'Close the recordset
Set rsInstProp = Nothing 'Free up its memory

dbTMS.Close 'Close TMS backend DB
Set dbTMS = Nothing 'Free up its memory

If IPDBVersion < TMSDBVersion Then Call Upgrade


End Sub
Private Sub InitVer7pt1()

'==========================================================================
' TMS Version 7.1 is the first version of TMS where TableDef's are
upgraded
' in the field. If the current DB is found to be without the InstDBVersion
' field within the Installation Properties, then DB needs to be upgraded
to
' compatibility with at least version 7.1. THEN, after the Installation
' Properties are loaded in the main code (above), there will be checks to
' determine if further upgrades are required.
'==========================================================================

booNotFound = True

For Each fldName In tdfInstProp!Fields 'For each field in the
fields collection
If fldName.Name = "InstDBVersion" Then 'This the field we're
looking for?
booNotFound = False 'YES, set
not-found-indicator to false
Exit For 'We're done searching
the collection
End If
Next fldName 'NO, step to next field


If booNotFound = True Then 'If version missing, so
are the others.
Set fldName = tdfInstProp.CreateField("InstDBVersion", dbSingle)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstAddress", dbText)
tdfInstProp.Fields.Append fldName

Set fldName = tdfInstProp.CreateField("InstCityState", dbText)
tdfInstProp.Fields.Append fldName

tdfInstProp.Fields.Refresh

IPAddress = InputBox("Your database has been updated to include two
new" & vbNewLine _
& "fields that are required for donation
statements" & vbNewLine _
& "suitable for submission to the IRS for tax
purposes." & vbNewLine & vbNewLine _
& "Please enter the mailing address of your
installation." & vbNewLine _
& "[We'll prompt for city, state and zip
momentarily.]")

IPCityState = InputBox("And now, the city, state zip of your
installation")
End If

Set fldName = Nothing
Set tdfInstProp = Nothing

'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache
'force the database to update what it knows about the tables:
dbTMS.TableDefs.Refresh

End Sub
Private Sub Upgrade()
MsgBox "Database at version " & IPDBVersion & "TMS requires it be at " &
TMSDBVersion
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' New code is required here to perform necessary upgrades from known
versions.
' That means when TMS code requires an update to any TableDefs that code
be
' inserted here to accomplish the modifications. For example, say we
update
' TMS to version 8.3 and that version requires TableDef changes. We set
' TMSDBVersion to 8.3 and write the necessary code to modify the table
definitions
' accordingly and update the IPDBVersion of the database to match.
'
' Everytime such a change is necessary we iterate the code blocks until
we've
' taken the current back-end database incrementally to the highest level,
where
' each block takes us from one version to the next and so on.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
End Sub

===================================================






Dirk Goldgar said:
When I copy your code to my own database, modify it in a minor way to use
my own external DB and table, and run it, it works fine. Therefore, I'm
inclined to believe that there's something going on in the code you've
omitted that is destroying your database object.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Bill said:
Interesting! If I let the module run to completion (I put
the cursor at the "End Sub" of Sub DAO_Example) I don't
get any error messages and all 13 of the global variables you
see in DAO_Example have proper values and the object
variables have been freed.

It's only when I put the cursor at the statement:

If booNotFound = True Then

and run to cursor does the error message come up.

I thought I'd learned my lessons well enough to begin
using DAO, but now I'm not so sure???
Click to expand...

If you just put your cursor somewhere in the InitVer7pt1 procedure and
then tell Access to "run to cursor", it's only that procedure that will
be run. The intended calling procedure, DAO_Example, will *not* be run,
and so the global variables won't have been set. You're only telling
Access to run that one routine, not all routines that might possibly
call that routine.

If you had put a breakpoint on the line in question in InitVer7pt1, then
clicked into the DAO_Example procedure to make that the current
procedure in the VB Editor, and then clicked the Run button (or menu
item Run -> Run Sub/UserForm), you would have stopped at the line
(because of the breakpoint), but wouldn't have gotten that error.
 
D

Dirk Goldgar

Bill said:
Continuing to experiment with DAO and the DAO_Example
module, I deleted the 3 fields that the InitVer7pt1 sub looks
for and the creates & appends the new fields if the key field
is missing (booNotFound=True).

I get a RT Error 3211 at the first "Append":

tdfInstProp.Fields.Append fldName

because the DB engine couldn't lock the table. I have no idea
why not?????
Click to expand...

Probably because you have a recordset open on the table: global
recordset rsInstProp, opened near the top of DAO_Example.
 
B

Bill

It it never occured to me that it would only be the Sub
containing the cursor that ran with "Debug -> Run to
cursor". Something about VBA Debug that I'd never
stumbled onto before.
Thanks,
Bill
 
B

Bill

Indeed! I just needed to defer opening the Recordset
until AFTER I'd finished with tasks related to the
TableDefs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't DAO Append because of RT3211 4
Working outlook via vba code with xlsx - error 0
Need help with a DAO to ADO conversion 22
Object variable or with Block variable not set 9
object variable or with block variable not set 12
Runtime error 91: Object variable or With block variable not set 8
Object variable or With block variable not set 7
That darned error message "Object variable or With block variable notset" ! 20

Top