Need help with a DAO to ADO conversion

B

Bill

Well, certainly my recent experience and help from
you and so many others suggests that ADO is not
typically the method of prevalent choice.

I think the answer to you question is simply that, when
I first came to VBA, someone showed me how to
create and process recordsets in code. And, that there
were so many other priorities within the task at hand
that I never returned to the subject to learn better
lessons regarding these types of objects. The ADO
method you see me using in LoadInstProp has been
used pervasively throughout this application.

I would be delighted to learn whatever method you
see to be more favorable and used in common practice.

I can't thank you enough.

Regards,
Bill




Douglas J. Steele said:
Nothing untoward jumps out.

While I probably should have asked this a while ago, why are you bothing
to use ADO in LoadInstProp?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
HALLAULIA!!!!!!!!!!

Do me one last favor on this 30-hour marathon. Scan the final
code to see if there's a "snake-in-grass" just waiting to bite me.

I COULD NOT HAVE DONE THIS WITHOUT YOU DOUG!!!

=========( Begin Final Code)================================
Option Compare Database
Option Explicit
Dim conBackend As ADODB.Connection
Dim errConnect As ADODB.Error
Dim strBackend As String
Dim rsInstProp As ADODB.Recordset
Dim strSQL As String

Public Sub LoadInstProp()

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

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

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

Set conBackend = New ADODB.Connection
conBackend.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= '" &
strBackend & "'"

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

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



'Open the table containing the donation key.
strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, 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

'Close the properties table recordset.
rsInstProp.Close
Set rsInstProp = Nothing

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

On Error GoTo Err_InitVer7pt1

Dim strDDL As String
Dim strErrors As String
Dim booNotFound As Boolean
Dim objFields As ADODB.Fields
Dim intIndex As Integer

booNotFound = True

strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, adOpenKeyset, adLockOptimistic

Set objFields = rsInstProp.Fields

For intIndex = 0 To (objFields.Count - 1)
If objFields.Item(intIndex).Name = "InstDBVersion" Then
booNotFound = False
Next

'Release the lock. (Close the recordset)
rsInstProp.Close
Set rsInstProp = Nothing


If booNotFound = True Then

strDDL = "ALTER TABLE InstProperties ADD Column InstDBVersion
Single"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstAddress
text(50)"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstCityState
text(50)"
conBackend.Execute strDDL, dbFailOnError

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


'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache

' Check whether any errors were returned
If conBackend.Errors.Count > 0 Then
If conBackend.Errors.Count = 1 Then
strErrors = "There is 1 error:" & vbCrLf
Else
strErrors = "There are " & _
conBackend.Errors.Count & _
" errors:" & vbCrLf
End If

For Each errConnect In conBackend.Errors
strErrors = strErrors & _
errConnect.Description & vbCrLf
Next errConnect
MsgBox strErrors
End If

End_InitVer7pt1:
Exit Sub

Err_InitVer7pt1:
MsgBox Err.Number & ": " & Err.Description
Resume End_InitVer7pt1

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
=========(End Final Code)=============================
Douglas J. Steele said:
You need to instantiate an instance pointing to the actual database that
contains the tables.

Dim dbCurr As DAO.Database

dbCurr = OpenDatabase("C:\Folder\MyBackend.MDB")
dbCurr.Execute "DDL", dbFailOnError
dbCurr.Close
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
I've converted the offending module to check for existing
fields using the fields collection of the open recordset, that
works fine.

Next, I replaced the old code with DDL ALTER TABLE,
but I get a runtime error:

Run-time error '3611':
Cannot execute data definition statements on linked data sources.

code:
strDDL = "ALTER TABLE InstProperties ADD InstDBVersion Single"
CurrentDb.Execute strDDL ', dbFailOnExecute

(Note dbFailOnExecute is commented out, as I get a compiler error.)

I must have missed something in all the postings that allowed me to
update table defs in the backend from a general module executing
in the frontend mde?

Bill



