ADO and refresh method

[scroce]

Someone please explain this to me. I'm familiar with ADO in regards to ASP, but in Jet, I'm new.

I have a form where a user can enter a new record. The user hits the "add" button and the code uses ADO to append the record.

After this I need to refresh a listbox on the originating form so the record pops up. This has been the problem. The record will not show up no matter what I do. The listbox is on a tab control.

The record will show up if:
1. I click off of the current tab and then back, the record shows up.

2. If I move to another record using the nav buttons and then move back.

3. if I close and reopen the form.

Now here's the amazing part -

I've read on a lot of other posts that it could be a delay in ADO writing to the datbase - in essence, i guess the record doesn't show up before the the lstBox.requery method is called. Since this is a small app (split table) I didn't think that could be it - but I tried it, and lo and behold - if I put in a loop like this:

Dim n As Double

For n = 0 To 10000000
 n = n + 1
Next

sure enough, the record shows up if I write this delay in the code.

This can't be the right way to do this. What is the right way? Here is the full code:

Public Sub addNewAuto()

DoCmd.SetWarnings False
   
Dim strSQL As String
Dim strPolID, strYear As String
Dim datEffDate As Date
Dim datExDate As Date
Dim intAutoNum As Integer
Dim strVehDescrip As String
Dim strVin As String
Dim cnString As String

Dim cnADO As New ADODB.Connection
Dim rsADO As New ADODB.Recordset

Dim frmMain As Form

Set frmMain = Forms!frmMain

'Test Code
'Set cnADO = CurrentProject.Connection

'The connection string:
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\myPath\myDB.mdb;"

'Open the string:
cnADO.Open (cnString)

'Set variables:
strPolID = Me.txtPolID.Value
strYear = Nz(Me.txtYear.Value, "")
datEffDate = Nz(Me.txtEffDate.Value, Date)
datExDate = Nz(Me.txtExpDate.Value, Date)
intAutoNum = Me.txtVehicleNum.Value
strVehDescrip = Nz(Me.txtDescrip.Value, "")
strVin = Nz(Me.txtVin.Value, "")

'pull the fields from the table we want to update:
strSQL = "SELECT tblAutoSchedule.ID, tblAutoSchedule.PolicyID, tblAutoSchedule.EffDate, "
strSQL = strSQL & "tblAutoSchedule.ExDate, tblAutoSchedule.Number, tblAutoSchedule.Year, "
strSQL = strSQL & "tblAutoSchedule.VehDescrip, tblAutoSchedule.Vin "
strSQL = strSQL & "FROM tblAutoSchedule;"

rsADO.Open strSQL, cnADO, adOpenDynamic, adLockOptimistic
'rsADO.Open strSQL, cnADO

'Add the new record
rsADO.AddNew
rsADO("PolicyID") = strPolID
rsADO("EffDate") = datEffDate
rsADO("ExDate") = datExDate
rsADO("Number") = intAutoNum
rsADO("Year") = strYear
rsADO("VehDescrip") = strVehDescrip
rsADO("Vin") = strVin

'update the recordset
rsADO.Update

'rsADO.Requery

rsADO.Close
cnADO.Close

Set rsADO = Nothing
Set cnADO = Nothing

strSQL = Empty

DoCmd.SetWarnings True
'Me.Form.Recordset.Requery

'this is so incredibly stupid - this can't be right....
Dim n As Double

For n = 0 To 10000000
 n = n + 1
Next

frmMain.Recordset.Requery
frmMain.lstAutoSched.Requery

End Sub

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

interestingly enough, this problem goes away if you use DAO, so that's what I'm a-gonna do, but I can't believe that ADO works this poorly:

Public Sub addNewAuto2()

DoCmd.SetWarnings False

Dim db As DAO.Database
Dim rs As DAO.Recordset
   
Dim strSQL As String
Dim strPolID, strYear As String
Dim datEffDate As Date
Dim datExDate As Date
Dim intAutoNum As Integer
Dim strVehDescrip As String
Dim strVin As String

Dim frmMain As Form

Set frmMain = Forms!frmMain

'Set variables:
Set db = CurrentDb

