Requery "Too Soon"?

[Bill]

Marsh,
I have found the offending module. It has nothing to
do directly with the forms that were failing. It is one
of the general modules that runs at startup and loads
up a collection of global variables that are dim'd in
an "un-called general module" thereby preserving
them throughout the applications life.

What's new in V7.2 of the application is that there
are 3 new fields added to one of the tables beginning
with V7.2 and this module now checks for those
new fields in the backend mdb. If they are absent,
the code tbldefs an appendage of the three new
fields. That all seemed to work okay in-so-far as
the adding of the fields. However, it appears that
the "field checking" code has somehow left the
backend mdb in an un-desired state, as seen in
the failures we've been discussing. Doug Steele
had shown me how to do the tbldefs, but I must
have not learned my lesson completely.

If I can't find the problem myself, I would ask
your permission to e-mail you a text file of the
general module with the V7.2 changes highlighted
for your inspection. I realize you probably have
better things to do with your time, so please
accept my apologies for asking if that's not
acceptable to you.

Bill

It worked once, then doesn't work again??? Totally weird!

You don't have an incomplete transaction going on, a machine
that's in a cache thrashing state, or a horribly slow
server, do you?

Starting over is really tedious, but I don't have a better
idea.

Good luck,
--
Marsh
MVP [MS Access]

RATS! I thought we'd hit "pay-dirt" when I first tested
the newly created mdb with all the imports. However,
after I did some additional regression testing for many
of the other functions, I returned to the offending function
only to find that it was once again failing.

I don't know what else to do other than to re-build V7.2
starting from the V7.1 source adding the changes one at
a time and testing the offending form each time anything
is added.

I can see that you have replied to some other folks in this
thread, but I can't see what they said. I hope someone else
has a better idea than I do.

Since logic seems to have flown out the window, I think it's
time to take the usual copout and blame it on some kind of
corruption. The fact that you said somewhere that the
identical code in an older version of your app runs fine
also lends credence to corruption being the culprit.

There are a few things you can do to try to clear it, but no
guarantees. First make sure you have a backup in case
things get worse. I think(?) the first thing to try is to
decompile. Use the Start button's Run command:
"path\MSACCESS.EXE" "path\yourapp.mdb" /Decompile
Be sure to hold down the shift key to prevent any startup
procedure from running. Then use Tools - Compact (holding
the shift key down) and close Access. While holding the
shift key down, open Access with your database, then hit
Ctrl+g to get to the VB window and use Debug - Compile and
close Access. Finally, try the problem to see if all that
made a difference.

A different thing to try is to create a new, blank mdb, set
all the options the way you want, especially make sure Name
AutoCorrect is OFF. Then import everything from the problem
mdb.

Note that a common cause of front end corruption is editing
a form/report's module while the form/report is open in any
view except design view.


Bill wrote:
Three DoEvents didn't have any affect. However, if I
insert MsgBox "Hi" and wait 3 or 4 seconds before
I click Okay, then the Requery will return the just
inserted record. If I click Okay as fast as the message
MsgBox appears, I get an empty subform, i.e., I do
have to wait a few seconds before clicking Okay.

I'm out of ideas at the moment.

I put a breakpoint at the statement before the Execute
and slowwwwwwwly stepped through the Execute
and Requery. The inserted record then appeared in
the Requery. I'll try inserting a DoEvents or two.


I've never experienced the problem. You say that it works
if you do something innocuous, but doesn't that mean that
the Requery was removed or that you put a break point on
it? If so, I think that's an invalid test. Maybe adding a
DoEvents (or two) before the Requery might be effective??


Bill wrote:
That's what I believed when I wrote the code. However,
I have to perform a couple of incidental screen actions
before the data inserted displays in the subform. I added
the dbFailOnError to the Execute, but that didn't produce
any errors nor solve the problem.

One interesting observation is that it only fails when the
record inserted is THE FIRST record that would appear
in the Recordsource of the subform. Like the Recordsource
is "no records found" in the initial opening of the subform
and the record inserted is the FIRST.

What else might it be?


Bill wrote:
In the following segment of code, a record has been
inserted into the RecordSource of a subform. I'm
missing a very fundamental, I think, statement that
insures that the insert has completed before the
Requery is issued. What might that be?