message One way of checking using strictly SQL would be to open a recordset
using SELECT * FROM Table, and loop through the Fields collection of
the resultant recordset, looking at the names of each field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
As I posted earlier to Chuck Grimsby in the "Query Too Soon?"
thread, I finally understood what you were trying to teach me
regarding the use of DDL. In your post about DDL, you had
also cautioned about "adding blindly" and handling the error
when a field already existed. What would be the SQL query
to "softly" test for existance? It seems that if I can do that then
I can eliminate the DAO question completely and perhaps get
this problem "put to bed".

I have to tell you that all of you have been really great in
helping me with this problem, so thanks ever so much.

Bill


message David's question (and a very good one that the rest of us didn't
think to ask!) is what is the backend database: a Jet database (i.e.
an MDB or MDE file), or some other DBMS?

If the BE's a Jet database, then it's not ODBC: you can't use ODBC
to link from an Access application to a Jet database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


As far as I know it is. The application employs a "standard"
(default) Access split mde/mdb configuration.


(PS) There are many places in the current application
wherein an insert is done into what is the underlying
Recordsource for a subform or even Rowsource of
a list box and the Requerys fail to show the newly
added record.

ALL those failures are triggered by the inclusion of
the open/close code of the DAO object. I.e., as soon
as the DAO related code is bypassed, everything
returns to proper functioning.

THIS IS A BUGGER!!!!!!!

Is this an ODBC data source?
 
D

Douglas J. Steele

In dealing with Jet backends (such as you are), I never use anything but
DAO.

Only if I'm dealing with other backends (typically I work with SQL Server)
do I use ADO.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
Well, certainly my recent experience and help from
you and so many others suggests that ADO is not
typically the method of prevalent choice.

I think the answer to you question is simply that, when
I first came to VBA, someone showed me how to
create and process recordsets in code. And, that there
were so many other priorities within the task at hand
that I never returned to the subject to learn better
lessons regarding these types of objects. The ADO
method you see me using in LoadInstProp has been
used pervasively throughout this application.

I would be delighted to learn whatever method you
see to be more favorable and used in common practice.

I can't thank you enough.

Regards,
Bill




Douglas J. Steele said:
Nothing untoward jumps out.

While I probably should have asked this a while ago, why are you bothing
to use ADO in LoadInstProp?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
HALLAULIA!!!!!!!!!!

Do me one last favor on this 30-hour marathon. Scan the final
code to see if there's a "snake-in-grass" just waiting to bite me.

I COULD NOT HAVE DONE THIS WITHOUT YOU DOUG!!!

=========( Begin Final Code)================================
Option Compare Database
Option Explicit
Dim conBackend As ADODB.Connection
Dim errConnect As ADODB.Error
Dim strBackend As String
Dim rsInstProp As ADODB.Recordset
Dim strSQL As String

Public Sub LoadInstProp()

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

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

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

Set conBackend = New ADODB.Connection
conBackend.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= '" &
strBackend & "'"

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

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



'Open the table containing the donation key.
strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, 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

'Close the properties table recordset.
rsInstProp.Close
Set rsInstProp = Nothing

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

On Error GoTo Err_InitVer7pt1

Dim strDDL As String
Dim strErrors As String
Dim booNotFound As Boolean
Dim objFields As ADODB.Fields
Dim intIndex As Integer

booNotFound = True

strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, adOpenKeyset, adLockOptimistic

Set objFields = rsInstProp.Fields

For intIndex = 0 To (objFields.Count - 1)
If objFields.Item(intIndex).Name = "InstDBVersion" Then
booNotFound = False
Next

'Release the lock. (Close the recordset)
rsInstProp.Close
Set rsInstProp = Nothing


If booNotFound = True Then

strDDL = "ALTER TABLE InstProperties ADD Column InstDBVersion
Single"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstAddress
text(50)"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstCityState
text(50)"
conBackend.Execute strDDL, dbFailOnError

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


'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache

' Check whether any errors were returned
If conBackend.Errors.Count > 0 Then
If conBackend.Errors.Count = 1 Then
strErrors = "There is 1 error:" & vbCrLf
Else
strErrors = "There are " & _
conBackend.Errors.Count & _
" errors:" & vbCrLf
End If

For Each errConnect In conBackend.Errors
strErrors = strErrors & _
errConnect.Description & vbCrLf
Next errConnect
MsgBox strErrors
End If

End_InitVer7pt1:
Exit Sub

