Re: Need help with a DAO to ADO conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

(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!!!!!!!


"Bill" <stanton@xxxxxxx> wrote in message
news:ZzXph.10392$w91.6209@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Yes it has. In the beginning the symptoms pointed heavily
towards there being a problem in the class module that
attended to the insertion of a newly entered donation
value, the symptom being that the subsequent Requery
of the Recordsource of a subform failed to reveal the
new insertion.

As several attempts were made to identify what appeared
to be some sort of timing problem, I/we discovered that
the recently added DAO code in one of the general modules
was having some sort of "rippling" effect later on in the
application. (You might recall your having posted a
reply as to how to accomplish TableDefs and Appends.)
That was further isolated to the section of code wherein
the DAO DatabaseOpen and subsequent close occured.
That is, to cause the failure "downstream" in the application,
all I had to do was go through the DAO open/close sequence
with all of the related code commented out.

All of that experience is what gave rise to the idea that
perhaps there was some sort of bazaar interference
occurring between the DAO and ADO.....sort of "grasping
for straws". My intent was to satisfy once and for whether
there was any truth in that idea, so making the total application
ADO consistent would prove that out.

Bill


"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:uz9yGcrNHHA.3552@xxxxxxxxxxxxxxxxxxxxxxx
To be perfectly honest, I haven't been following the thread that closely.
I popped my head in a couple of times, and was able to address specific
points that were being raised, but I haven't looked at the bigger
picture.

Looking back, your original post that you needed some way of ensure the
insert had taken place before the Requery in this code:

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

This might work:

Dim wksCurr As DAO.Workspace
Dim dbCurr As DAO.Database
Dim tmpSQL As String

tmpSQL = "INSERT INTO [DonRegFam](FundID,DOE,FamilyID,Amount,Type)"
tmpSQL = tmpSQL & " VALUES(" & cboFunds.Column(0) & ", " & DVal & ", "
tmpSQL = tmpSQL & FamID & ", " & Me.AmtBox & ", " & TypeVal & ");"

Set wksCurr = DBEngine.Workspaces(0)
Set dbCurr = wksCurr.Databases(0)
wksCurr.BeginTrans
dbCurr.Execute tmpSQL, dbFailOnError
wksCurr.CommitTrans, 1
Me.DonationsSubform.Form.Requery
Me.CashBox = False
Me.AmtBox = 0
Me.AmtBox.SetFocus
Me.Record.Visible = False
Me.PrtStmtCmd.Visible = True

might solve that problem.

To save me having to read everything else <g>, has the problem moved on
to something else?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill" <stanton@xxxxxxx> wrote in message
news:gbWph.9288$pQ3.2096@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Doug,
I put a lot of credence to your comment:
"I personally don't believe it's anything to do with ADO vs. DAO
though."

I would really prefer to leave the DAO in place, so before
I attempt to follow your suggestion regarding DDL, I would
point out that the current backend has already received its
field updates, so there's no TableDefs or Appends being executed
in the module. All I have to do to get the failure to occur is to
execute the DAO open and close. With that, do you think
attempting to, as you say, "wrap a transaction" would have
much meaning?

Bill



"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:eFZyauqNHHA.448@xxxxxxxxxxxxxxxxxxxxxxx
You can use ADO object to run DDL, or you can use ADOX.

The DDL would look something like:

ALTER TABLE InstProperties
ADD COLUMN InstDBVersion SINGLE,
InstAddress TEXT,
InstCityState TEXT

and the following is an example of how to run DDL:

Sub RunDDL_ADO()
On Error GoTo Err_RunDDL_ADO

Dim conBackend As ADODB.Connection
Dim errConnect As ADODB.Error
Dim strBackend As String
Dim strDDL As String
Dim strErrors As String

strBackend = "H:\Databases\MyBackend.mdb"
strDDL = ""

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

conBackend.Execute strDDL

' 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_RunDDL_ADO:
Set conBackend = Nothing
Exit Sub

Err_RunDDL_ADO:
MsgBox Err.Number & ": " & Err.Description
Resume End_RunDDL_ADO

End Sub

You can see an example of use ADOX at
http://msdn.microsoft.com/library/en-us/ado/html/678e5546-df5d-4cd0-bfe9-6cf13cb385c0.asp?frame=true

I personally don't believe it's anything to do with ADO vs DAO though.

What about trying to wrap a transaction around your table updates? The
CommitTrans method has a dbForceOSFlush parameter (it's wrongly
identified as dbFlushOSCacheWrites in some documentation. If in doubt,
use its numeric value, 1)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill" <stanton@xxxxxxx> wrote in message
news:18Uph.10337$w91.4207@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Without offering here any rationale as to why I need to
convert (you really don't want to know), I need to
convert the code segment below to use ADO. I can't
quite find sufficient details in HELP, particularly where
the TableDefs are concerned, to make the necessary
changes.

(Marshall Barton, Doug Steele and I have been screwing
with a bug for the last 4 days, so any help would be greatly
appreciated. See the "Requery Too Soon?" thread if you're
just dying to know what's behind this question.)
===============================================

Private Sub InitVer7pt1()
.
.
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
.
.
.
.
End If

Set tdfCurr = Nothing
Set fldCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing

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











.

Relevant Pages

  • Re: Requery "Too Soon"?
    ... Dim rsInstProp As DAO.Recordset ... Dim fldCurr As DAO.Field ... Set dbCurr = OpenDatabase ... ' New code is required here to perform necessary upgrades from known ...
    (microsoft.public.access.formscoding)
  • Re: Need help with a DAO to ADO conversion
    ... "I personally don't believe it's anything to do with ADO vs. DAO though." ... Dim conBackend As ADODB.Connection ... Dim fldCurr As DAO.Field ...
    (microsoft.public.access.formscoding)
  • Re: Need help with a DAO to ADO conversion
    ... Dim wksCurr As DAO.Workspace ... Dim booNotFound As Boolean ... Dim fldCurr As DAO.Field ... Set dbCurr = OpenDatabase ...
    (microsoft.public.access.formscoding)
  • Re: Need help with a DAO to ADO conversion
    ... the DAO DatabaseOpen and subsequent close occured. ... Dim wksCurr As DAO.Workspace ... Dim fldCurr As DAO.Field ... Set dbCurr = OpenDatabase ...
    (microsoft.public.access.formscoding)
  • Re: SQL pass through query
    ... and DAO ... Doug Steele, Microsoft Access MVP ... Dim qdfPassthrough As DAO.QueryDef ... Set dbCurr = CurrentDb ...
    (microsoft.public.access.forms)