tmpSQL = "INSERT INTO [DonRegFam](FundID,DOE,FamilyID,Amount,Type)"
tmpSQL = tmpSQL & " VALUES(" & cboFunds.Column(0) & ", " & DVal &
",
"
tmpSQL = tmpSQL & FamID & ", " & Me.AmtBox & ", " & TypeVal & ");"
CurrentDb.Execute tmpSQL
Me.DonationsSubform.Form.Requery
Me.CashBox = False
Me.AmtBox = 0
Me.AmtBox.SetFocus
Me.Record.Visible = False
Me.PrtStmtCmd.Visible = True


The Execute method runs synchronously, so you should not
have to worry about it. You might want to use dbFailOnError
just in case the query can not do what you told it to do.

 

[Bill]

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

It worked once, then doesn't work again??? Totally weird!

You don't have an incomplete transaction going on, a machine
that's in a cache thrashing state, or a horribly slow
server, do you?

Starting over is really tedious, but I don't have a better
idea.

Good luck,
--
Marsh
MVP [MS Access]

RATS! I thought we'd hit "pay-dirt" when I first tested
the newly created mdb with all the imports. However,
after I did some additional regression testing for many
of the other functions, I returned to the offending function
only to find that it was once again failing.

I don't know what else to do other than to re-build V7.2
starting from the V7.1 source adding the changes one at
a time and testing the offending form each time anything
is added.

I can see that you have replied to some other folks in this
thread, but I can't see what they said. I hope someone else
has a better idea than I do.

Since logic seems to have flown out the window, I think it's
time to take the usual copout and blame it on some kind of
corruption. The fact that you said somewhere that the
identical code in an older version of your app runs fine
also lends credence to corruption being the culprit.

There are a few things you can do to try to clear it, but no
guarantees. First make sure you have a backup in case
things get worse. I think(?) the first thing to try is to
decompile. Use the Start button's Run command:
"path\MSACCESS.EXE" "path\yourapp.mdb" /Decompile
Be sure to hold down the shift key to prevent any startup
procedure from running. Then use Tools - Compact (holding
the shift key down) and close Access. While holding the
shift key down, open Access with your database, then hit
Ctrl+g to get to the VB window and use Debug - Compile and
close Access. Finally, try the problem to see if all that
made a difference.

A different thing to try is to create a new, blank mdb, set
all the options the way you want, especially make sure Name
AutoCorrect is OFF. Then import everything from the problem
mdb.

Note that a common cause of front end corruption is editing
a form/report's module while the form/report is open in any
view except design view.


Bill wrote:
Three DoEvents didn't have any affect. However, if I
insert MsgBox "Hi" and wait 3 or 4 seconds before
I click Okay, then the Requery will return the just
inserted record. If I click Okay as fast as the message
MsgBox appears, I get an empty subform, i.e., I do
have to wait a few seconds before clicking Okay.

I'm out of ideas at the moment.

I put a breakpoint at the statement before the Execute
and slowwwwwwwly stepped through the Execute
and Requery. The inserted record then appeared in
the Requery. I'll try inserting a DoEvents or two.


I've never experienced the problem. You say that it works
if you do something innocuous, but doesn't that mean that
the Requery was removed or that you put a break point on
it? If so, I think that's an invalid test. Maybe adding a
DoEvents (or two) before the Requery might be effective??


Bill wrote:
That's what I believed when I wrote the code. However,
I have to perform a couple of incidental screen actions
before the data inserted displays in the subform. I added
the dbFailOnError to the Execute, but that didn't produce
any errors nor solve the problem.

One interesting observation is that it only fails when the
record inserted is THE FIRST record that would appear
in the Recordsource of the subform. Like the Recordsource
is "no records found" in the initial opening of the subform
and the record inserted is the FIRST.

What else might it be?


Bill wrote:
In the following segment of code, a record has been
inserted into the RecordSource of a subform. I'm
missing a very fundamental, I think, statement that
insures that the insert has completed before the
Requery is issued. What might that be?

tmpSQL = "INSERT INTO [DonRegFam](FundID,DOE,FamilyID,Amount,Type)"
tmpSQL = tmpSQL & " VALUES(" & cboFunds.Column(0) & ", " & DVal &
",
"
tmpSQL = tmpSQL & FamID & ", " & Me.AmtBox & ", " & TypeVal & ");"
CurrentDb.Execute tmpSQL
Me.DonationsSubform.Form.Requery
Me.CashBox = False
Me.AmtBox = 0
Me.AmtBox.SetFocus
Me.Record.Visible = False
Me.PrtStmtCmd.Visible = True


The Execute method runs synchronously, so you should not
have to worry about it. You might want to use dbFailOnError
just in case the query can not do what you told it to do.

 