Err_InitVer7pt1:
MsgBox Err.Number & ": " & Err.Description
Resume End_InitVer7pt1

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
=========(End Final Code)=============================
You need to instantiate an instance pointing to the actual database
that contains the tables.

Dim dbCurr As DAO.Database

dbCurr = OpenDatabase("C:\Folder\MyBackend.MDB")
dbCurr.Execute "DDL", dbFailOnError
dbCurr.Close
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
I've converted the offending module to check for existing
fields using the fields collection of the open recordset, that
works fine.

Next, I replaced the old code with DDL ALTER TABLE,
but I get a runtime error:

Run-time error '3611':
Cannot execute data definition statements on linked data sources.

code:
strDDL = "ALTER TABLE InstProperties ADD InstDBVersion Single"
CurrentDb.Execute strDDL ', dbFailOnExecute

(Note dbFailOnExecute is commented out, as I get a compiler error.)

I must have missed something in all the postings that allowed me to
update table defs in the backend from a general module executing
in the frontend mde?

Bill



message One way of checking using strictly SQL would be to open a recordset
using SELECT * FROM Table, and loop through the Fields collection of
the resultant recordset, looking at the names of each field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
As I posted earlier to Chuck Grimsby in the "Query Too Soon?"
thread, I finally understood what you were trying to teach me
regarding the use of DDL. In your post about DDL, you had
also cautioned about "adding blindly" and handling the error
when a field already existed. What would be the SQL query
to "softly" test for existance? It seems that if I can do that then
I can eliminate the DAO question completely and perhaps get
this problem "put to bed".

I have to tell you that all of you have been really great in
helping me with this problem, so thanks ever so much.

Bill


message David's question (and a very good one that the rest of us didn't
think to ask!) is what is the backend database: a Jet database
(i.e. an MDB or MDE file), or some other DBMS?

If the BE's a Jet database, then it's not ODBC: you can't use ODBC
to link from an Access application to a Jet database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


As far as I know it is. The application employs a "standard"
(default) Access split mde/mdb configuration.


(PS) There are many places in the current application
wherein an insert is done into what is the underlying
Recordsource for a subform or even Rowsource of
a list box and the Requerys fail to show the newly
added record.

ALL those failures are triggered by the inclusion of
the open/close code of the DAO object. I.e., as soon
as the DAO related code is bypassed, everything
returns to proper functioning.

THIS IS A BUGGER!!!!!!!

Is this an ODBC data source?
 
B

Bill

Okay, I will study up on DAO and experiment as
time allows.
Thanks again,
Bill



Douglas J. Steele said:
In dealing with Jet backends (such as you are), I never use anything but
DAO.

Only if I'm dealing with other backends (typically I work with SQL Server)
do I use ADO.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
Well, certainly my recent experience and help from
you and so many others suggests that ADO is not
typically the method of prevalent choice.

I think the answer to you question is simply that, when
I first came to VBA, someone showed me how to
create and process recordsets in code. And, that there
were so many other priorities within the task at hand
that I never returned to the subject to learn better
lessons regarding these types of objects. The ADO
method you see me using in LoadInstProp has been
used pervasively throughout this application.

I would be delighted to learn whatever method you
see to be more favorable and used in common practice.

I can't thank you enough.

Regards,
Bill




Douglas J. Steele said:
Nothing untoward jumps out.

While I probably should have asked this a while ago, why are you bothing
to use ADO in LoadInstProp?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HALLAULIA!!!!!!!!!!

Do me one last favor on this 30-hour marathon. Scan the final
code to see if there's a "snake-in-grass" just waiting to bite me.

I COULD NOT HAVE DONE THIS WITHOUT YOU DOUG!!!

=========( Begin Final Code)================================
Option Compare Database
Option Explicit
Dim conBackend As ADODB.Connection
Dim errConnect As ADODB.Error
Dim strBackend As String
Dim rsInstProp As ADODB.Recordset
Dim strSQL As String

Public Sub LoadInstProp()

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

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

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

Set conBackend = New ADODB.Connection
conBackend.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= '" &
strBackend & "'"

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

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



'Open the table containing the donation key.
strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, 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

'Close the properties table recordset.
rsInstProp.Close
Set rsInstProp = Nothing

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