strPolID = Me.txtPolID.Value
strYear = Nz(Me.txtYear.Value, "")
datEffDate = Nz(Me.txtEffDate.Value, Date)
datExDate = Nz(Me.txtExpDate.Value, Date)
intAutoNum = Me.txtVehicleNum.Value
strVehDescrip = Nz(Me.txtDescrip.Value, "")
strVin = Nz(Me.txtVin.Value, "")


strSQL = "SELECT tblAutoSchedule.ID, tblAutoSchedule.PolicyID, tblAutoSchedule.EffDate, "
strSQL = strSQL & "tblAutoSchedule.ExDate, tblAutoSchedule.Number, tblAutoSchedule.Year, "
strSQL = strSQL & "tblAutoSchedule.VehDescrip, tblAutoSchedule.Vin "
strSQL = strSQL & "FROM tblAutoSchedule;"

   
  Set rs = db.OpenRecordset(strSQL)
  
  
      rs.AddNew
      rs("PolicyID") = strPolID
      rs("EffDate") = datEffDate
      rs("ExDate") = datExDate
      rs("Number") = intAutoNum
      rs("Year") = strYear
      rs("VehDescrip") = strVehDescrip
      rs("Vin") = strVin
      rs.Update
      
      
      
DoCmd.SetWarnings True

frmMain.Recordset.Requery
frmMain.lstAutoSched.Requery

End Sub

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[cmmrfrds]

Me.Requery should refresh the recordset and all controls on the Form regardless of the library used.

 

[scroce]

it should, but in my case, it just doesn't want to - or maybe it does do it, but as I'm theorizing right now, maybe the record hasn't been written yet - so what is in effect happening is that refresh gets called first, then the update happens - consequently no record shows up

However, If I delay the refresh with that loop, it gives jet the time to finish writing the record - and when the refresh finally gets called, the record is there waiting, and thus shows up in my listbox.

... but that theory just sounds too crazy to be true.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[cmmrfrds]

Just to verify.

You used this Me.Requery
NOT this frmMain.Recordset.Requery

 

[scroce]

i had tried that and commented it out, in favor of

frmMain.Recordset.Requery

and/or

frmMain.lstAutoSched.Requery

neither had worked for me.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[PHV]

And this ?
frmMain.lstAutoSched.RowSource = frmMain.lstAutoSched.RowSource

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scroce]

I didn't try that

That's another way of refreshing the underlying data, I presume.



I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[markgrizzle]

I went through the same headache sometime back. I ended up writing a function to wait for the write, testing the code for the minimum time needed, and setting the number of passes through the loop to match.

I think it has something to do with write caching, but I ran out of time on the project and never looked back. Still prefer ado to dao though.

 

[scroce]

mark,

yes, i have seen other posts in other places where people have done something like that, and on the surface it seems that this is indeed what I am dealing with.

Although it just doesn't seem to make sense to have to program in something that deliberately slows down your db functions!!! how silly?

do you remember what specific objects/properties/methods etc. that you manipulated in order to write the code your mentioned?

I haven't coded enough ADO to really have a preference, although as I understand it , DAO is really for internal Jet Stuff. If you want access to connect to an outside DB, then ADO is your best bet.


I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[markgrizzle]

Hi scroce:
It isn't as rare as you might think. I heard one of the last dos versions of foxpro wouldn't run if the computer was too fast!

The function was very simple. The bear was the manual testing to figure out the minimum wait time required.

Function Foo(lngCount as long) as boolean

dim lngCounter as long

on error goto LocalError

lngCounter = 1

do until lngCounter > lngCount * 1000

lngCounter = lngCounter + 1

loop

Foo = true

ExitHere:
exit function

LocalError:
...

End function

In my code, I would perform the insert operation, then call the function like bln = foo(100). The procedure would then wait until foo returned a value.

Mark

 

[scroce]

the question that pops into my mind on this one is:

how can you be sure that every procedure that is called from ADO will take the same amount of time? Does that mean you have to test every one and come up with a different number to feed the Foo?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[markgrizzle]

That's right. I started by trying to test each call, but then ended up simply increasing the iterations in the function whenever it failed. This meant everything slowed down each time I did it, but since my work tended to retrieve and edit existing data more than insert new, it wasn't a big deal for me.

The bottom line is, you'll almost always need to take any method and adapt it to your individual situation.

 

[scroce]

hmmm....interesting

thank you very much for your input. It is certainly appreciated.



I am a nobody, and nobody is perfect; therefore, I am perfect.