[Marshall Barton]

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

I added the Set fldCurr = Nothing statement, but
that had no affect.

I have to leave for most of the day, so I'll look in
again later today to see if you've suddenly found
a new idea.

Thanks for your words of encouragement. I have
to keep at this until I find the root of the problem.

By the way, I checked the reference used in the
general module where DAO appears. I've been
using the Microsoft DAO 3.6 Object Library.

Later, Bill

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Marshall Barton]

I don't use ADO so I can't say anything useful beyond the
usual debugging techniques.
--
Marsh
MVP [MS Access]

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

I'm certain I could help my own cause if I could
obtain sufficient education regarding ADO versus
DAO versus whatever else there is under-the-
covers of Access. I liken the dilemma to the
necessity of understanding the key differences
that exist in database formats and their related
access methods on IBM's MVS and VM
mainframes. Namely, you just don't arbitrarily
mix them without dire consequences.

Intuitively, I would think it unwise to mix as I
have fallen into with this module. What would
be the DAO counterpart to what you see used
in this module?

Bill

..

I don't use ADO so I can't say anything useful beyond the
usual debugging techniques.
--
Marsh
MVP [MS Access]

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim rsInstProp As DAO.Recordset
Dim rsAtt As ADODB.Recordset

Set dbCurr = CurrentDb()

'Open the table containing the donation key.
stSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = dbCurr.OpenRecordset(stSQL)

I don't see where you're actually using rsAtt.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm certain I could help my own cause if I could
obtain sufficient education regarding ADO versus
DAO versus whatever else there is under-the-
covers of Access. I liken the dilemma to the
necessity of understanding the key differences
that exist in database formats and their related
access methods on IBM's MVS and VM
mainframes. Namely, you just don't arbitrarily
mix them without dire consequences.

Intuitively, I would think it unwise to mix as I
have fallen into with this module. What would
be the DAO counterpart to what you see used
in this module?

Bill

.

I don't use ADO so I can't say anything useful beyond the
usual debugging techniques.
--
Marsh
MVP [MS Access]

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

===================================================
Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

Thanks Doug.
So much for my intuition! I made the offending module
homogenously DAO, but that didn't clear up the problem.

I don't remember where "rsAtt" came from. I assume I
must have done some copy/paste long ago and failed to
remove the Dim statement.

At one point in the thread, Marsh suggested that I step
through the code in Debug mode. Since there are no
apparent errors detected during execution of the offending
module, I don't know what I'd be looking for with that
exercise????

Bill

Dim dbCurr As DAO.Database
Dim rsInstProp As DAO.Recordset
Dim rsAtt As ADODB.Recordset

Set dbCurr = CurrentDb()

'Open the table containing the donation key.
stSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = dbCurr.OpenRecordset(stSQL)

I don't see where you're actually using rsAtt.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm certain I could help my own cause if I could
obtain sufficient education regarding ADO versus
DAO versus whatever else there is under-the-
covers of Access. I liken the dilemma to the
necessity of understanding the key differences
that exist in database formats and their related
access methods on IBM's MVS and VM
mainframes. Namely, you just don't arbitrarily
mix them without dire consequences.

Intuitively, I would think it unwise to mix as I
have fallen into with this module. What would
be the DAO counterpart to what you see used
in this module?

Bill

.

I don't use ADO so I can't say anything useful beyond the
usual debugging techniques.
--
Marsh
MVP [MS Access]


Bill wrote:

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

===================================================
Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

Marsh & Doug,
All I have to run is this much (below) of the offending code
to cause a failure.

==========================================
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")

 

[Bill]

All I have to do is comment out the DAO open and close
and the problem goes away!!!!

I don't see any TableDef counterpart to that in DAO.
It appears that the ADO append itself will create the
table def if the field doesn't exist.

This problem is driving me nuts!!!!

Marsh & Doug,
All I have to run is this much (below) of the offending code
to cause a failure.

==========================================
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
.
.
.
.
Set tdfCurr = Nothing
Set fldCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing
==========================================
I've studied a bit of the documentation on DAO and ADO
this morning. I can only offer that several years ago when
this application was born that I was young enough in the
VBA language that out of ignorance I adopted the use of
ADO quite pervasively. My intuition is still telling me that
the DAO/ADO mix is likely problematic. So that I can put
this question to bed, what would be the ADO counter part
to this code segment together with the appendage code:
=========================================
' For Each fldCurr In tdfCurr.Fields
' If fldCurr.Name = "InstDBVersion" Then
' booNotFound = False
' Exit For
' End If
' Next fldCurr