On Error GoTo Err_InitVer7pt1

Dim strDDL As String
Dim strErrors As String
Dim booNotFound As Boolean
Dim objFields As ADODB.Fields
Dim intIndex As Integer

booNotFound = True

strSQL = "SELECT * FROM [InstProperties]"
Set rsInstProp = New ADODB.Recordset
rsInstProp.Open strSQL, conBackend, adOpenKeyset, adLockOptimistic

Set objFields = rsInstProp.Fields

For intIndex = 0 To (objFields.Count - 1)
If objFields.Item(intIndex).Name = "InstDBVersion" Then
booNotFound = False
Next

'Release the lock. (Close the recordset)
rsInstProp.Close
Set rsInstProp = Nothing


If booNotFound = True Then

strDDL = "ALTER TABLE InstProperties ADD Column InstDBVersion
Single"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstAddress
text(50)"
conBackend.Execute strDDL, dbFailOnError

strDDL = "ALTER TABLE InstProperties ADD Column InstCityState
text(50)"
conBackend.Execute strDDL, dbFailOnError

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


'force Jet to finish any pending operations:
DBEngine.Idle dbRefreshCache

' Check whether any errors were returned
If conBackend.Errors.Count > 0 Then
If conBackend.Errors.Count = 1 Then
strErrors = "There is 1 error:" & vbCrLf
Else
strErrors = "There are " & _
conBackend.Errors.Count & _
" errors:" & vbCrLf
End If

For Each errConnect In conBackend.Errors
strErrors = strErrors & _
errConnect.Description & vbCrLf
Next errConnect
MsgBox strErrors
End If

End_InitVer7pt1:
Exit Sub

Err_InitVer7pt1:
MsgBox Err.Number & ": " & Err.Description
Resume End_InitVer7pt1

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
=========(End Final Code)=============================
message You need to instantiate an instance pointing to the actual database
that contains the tables.

Dim dbCurr As DAO.Database

dbCurr = OpenDatabase("C:\Folder\MyBackend.MDB")
dbCurr.Execute "DDL", dbFailOnError
dbCurr.Close
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
I've converted the offending module to check for existing
fields using the fields collection of the open recordset, that
works fine.

Next, I replaced the old code with DDL ALTER TABLE,
but I get a runtime error:

Run-time error '3611':
Cannot execute data definition statements on linked data sources.

code:
strDDL = "ALTER TABLE InstProperties ADD InstDBVersion Single"
CurrentDb.Execute strDDL ', dbFailOnExecute

(Note dbFailOnExecute is commented out, as I get a compiler error.)

I must have missed something in all the postings that allowed me to
update table defs in the backend from a general module executing
in the frontend mde?

Bill



message One way of checking using strictly SQL would be to open a recordset
using SELECT * FROM Table, and loop through the Fields collection of
the resultant recordset, looking at the names of each field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
As I posted earlier to Chuck Grimsby in the "Query Too Soon?"
thread, I finally understood what you were trying to teach me
regarding the use of DDL. In your post about DDL, you had
also cautioned about "adding blindly" and handling the error
when a field already existed. What would be the SQL query
to "softly" test for existance? It seems that if I can do that then
I can eliminate the DAO question completely and perhaps get
this problem "put to bed".

I have to tell you that all of you have been really great in
helping me with this problem, so thanks ever so much.

Bill


message David's question (and a very good one that the rest of us didn't
think to ask!) is what is the backend database: a Jet database
(i.e. an MDB or MDE file), or some other DBMS?

If the BE's a Jet database, then it's not ODBC: you can't use ODBC
to link from an Access application to a Jet database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


As far as I know it is. The application employs a "standard"
(default) Access split mde/mdb configuration.

message
(PS) There are many places in the current application
wherein an insert is done into what is the underlying
Recordsource for a subform or even Rowsource of
a list box and the Requerys fail to show the newly
added record.

ALL those failures are triggered by the inclusion of
the open/close code of the DAO object. I.e., as soon
as the DAO related code is bypassed, everything
returns to proper functioning.

THIS IS A BUGGER!!!!!!!

Is this an ODBC data source?
 

Ask a Question

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

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

Ask a Question

Top