' If booNotFound = True Then
' Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
' tdfCurr.Fields.Append fldCurr
=========================================

I couldn't have gotten this far without your help, so I thank
you for all your time and thought.
Bill

In the following segment of code, a record has been
inserted into the RecordSource of a subform. I'm
missing a very fundamental, I think, statement that
insures that the insert has completed before the
Requery is issued. What might that be?

tmpSQL = "INSERT INTO [DonRegFam](FundID,DOE,FamilyID,Amount,Type)"
tmpSQL = tmpSQL & " VALUES(" & cboFunds.Column(0) & ", " & DVal & ", "
tmpSQL = tmpSQL & FamID & ", " & Me.AmtBox & ", " & TypeVal & ");"
CurrentDb.Execute tmpSQL
Me.DonationsSubform.Form.Requery
Me.CashBox = False
Me.AmtBox = 0
Me.AmtBox.SetFocus
Me.Record.Visible = False
Me.PrtStmtCmd.Visible = True

 

[Chuck Grimsby]

You appear to be missing 2 commands that I (personally) always use
when changing the structure of a table via code, or adding tables via
code to an existing database. Perhaps they'll help, perhaps not, but
it may be worth a shot!

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

Set tdfCurr = Nothing

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

dbCurr.Close
Set dbCurr = Nothing

' optional:
DoEvents

Also, you should always set your objects to nothing in the reverse
order of how you set them to something, so your "Set fldCurr =
Nothing" statement should come before your "Set tdfCurr = Nothing"
statement. fldCurr is a "child" of tbfCurr, which is a "child" of
dbCurr. Not setting them to nothing in the reverse order that you set
them to something in can cause problems when the child holds onto
properties of the parent.

Personally, I've found a need to refresh the fields collection, but
feel free to use it.

By the way, this could all be done with 2 or 3 SQL queries. You may
find that method easier to use and maintain as well as less
"expensive" in terms of the code and resources needed. You also won't
need to mix ADO and DAO to accomplish this.

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================

This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

Chuck,
I incorporated your suggestions regarding "order of sets
and release" plus the forcing of Jet to finish pending
operations. Though I will leave that code in place, I
would like to explore your idea about using SQL queries
to accomplish the overall task. Having said that, I have
no idea what such a query would look like to do that,
most notably adding/appending fields, unless that's what
Doug Steele was trying to tell me with his DDL suggestion
earlier?

Bill

You appear to be missing 2 commands that I (personally) always use
when changing the structure of a table via code, or adding tables via
code to an existing database. Perhaps they'll help, perhaps not, but
it may be worth a shot!

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

Set tdfCurr = Nothing

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

dbCurr.Close
Set dbCurr = Nothing

' optional:
DoEvents

Also, you should always set your objects to nothing in the reverse
order of how you set them to something, so your "Set fldCurr =
Nothing" statement should come before your "Set tdfCurr = Nothing"
statement. fldCurr is a "child" of tbfCurr, which is a "child" of
dbCurr. Not setting them to nothing in the reverse order that you set
them to something in can cause problems when the child holds onto
properties of the parent.

Personally, I've found a need to refresh the fields collection, but
feel free to use it.

By the way, this could all be done with 2 or 3 SQL queries. You may
find that method easier to use and maintain as well as less
"expensive" in terms of the code and resources needed. You also won't
need to mix ADO and DAO to accomplish this.

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

Chuck,
I see the "Alter Table" now in SQL and finally found
the HELP text that explains what Doug was trying to
tell me regarding DDL. In that same post, Doug also
suggested that it was better to check the fields collection
first, rather than "blindly" add and deal with the error if
it was already there. In doing so, that led me into the
realm of DAO. With the sole use of queries, are you
suggesting that I handle the error following an "Alter
Table"? Or, is there a query that will check field existence
"softly".

Bill

You appear to be missing 2 commands that I (personally) always use
when changing the structure of a table via code, or adding tables via
code to an existing database. Perhaps they'll help, perhaps not, but
it may be worth a shot!

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

Set tdfCurr = Nothing

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

dbCurr.Close
Set dbCurr = Nothing

' optional:
DoEvents

Also, you should always set your objects to nothing in the reverse
order of how you set them to something, so your "Set fldCurr =
Nothing" statement should come before your "Set tdfCurr = Nothing"
statement. fldCurr is a "child" of tbfCurr, which is a "child" of
dbCurr. Not setting them to nothing in the reverse order that you set
them to something in can cause problems when the child holds onto
properties of the parent.

Personally, I've found a need to refresh the fields collection, but
feel free to use it.

By the way, this could all be done with 2 or 3 SQL queries. You may
find that method easier to use and maintain as well as less
"expensive" in terms of the code and resources needed. You also won't
need to mix ADO and DAO to accomplish this.

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

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

Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Chuck Grimsby]

Unfortunately, you can't run a query to find out the fields in a
table. However you can run a query against the table and trap it for
error # 3061 (Too few parameters) which (in this case) will mean that
the field doesn't exist.

Public Function CheckForField(strTableName As String, _
strFieldName As String, _
Optional whatDB As DAO.Database) As Boolean
Dim mydb As DAO.Database
Dim strSQL As String
Dim bolISetTheDB As Boolean

If whatDB Is Nothing Then
Set whatDB = CurrentDb
bolISetTheDB = True
End If

strSQL = "SELECT Count(" & _
"[" & strFieldName & "]" & _
") FROM [" & strTableName & "]"
On Error Resume Next
Set myRST = whatDB.OpenRecordset(strSQL, dbOpenForwardOnly)
Select Case Err.Number
Case 0
CheckForField = True
Case 3061
CheckForField = False
Case Else
MsgBox "Error!" & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description, _
vbCritical + vbOKOnly, _
"Check For Field Error"
End Select
Err.Clear
If bolISetTheDB = True Then Set whatDB = Nothing
End Function

The above said, I want to say that I think you're doing this table the
wrong way. To my way of thinking, you're going horizontally when you
should be going vertically.

I'd just create a 2 column table with one column being the "setting",
and the other being the value for that setting. Both fields are text,
so you can toss just about whatever you need in there (except binary
stuff), you just have to remember to convert the value before it goes
in, or when it comes out.

This method also offers the advantage of being able to toss in
whatever settings you might need in the future without having to
change the structure of the table (and thus the database), and you can
also easily create a crosstab query from the table if you need more
then one value.

I've used this method in a number of databases over the years, and it
seems to work quite well.

Chuck,
I see the "Alter Table" now in SQL and finally found
the HELP text that explains what Doug was trying to
tell me regarding DDL. In that same post, Doug also
suggested that it was better to check the fields collection
first, rather than "blindly" add and deal with the error if
it was already there. In doing so, that led me into the
realm of DAO. With the sole use of queries, are you
suggesting that I handle the error following an "Alter
Table"? Or, is there a query that will check field existence
"softly".

Bill

You appear to be missing 2 commands that I (personally) always use
when changing the structure of a table via code, or adding tables via
code to an existing database. Perhaps they'll help, perhaps not, but
it may be worth a shot!

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

Set tdfCurr = Nothing

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

dbCurr.Close
Set dbCurr = Nothing

' optional:
DoEvents

Also, you should always set your objects to nothing in the reverse
order of how you set them to something, so your "Set fldCurr =
Nothing" statement should come before your "Set tdfCurr = Nothing"
statement. fldCurr is a "child" of tbfCurr, which is a "child" of
dbCurr. Not setting them to nothing in the reverse order that you set
them to something in can cause problems when the child holds onto
properties of the parent.

Personally, I've found a need to refresh the fields collection, but
feel free to use it.

By the way, this could all be done with 2 or 3 SQL queries. You may
find that method easier to use and maintain as well as less
"expensive" in terms of the code and resources needed. You also won't
need to mix ADO and DAO to accomplish this.

One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

===================================================
Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.

 

[Bill]

Thanks Chuck. I'm way too far down the road to change the
table approach, though I see what you mean. The current
application is 4 years old with about 5K lines of code and
tons of forms that reference the fields in the table at hand.

Anyway, I've more-or-less followed yours and Doug's SQL
advise and have finally arrived at a solution. See my post of
a few minutes ago to Doug (4:41PM) in the DAO to ADO
thread.

Thanks again,
Bill

Unfortunately, you can't run a query to find out the fields in a
table. However you can run a query against the table and trap it for
error # 3061 (Too few parameters) which (in this case) will mean that
the field doesn't exist.

Public Function CheckForField(strTableName As String, _
strFieldName As String, _
Optional whatDB As DAO.Database) As Boolean
Dim mydb As DAO.Database
Dim strSQL As String
Dim bolISetTheDB As Boolean

If whatDB Is Nothing Then
Set whatDB = CurrentDb
bolISetTheDB = True
End If

strSQL = "SELECT Count(" & _
"[" & strFieldName & "]" & _
") FROM [" & strTableName & "]"
On Error Resume Next
Set myRST = whatDB.OpenRecordset(strSQL, dbOpenForwardOnly)
Select Case Err.Number
Case 0
CheckForField = True
Case 3061
CheckForField = False
Case Else
MsgBox "Error!" & vbNewLine & _
Err.Number & vbNewLine & _
Err.Description, _
vbCritical + vbOKOnly, _
"Check For Field Error"
End Select
Err.Clear
If bolISetTheDB = True Then Set whatDB = Nothing
End Function

The above said, I want to say that I think you're doing this table the
wrong way. To my way of thinking, you're going horizontally when you
should be going vertically.

I'd just create a 2 column table with one column being the "setting",
and the other being the value for that setting. Both fields are text,
so you can toss just about whatever you need in there (except binary
stuff), you just have to remember to convert the value before it goes
in, or when it comes out.

This method also offers the advantage of being able to toss in
whatever settings you might need in the future without having to
change the structure of the table (and thus the database), and you can
also easily create a crosstab query from the table if you need more
then one value.

I've used this method in a number of databases over the years, and it
seems to work quite well.

Chuck,
I see the "Alter Table" now in SQL and finally found
the HELP text that explains what Doug was trying to
tell me regarding DDL. In that same post, Doug also
suggested that it was better to check the fields collection
first, rather than "blindly" add and deal with the error if
it was already there. In doing so, that led me into the
realm of DAO. With the sole use of queries, are you
suggesting that I handle the error following an "Alter
Table"? Or, is there a query that will check field existence
"softly".

Bill

You appear to be missing 2 commands that I (personally) always use
when changing the structure of a table via code, or adding tables via
code to an existing database. Perhaps they'll help, perhaps not, but
it may be worth a shot!

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

Set tdfCurr = Nothing

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

dbCurr.Close
Set dbCurr = Nothing

' optional:
DoEvents

Also, you should always set your objects to nothing in the reverse
order of how you set them to something, so your "Set fldCurr =
Nothing" statement should come before your "Set tdfCurr = Nothing"
statement. fldCurr is a "child" of tbfCurr, which is a "child" of
dbCurr. Not setting them to nothing in the reverse order that you set
them to something in can cause problems when the child holds onto
properties of the parent.

Personally, I've found a need to refresh the fields collection, but
feel free to use it.

By the way, this could all be done with 2 or 3 SQL queries. You may
find that method easier to use and maintain as well as less
"expensive" in terms of the code and resources needed. You also won't
need to mix ADO and DAO to accomplish this.




One last thought before I leave for the day.......
Maybe I've created a problem with the DAO
and ADODB mix in this module, here's the
whole module:

===================================================
Option Compare Database
Option Explicit
Public Sub LoadInstProp()
Dim con As ADODB.Connection
Dim rsInstProp As ADODB.Recordset
Dim rsAtt As ADODB.Recordset
Dim stSQL As String

'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' Here and only here is where one sets the current version of the TMS
code.
' AND, where the compatibility version of the back-end database is set.
'
' The comparison at startup is to see if back-end is version compatible
with
' the front-end code. I.e., If the IPDBVersion found in the Installation
' Properties table is "Equal" to the TMSDBVersion to which the current
code
' is compatible.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

TMSVersion = 7.2
TMSDBVersion = 7.1 'This ONLY changes when a TableDef changes.

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

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.
'==========================================================================

Set con = Application.CurrentProject.Connection

'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
Set rsInstProp = Nothing

Set con = Nothing

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.
'==========================================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

tdfCurr.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 tdfCurr = Nothing
dbCurr.Close
Set fldCurr = Nothing
Set dbCurr = Nothing

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

===================================================
Bill wrote:

Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.

I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)

Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)

=============================================
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.
'====================================================

Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

IPDatabase = DLookup("InstDatabase", "InstProperties")

Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr

Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr

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 tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

End Sub
=============================================


This is a really, really tough problem and you've done an
excellent job of tracking it this far.

With the dbCurr.Close in there, I don't see anything that
should cause the problem. It all looks pretty
straightforward to me, especially if the fields were already
there. In that case you can comment out the entire If block
and only debug the few remaining lines. The only straw I
can grasp at then is to add:
Set fldCurr = Nothing
after the For loop (and after the fields are created)

I don't see how it would make a difference here, but for
completeness sake, I guess you should also use:
tdfCurr.Fields.Refresh
after the fields have been appended.